Update Views in CockroachDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this lesson, we will learn how to Update Views in CockroachDB. At times you may have a query so complex you would like to package it, give it a name, and call it from within other queries as if it were a table or view. Views provide a way to accomplish that in a fairly easy manner, making query design much easier, more efficient, and more modular. We will learn here to create, use, and update views in our Cockroach SQL. This lesson is structured as follows:

  • What? What is a View in CockroachDB and what does it do? Including exploration of syntax.
  • How? How do we create, update, and use Views in our Cockroach database code?

Prerequisites

  • Beginner-level knowledge of how to build SQL statements in Cockroach (or Postgres, Microsoft SQL Server, etc.), with something that will work with CockroachDB like the free Dbeaver database admin tool or some other relational database administration package, or by using server-side script coding languages like ASP.net, Python, PHP, Java, C#, Node.js, VB.Net, Ruby, etc. that provides a connection to your CockroachDB as well as a method for sending it SQL database commands.
  • Experience with the use of the more basic SQL commands, including use of and relation to column, table, SELECT, AS, JOIN, HAVING, and WHERE.

What is a CockroachDB View?

CockroachDB Views are like a query you can create, name, and call into use as if it is a database table. Pretty simple, really. If you ever developed with a Microsoft Access database, you would instantly understand the term because MS Access, since version one, has had “saved queries” that are pretty much exactly what a View is in Cockroach and Postgres. Quite often, the more normalized your database happens to be, the more utility you may find in using Views.

Creating a View

View Creation Syntax

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE
    VIEW viewName
    (
        colName01
        , colName02
        , colName03
        , etc
    )
    WITH
    (
        nameOption [VALUE] [, ... ]
    )
    AS query;

Let us take the above example line-by-line in a brief and high level manner:

  • We used CREATE OR REPLACE instead of CREATE because we want to guard against getting overwrite errors. You may choose to use just CREATE in case you want to find and trap errors and react because your situation dictates that you need to know if a particular View already existed. We’re taking the simpler route here to bring ease to your learning.
  • viewName is the name you give the new View so you can call it from within other queries, code, script, SQL, etc. Remember, CockroachDB treats Views as if they are tables.
  • colName01, colName02… This is the structure you would use to reference column names in your source table.
  • WITH is optional. It refers to parameters you may want to give to the new view.
  • query is your SELECT or VALUES statement that gives the view its rows and columns. See below for how this looks in a use case.

IMPORTANT!

The new query must return the same columns that were created by the existing view SQL. This means the same columns in the same order and being the same data types, but the view may add new columns to the end. If the column names are not given, the query supplies them based on order. The functions or operations generating the output columns may be different.

Let us now use that above syntax to create a view of our own. We’ll start by using two data tables.

tblTechnologies table:

idTechtxtNameidCategory
0Postgres5
1Mongo6
2VB.NET7
3CockroachDB5
4Python7
5PHP7

tblCategories table:

idtxtNameCategory
5RDB
6NoSQL
7Language

Before we go any further, please notice that in the tblTechnologies table, looking at the idCategory column, the numbers you see there refer to the column we named “id” in the tblCategories table.

Normally, in a world without Views or Stored Procedures, we would query the data for a company report by using a join like the following:

1
2
3
4
5
6
7
SELECT
    tblTechnologies.idTech
    , tblTechnologies.txtName
    , tblTechnologies.idCategory
    , tblCategories.txtNameCategory
FROM tblCategories
INNER JOIN tblTechnologies ON tblCategories.id = tblTechnologies.idCategory;

Which gives us the following rows when executed:

![Cockroach view of rows from joined tables](https://i.gyazo.com/a57f7b3105ea2fb5dc14eac8f2ad1a0f.png “Cockroach view of rows from joined tables”)

Note: Odds are that we won’t need an output of “idCategory” here, but we left it in to provide additional clarity.

The next step is to make our SQL into a View. We’ve already written all the relevant SQL above and now only need to put it together. Here’s what it looks like when we have done so:

1
2
3
4
5
6
7
8
CREATE OR REPLACE VIEW viewTechnologies AS
SELECT
    tblTechnologies.idTech
    , tblTechnologies.txtName
    , tblTechnologies.idCategory
    , tblCategories.txtNameCategory
FROM tblCategories
INNER JOIN tblTechnologies ON tblCategories.id = tblTechnologies.idCategory;

It’s that easy! Note how the only change to our query was to add the following line of code at the top before our SQL SELECT statement?

1
CREATE OR REPLACE VIEW viewTechnologies AS...

Yay: now you can use that View via the name viewTechnologies anywhere you would normally reference a table, like in your code or SQL.

Miscellaneous

  • As we discussed a bit above, adding “OR REPLACE” to “CREATE VIEW”, serves to update our view with whatever new code you put after that “CREATE OR REPLACE VIEW viewTechnologies AS” phrase in case a view with the same name already exists and prevents excessive dialog boxes and even errors.
  • Another way to update a view would be if you want to change the name of a view. To do that, you could use the following SQL command: “ALTER VIEW viewTechnologies RENAME TO viewTechnologiesJoined”.
  • As you may have noticed above, you don’t have to name your view with a prefix of “view”. That is just the naming convention we use here so as to provide as much clarity, readability, and ease as possible.
  • Finally, if you want to delete a view, the following SQL command will make it happen: “DROP VIEW viewNameToBeDeleted”.

Conclusion

In this lesson document we studied about and practiced creating Views in Cockroach and we looked into why and when Views can be useful and powerful tools for increasing ease, efficiency, modularity, and even how readable your code is to yourself and others who might inherit your application.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.