The SELECT list

A very simple SQL statement consists of nothing but a SELECT statement together with scalar expressions. For instance:

SELECT CONCAT('Hello from CockroachDB at ',
              CAST (NOW() as STRING)) as hello

The SELECT list includes a comma-separated list of expressions that can contain combinations of constants, functions, and operators. The CockroachDB SQL language supports all the familiar SQL operators. A complete list of functions and operators can be found in the CockroachDB documentation set².

The FROM clause

The FROM clause is the primary method of attaching table data to the SELECT statement. In its most simple incarnation, all rows and columns from a table can be fetched:

SELECT * FROM rides

Table names may be aliased using the AS clause or simply by following the table name with an alias. That alias can then be used anywhere in the query to refer to the table. Column names can also be aliased. For instance, the following are all equivalent:

SELECT name FROM users;
SELECT u.name FROM users u;
SELECT users.name FROM users u;
SELECT users.name AS user_name FROM users;
SELECT u.name FROM users AS u;

JOINS

Joins allow the results from two or more tables to be merged based on some common column values.

The inner join is the default join operation. In this join, rows from one table are joined to rows from another table based on some common (“key”) values. Rows that have no match in both tables are not included in the results. For instance, the following query links vehicle and ride information in the MOVR database:

SELECT v.id,v.ext,r.start_time r.start_address
  FROM vehicles v
  LEFT OUTER JOIN rides r
    ON (r.vehicle_id=v.id);

Note that a vehicle that had not been involved in a ride would not be included in the result set.

The ON clause specifies the conditions which join the two tables – in the above query, the columns vehicle_id in the rider table were matched with the id column in the vehicles table. If the join is on an identically named column in both tables, then the USING clause provides a handy shortcut. Here we join users and user_ride_counts using the common name column:

SELECT *
  FROM users u
  JOIN user_ride_counts urc
 USING (name)

The Outer join allows rows to be included even if they have no match in the other table. Rows that are not found in the outer join table are represented by NULL values. LEFT and RIGHT determine which table may have missing values. For instance, the following query prints all the users in the users table, even if some are not associated with a promo code:

SELECT u.name , upc.code
  FROM USERS u
  LEFT OUTER JOIN user_promo_codes upc
    ON (u.id=upc.user_id);

The RIGHT anti-join reverses the outer join. So, this query is identical to the previous query since the users table is now the “right” table in the join:

SELECT DISTINCT u.name , upc.code
  FROM user_promo_codes upc
  RIGHT OUTER JOIN USERS u
    ON (u.id=upc.user_id);

Anti-joins

It is often required to select all rows from a table that do not have a matching row in some other result set. This is called an anti-join, and while there is no SQL syntax for this concept, it is typically implemented using a subquery and the IN or EXISTS clause. The query planner recognizes this pattern and executes the query using a version of the JOIN machinery. The following examples illustrate the anti-join using the EXISTS and IN operators.

Each example selects employees who are not also customers.

SELECT *
  FROM users
 WHERE id NOT IN
       (SELECT id FROM employees)

This query returns the same results but using a correlated sub-query (we’ll discuss subqueries in more detail in an upcoming section):

SELECT *
   FROM users u
  WHERE NOT EXISTS
        (SELECT id
           FROM employees e
          WHERE e.id=u.id)

CROSS JOINS

CROSS JOIN indicates that every row in the left table should be joined to every row in the right table. Usually, this is a recipe for disaster unless one of the tables has only one row or is a laterally correlated subquery (see the section on correlated subqueries later in this chapter).

Set operations

SQL implements a number of operations that deal directly with result sets. These operations collectively referred to as “set operations” allow result sets to be concatenated, subtracted, or overlaid.

The most common of these operations is the UNION operator, which returns the sum of two result sets. By default, duplicates in each result set are eliminated. By contrast, the UNION ALL operation will return the sum of the two result sets, including any duplicates. The following example returns a list of customers and employees. Employees who are also customers will only be listed once:

SELECT name, address
  FROM customers
 UNION
SELECT name,address
  FROM employees;

INTERSECT returns those rows which are in both result sets. This query returns customers who are also employees:

SELECT name, address
  FROM customers
 INTERSECT
SELECT name,address
  FROM employees;

EXCEPT returns rows in the first result set which are not present in the second. This query returns customers who are not also employees:

SELECT name, address
  FROM customers
 EXCEPT
SELECT name,address
  FROM employees;

All set operations require that the component queries return the same number of columns and that those columns are of a compatible data type.

Group operations

Aggregate operations allow for summary information to be generated, typically upon groupings of rows. Rows can be grouped using the GROUP BY operator. If this is done, the select list must consist only of columns contained within the GROUP BY clause and aggregate functions.

The most common aggregate functions are shown in Table 4-1.

The following example generates summary ride information for each city:

SELECT u.city,SUM(urc.rides),AVG(urc.rides),max(urc.rides)
  FROM users u
  JOIN user_ride_counts urc
 USING (name)
 GROUP BY u.city

Subqueries

A subquery is a SELECT statement that occurs within another SQL statement. Such a “nested” SELECT statement can be used in a wide variety of SQL contexts, including SELECT, DELETE, UPDATE, and INSERT statements.

The following statement uses a subquery to count the number of rides that share the maximum ride length:

SELECT COUNT(*) FROM rides
 WHERE (end_time-start_time)=
 	(SELECT MAX(end_time-start_time) FROM rides );

Subqueries may also be used in the FROM clause wherever a table or view definition could appear. This query generates a result which compares each ride with the average ride duration for the city:

SELECT id, city,(end_time-start_time) ride_duration, avg_ride_duration
FROM rides
JOIN (SELECT city, AVG(end_time-start_time) avg_ride_duration
		FROM rides
		GROUP BY city)
	USING(city) ;

Correlated subquery

A correlated subquery is one in which the subquery refers to values in the parent query or operation. The subquery returns a potentially different result for each row in the parent result set. We saw an example of a correlated sub-query when performing an “anti-join” earlier in the chapter.

SELECT *
   FROM users u
  WHERE NOT EXISTS
        (SELECT id
           FROM employees e
          WHERE e.id=u.id)

Subqueries can often be used to perform an operation that is functionally equivalent to a join. In many cases, the query optimizer will transform these statements to joins so as to streamline the optimization process.

Lateral subquery

When a subquery is used in a join, the LATERAL keyword indicates that the subquery may access columns generated in preceding FROM table expressions. For instance, in the following query, the LATERAL keyword allows the subquery to access columns from the USERS table:

  SELECT name, address, start_time
   FROM USERS CROSS JOIN
        LATERAL (SELECT *
                   FROM rides
                  WHERE rides.start_address = users.address ) r;

This example is a bit contrived, and clearly, we could construct a simple join that performed this query more naturally. Where LATERAL joins really shine is in allowing subqueries to access computed columns in other subqueries within a FROM clause. See the CockroachDB documentation³ for a more serious example of lateral subqueries.

The WHERE clause

The WHERE clause is common to SELECT, UPDATE, and DELETE statements. It specifies a set of logical conditions that must evaluate to TRUE for all rows to be returned or processed by the SQL statement concerned.

Common Table expressions

SQL statements with a lot of subqueries can be hard to read and maintain, especially if the same subquery is needed in multiple contexts within the query. For this reason, SQL supports Common Table Expressions using the WITH clause. Figure 4-2 shows the syntax of a Common Table Expression.

Figure 4-2. Common Table Expression

In its simplest form, a Common Table Expression is simply a named query block that can be applied wherever a table expression can be used. For instance, here we create a Common Table Expression riderRevenue with the WITH clause, then refer to it in the FROM clause of the main query:

WITH riderRevenue AS (
	  SELECT u.id, SUM(r.revenue) AS sumRevenue
	    FROM rides r JOIN "users" u
	    ON (r.rider_id=u.id)
	   GROUP BY u.id)
SELECT * FROM "users" u2
         JOIN riderRevenue rr USING (id)
 ORDER BY sumrevenue DESC

The RECURSIVE clause allows the Common Table Expression to refer to itself, potentially allowing for a query to return an arbitrarily high (or even infinite) set of results. For instance, if the employees table contained a manager_id column which referred to the ‘manager’s row in the same table, then we could print a hierarchy of employees and managers as follows:

WITH RECURSIVE employeeMgr AS (
  SELECT id,manager_id, name , NULL AS manager_name, 1 AS level
    FROM employees managers
   WHERE manager_id IS NULL
  UNION ALL
  SELECT subordinates.id,subordinates.manager_id,
         subordinates.name, managers.name ,managers.LEVEL+1
    FROM employeeMgr managers
    JOIN employees subordinates
      ON (subordinates.manager_id=managers.id)
)
SELECT * FROM employeeMgr

The MATERIALIZED clause forces CockroachDB to store the results of the Common Table Expression as a temporary table rather than re-executing it on each occurrence. This can be useful if the Common Table Expression is referenced multiple times in the query.

ORDER BY

The ORDER BY clause allows query results to be returned in sorted order. Figure 4-3 shows the ORDER BY syntax.

Figure 4-3. Order By

In the simplest form, ORDER BY takes one or more column expressions or column numbers from the SELECT list.

In this example, we sort by column numbers:

SELECT city,start_time, (end_time-start_time) duration
   FROM rides r
  ORDER BY 1,3 DESC

And in this case by column expressions:

SELECT city,start_time, (end_time-start_time) duration
   FROM rides r
  ORDER BY city,(end_time-start_time)  DESC

You can also order by an index. In the following example, rows will be ordered by city and start_time, since those are the columns specified in the index:

CREATE INDEX rides_start_time ON rides (city ,start_time);


SELECT city,start_time, (end_time-start_time) duration
  FROM rides
 ORDER BY INDEX rides@rides_start_time;

The use of ORDER BY INDEX guarantees that the index will be used to directly return rows in sorted order, rather than having to perform a sort operation on the rows after they are retrieved. See Chapter 8 for more advice on optimizing statements that contain an ORDER BY.

Window functions

Window functions are functions that operate over a subset – a “window” of the complete set of the results. Figure 4-4 shows the syntax of a Window function.

Figure 4-4. Window Function Syntax

PARTITION BY and ORDER BY create a sort of “virtual table” that the function works with. For instance, this query lists the top ten rides in terms of revenue, with the percentage of the total revenue and city revenue displayed:

SELECT city, r.start_time ,revenue,
       revenue*100/SUM(revenue) OVER () AS pct_total_revenue,
       revenue*100/SUM(revenue) OVER (PARTITION BY city) AS pct_city_revenue
  FROM rides r
 ORDER BY 5 DESC
 LIMIT 10

There are some aggregation functions that are specific to Windowing functions. RANK() ranks the existing row within the relevant window, and DENSE_RANK() does the same while allowing no “missing” ranks. LEAD and LAG provide access to functions in adjacent partitions.

For instance, this query returns the top 10 rides, with each ‘ride’s overall rank and rank within the city displayed:

SELECT city, r.start_time ,revenue,
       RANK() OVER
           (ORDER BY revenue DESC) AS total_revenue_rank,
       RANK() OVER
            (PARTITION BY city ORDER BY revenue DESC) AS city_revenue_rank
  FROM rides r
 ORDER BY revenue DESC
 LIMIT 10;

Other SELECT clauses

The LIMIT clause limits the number of rows returned by a SELECT while the OFFSET clause “jumps ahead” a certain number of rows. This can be handy to paginate through a result set though it is almost always more efficient to use a filter condition to navigate to the next subset of results since otherwise, each request will need to reread and discard an increasing number of rows.

CockroachDB arrays

The ARRAY type allows a column to be defined as a one-dimensional array of elements, each of which shares a common data type. We’ll talk about arrays in the context of data modeling in the next chapter. Although they can be useful, they are strictly speaking a violation of the relational model and should be used carefully.

An ARRAY variable is defined by adding “[]” or the word “ARRAY” to the datatype of a column. For instance:

CREATE TABLE a (b STRING[]);
CREATE TABLE c (d INT ARRAY);

The ARRAY function allows us to insert multiple items into the ARRAY:

INSERT INTO a VALUES (ARRAY['sky', 'road', 'car']);
SELECT * FROM a;
        b
------------------
  {sky,road,car}

We can access an individual element of an array with the familiar array element notation:

SELECT arrayColumn[2] FROM arrayTable;
  arraycolumn
---------------
  Road

The “@>” operator can be used to find arrays that contain one or more elements:

SELECT * FROM arrayTable WHERE arrayColumn @>ARRAY['road'];
   arraycolumn
------------------
  {sky,road,car}

We can add elements to an existing array using the ARRAY_APPEND function and remove elements using ARRAY_REMOVE.

UPDATE  arrayTable
   SET arrayColumn=array_append(arrayColumn,'cat')
  WHERE arrayColumn @>ARRAY['car']
 RETURNING arrayColumn ;

     arraycolumn
----------------------
  {sky,road,car,cat}

 UPDATE  arrayTable
   SET arrayColumn=array_remove(arrayColumn,'car')
  WHERE arrayColumn @>ARRAY['car']
  RETURNING arrayColumn;

   arraycolumn
------------------
  {sky,road,cat}

Finally, the UNNEST function transforms an array into a tabular result – one row for each element of the array. This can be used to “join” the contents of an array with data held in relational form elsewhere in the database. We show an example of this in the next chapter:

SELECT unnest(arrayColumn) FROM arrayTable;
  unnest
----------
  sky
  road
  cat

Working with JSON

The JSONB data type allows us to store JSON documents into a column, and CockroachDB provides operators and functions to help us work with JSON.

For these examples, we’ve created a table with a primary key CUSTOMERID and all data in a JSONB column JSONDATA. We can use the jsonb_pretty function to retrieve the JSON in a nicely formatted manner:

root@:26257/json> SELECT jsonb_pretty(jsondata) FROM customersjson WHERE customerid=1;
                     jsonb_pretty
------------------------------------------------------
  {
      "Address": "1913 Hanoi Way",
      "City": "Sasebo",
      "Country": "Japan",
      "District": "Nagasaki",
      "FirstName": "MARY",
      "LastName": "Smith",
      "Phone": 886780309,
      "_id": "5a0518aa5a4e1c8bf9a53761",
      "dateOfBirth": "1982-02-20T13:00:00.000Z",
      "dob": "1982-02-20T13:00:00.000Z",
      "randValue": 0.47025846594884335,
      "views": [
          {
              "filmId": 611,
              "title": "MUSKETEERS WAIT",
              "viewDate": "2013-03-02T05:26:17.645Z"
          },
          {
              "filmId": 308,
              "title": "FERRIS MOTHER",
              "viewDate": "2015-07-05T20:06:58.891Z"
          },
          {
              "filmId": 159,
              "title": "CLOSER BANG",
              "viewDate": "2012-08-04T19:31:51.698Z"
          },
          /* Some data removed */
      ]
  }

Each JSON document contains some top-level attributes and a nested array of documents that contains details of films that they have streamed.

We can reference specific JSON attributes in the SELECT clause using the “->” operator:

root@:26257/json> SELECT jsondata->'City' AS City
  FROM customersjson WHERE customerid=1;

    city
------------
  "Sasebo"

The “->>” operator is similar but returns the data formatted as text, not JSON.

If we want to search inside a JSONB column, we can use the “@>” operator:

SELECT COUNT(*) FROM customersjson
 WHERE jsondata @> '{"City": "London"}';

  count
---------
      3

We often get the same result using the “->>” operator:

SELECT COUNT(*) FROM customersjson
 WHERE jsondata->>'City' = 'London';

  count
---------
      3

We can interrogate the structure of the JSON document using the JSONB_EACH and JSONB_OBJECT_KEYS functions. JSONB_EACH returns one row per attribute in the JSON document, while JSONB_OBJECT_KEYS returns just the attribute keys. This is useful if you don’t know what is stored inside the JSONB column.

JSONB_ARRAY_ELEMENTS returns one row for each element in a JSON array. For instance, here we expand out the VIEWS array for a specific customer, counting the number of movies that they have seen:

SELECT COUNT(jsonb_array_elements(jsondata->'views'))
  FROM customersjson
 WHERE customerid =1;

  count
---------
     37
(1 row)

Summary of SELECT

The SELECT statement is probably the most widely used statement in database programming and offers a very wide range of functionality. Even after decades of working in the field, the three of us don’t know every nuance of SELECT functionality. However, here we’ve tried to provide you with the most important aspects of the language. For more depth, view the CockroachDB documentation set⁴.

Although some database professionals use SELECT almost exclusively, the majority will be creating and manipulating data as well. In the following sections, we’ll look at the language features that support those activities.

Column Definitions

A column definition consists of a column name, datatype, nullability status, default value, and possibly column-level constraint definitions. At a minimum, the name and data type must be specified. Figure 4-6 shows the syntax for a column definition.

Figure 4-6. Column Definition

Although constraints may be specified directly against column definitions, they may also be independently listed below the column definitions. Many practitioners prefer to list the constraints separately in this manner because it allows all constraints, including multi-column constraints, to be located together.

Computed Columns

CockroachDB allows tables to include computed columns that in some other databases would require a view definition.

column_name AS expression [STORED|VIRTUAL]

A VIRTUAL computed column is evaluated whenever it is referenced. A STORED expression is stored in the database when created and need not always be recomputed.

For instance, this table definition has the firstName and lastName concatenated into a fullName column:

CREATE TABLE people
 (
     id INT PRIMARY KEY,
     firstName VARCHAR NOT NULL,
     lastName VARCHAR NOT NULL,
     dateOfBirth DATE NOT NULL,
     fullName STRING AS (CONCAT(firstName,' ',lastName) ) STORED,

 )

Computed columns cannot be context-dependent. That is, the computed value must not change over time or be otherwise non-deterministic. For instance, the computed column would not work since the age column would be static rather than recalculated every time. While it might be nice to stop aging in real life, we probably want the age column to increase as time goes on.

CREATE TABLE people
 (
     id INT PRIMARY KEY,
     firstName VARCHAR NOT NULL,
     lastName VARCHAR NOT NULL,
     dateOfBirth timestamp NOT NULL,
     fullName STRING AS (CONCAT(firstName,' ',lastName) ) STORED,
     age int AS (now()-dateOfBirth) STORED
 );

Datatypes

The base CockroachDB datatypes are shown in Table 4-3 . See https://www.cockroachlabs.com/docs/stable/data-types.html for more details.

Note that other datatype names may be aliased against these CockroachDB base types. For instance, the PostgreSQL types BIGINT and SMALLINT are aliased against the CockroachDB type INT.

In CockroachDB, datatypes may be cast – or converted – by appending the datatype to an expression using “::”. For instance:

SELECT revenue::int FROM rides

The CAST function can also be used to convert data types and is more broadly compatible with other databases and SQL standards. For instance:

SELECT CAST(revenue AS int) FROM rides

Primary Keys

As we know, a primary key uniquely defines a row within a table. In CockroachDB, a primary key is mandatory since all tables are distributed across the cluster based on the ranges of their primary key. If you ‘don’t specify a primary key, a key will be automatically generated for you.

It’s common practice to define an auto-generating primary key using clauses such as AUTOINCREMENT. The generation of primary keys in distributed databases is a significant issue since it’s the primary key that is used to distribute data across nodes in the cluster. We’ll discuss the options for primary key generation in the next chapter, but for now, we’ll simply note that you can generate randomized primary key values using the UUID datatype with the gen_random_uuid() function as the default value:

CREATE TABLE people (
        id UUID NOT NULL DEFAULT gen_random_uuid(),
        firstName VARCHAR NOT NULL,
        lastName VARCHAR NOT NULL,
        dateOfBirth DATE NOT NULL
 );

This pattern is considered best practice to ensure even distribution of keys across the cluster. Other options for autogenerating primary keys will be discussed in Chapter 5.

Constraints

The CONSTRAINT clause specifies conditions that must be satisfied by all rows within a table. In some circumstances, the CONSTRAINT keyword may be omitted, for instance, when defining a column constraint or specific constraint types such as PRIMARY KEY or FOREIGN KEY. Figure 4-7 shows the general form of a constraint definition.

Figure 4-7. Contraint Statement

A UNIQUE constraint requires that all values for the column or column_list be unique.

PRIMARY KEY implements a set of columns that must be unique and which can also be the subject of a FOREIGN KEY constraint in another table. Both PRIMARY KEY and UNIQUE constraints require the creation of an implicit index. If desired, the physical storage characteristics of the index can be specified in the USING. The options of the USING INDEX clause have the same usages as in the CREATE INDEX statement.

NOT NULL indicates that the column in question may not be NULL. This option is only available for column constraints, but the same effect can be obtained with a table CHECK constraint.

CHECK defines an expression that must evaluate to true for every row in the table. We’ll discuss best practices for creating constraints in Chapter 5.

Sensible use of constraints can help ensure data quality and can provide the database with a certain degree of self-documentation. However, some constraints have significant performance implications; we’ll discuss these implications in Chapter 5.

INDEXES

Indexes can be created by the CREATE INDEX statement, or an INDEX definition can be included within the CREATE TABLE statement.

We talked a lot about indexes in Chapter 2, and we’ll keep discussing indexes in the schema design and performance tuning chapters. Effective indexing is one of the most important success factors for a performance CockroachDB implementation.

Figure 4-8 illustrates a simplistic syntax for the CockroachDB CREATE INDEX statement.

Figure 4-8. Create Index Statement

We looked at the internals of CockroachDB indexes in Chapter 2. From a performance point of view, CockroachDB indexes behave much as indexes in other databases – providing a fast access method for locating rows with a particular set of non-primary key values. For instance, if we simply want to locate a row name and date of birth, we might create the following multi-column index:

CREATE INDEX people_namedob_ix ON people
 (lastName,firstName,dateOfBirth);

If we furthermore wanted to ensure that no two rows could have the same value for name and date of birth, we might create a unique index:

CREATE UNIQUE INDEX people_namedob_ix ON people
 (lastName,firstName,dateOfBirth);

The STORING clause allows us to store additional data in the index, which can allow us to satisfy queries using the index alone. For instance, this index can satisfy queries that retrieve phone numbers for a given name and date of birth:

CREATE UNIQUE INDEX people_namedob_ix ON people
 (lastName,firstName,dateOfBirth) STORING (phoneNumber);

CREATE TABLE people
 ( id UUID NOT NULL DEFAULT gen_random_uuid(),
   personData JSONB );

INSERT INTO people (personData)
VALUES('{
		"firstName":"Guy",
        "lastName":"Harrison",
        "dob":"21-Jun-1960",
        "phone":"0419533988",
        "photo":"eyJhbGciOiJIUzI1NiIsI..."
     }');

CREATE INVERTED INDEX people_inv_idx ON
people(personData);

SELECT *
FROM people
WHERE personData @> '{"phone":"0419533988"}';

ALTER TABLE people ADD phone STRING AS (personData->>'phone') VIRTUAL;

CREATE INDEX people_phone_idx ON people(phone);

CREATE TABLE people
( id INT PRIMARY KEY,
  firstName VARCHAR NOT NULL,
  lastName VARCHAR NOT NULL,
  dateOfBirth timestamp NOT NULL,
  phoneNumber VARCHAR NOT NULL,
  serialNo SERIAL ,
  INDEX serialNo_idx (serialNo) USING HASH WITH BUCKET_COUNT=4);

CREATE TABLE AS SELECT

The AS select clause of CREATE TABLE allows us to create a new table that has the data and attributes of a SQL SELECT statement. Columns, constraints and indexes can be specified as for an existing table but must align with the data types and number of columns returned by the SELECT statement. For instance, here we create a table based on a join and aggregate of two tables in the MOVR database:

CREATE TABLE user_ride_counts AS
SELECT u.name, COUNT(u.name) AS rides
  FROM "users" AS u JOIN "rides" AS r
    ON (u.id=r.rider_id)
 GROUP BY u.name

Note that while CREATE TABLE as SELECT can be used to create summary tables and the like, CREATE MATERIALIZED VIEW offers a more functional alternative.

ALTERING TABLES

The ALTER TABLE statement allows table columns or constraints to be added, modified, renamed or removed, as well as allowing for constraint validation and partitioning. Figure 4-9 shows the syntax

Figure 4-9. Alter Table Statement

Altering table structures online is not something to be undertaken lightly, although CockroachDB provides highly advanced mechanisms for propagating such changes without impacting availability and with minimal impact on performance⁵

DROPPING TABLES

Tables can be dropped using the DROP TABLE statement. Figure 4-10 shows the syntax.

Figure 4-10. Drop Table Statement

More than one table can be removed with a single DROP TABLE statement. The CASCADE keyword causes dependent objects such as views or foreign key constraints to be dropped as well. RESTRICT – the default – has the opposite effect; if there are any dependent objects, then the table will not be dropped.

VIEWS

A standard view is a query definition stored in the database which defines a virtual table. This virtual table can be referenced in the same way as a regular table. Common Table Expressions can be thought of as a way of creating a temporary view for a single SQL. If you had a Common Table Expression that you wanted to share amongst SQLs, then a view would be a logical solution.

A Materialised view stores the results of the view definition into the database so that the view need not be re-executed whenever encountered. This improves performance but may result in stale results. If you think of a view as a stored query, then a materialized view can be thought of as a stored result.

Figure 4-11 shows the syntax of the CREATE VIEW statement.

Figure 4-11. CREATE VIEW Statement

The REFRESH MATERIALIZED VIEW statement can be used to refresh the data underlying a materialized view.

IMPORT/IMPORT INTO

The IMPORT statement imports the following types of data into CockroachDB:

• Avro

• CSV/TSV

• Postgres dump files

• MySQL dump files

• CockroachDB dump files

IMPORT creates a new table, while IMPORT INTO allows an import into an existing table.

The files to be imported should exist either in a cloud storage bucket – Google Cloud Storage, Amazon S3 or Azure Blob storage – from a HTTP address or from the local filesystem (“nodelocal”).

We’ll discuss the various options for loading data into CockroachDB in Chapter 7. However, for now, let’s create a new table CUSTOMERS from a CSV file:

root@localhost:26257/defaultdb> IMPORT TABLE customers (
        id INT PRIMARY KEY,
        name STRING,
        INDEX name_idx (name)
)
CSV DATA ('nodelocal://1/customers.csv');
        job_id       |  status   | fra | rows | index_entries | bytes
---------------------+-----------+-----+------+---------------+--------
  659162639684534273 | succeeded |   1 |    1 |             1 |    47
(1 row)

Time: 934ms total (execution 933ms / network 1ms)

For a single node demo cluster, the nodelocal location will be somewhat dependent on your installation but will often be in an extern directory beneath the CockroachDB installation directory.

BEGIN TRANSACTION

The BEGIN statement commences a transaction and sets its properties. Figure 4-17 shows the syntax.

Figure 4-17. Begin Transaction

PRIORITY sets the transaction priority. In the event of a conflict, HIGH priority transactions are less likely to be retried.

READ ONLY specifies that the transaction is read-only and will not modify data.

AS OF SYSTEM TIME allows a READ ONLY transaction to view data from a snapshot of database history. We’ll come back to this in a few pages.

SAVEPOINT

SAVEPOINT creates a named rollback point that can be used as the target of a ROLLBACK statement. This allows a portion of a transaction to be discarded without discarding all of the transactions’ work. See ROLLBACK for more details.

COMMIT

The COMMIT statement commits the current transactions, making changes permanent.

Note that some transactions may require client-side intervention to handle retry scenarios. These patterns will be explored in Chapter 6.

ROLLBACK

ROLLBACK aborts the current transaction. Optionally, we can ROLLBACK to a savepoint, which rollbacks only the statements issued after the SAVEPOINT.

For instance, in the following example, the insert of the misspelled number ''tree is rollbacked and corrected without abandoning the transaction as a whole:

BEGIN ;

INSERT INTO numbers VALUES(1,'one');
INSERT INTO numbers VALUES(2,'two');
SAVEPOINT two;

INSERT INTO numbers VALUES(3,'tree');
ROLLBACK TO SAVEPOINT two;

INSERT INTO numbers VALUES(3,'three');
COMMIT;

The special savepoint cockroach_restart can be used to optimize transaction retries under certain circumstances. We’ll review this procedure in Chapter 6.

SELECT FOR UPDATE

The FOR UPDATE clause of a SELECT statement locks the rows returned by a query, ensuring that they cannot be modified by another transaction between the time they are read and when the transaction ends. This is typically used to implement the pessimistic locking pattern that we’ll discuss in Chapter 6.

A FOR UPDATE query should be executed within a transaction. Otherwise, the locks are released on completion of the SELECT statement.

A FOR UPDATE issued within a transaction will, by default, block other FOR UPDATE statements on the same rows or other transactions that seek to update those rows from completing until a COMMIT or ROLLBACK is issued. However, if a higher priority transaction attempts to update the rows or attempt to issue a FOR UPDATE, then the lower priority transaction will be aborted and will need to retry.

We’ll discuss the mechanics of transaction retries in Chapter 6.

Table 4-4 illustrates two FOR UPDATE statements executing concurrently. The first FOR UPDATE holds locks on the affected rows, preventing the second session from obtaining those locks until the first session completes its transaction.

AS OF SYSTEM TIME

The AS OF SYSTEM TIME clause can be applied to SELECT and BEGIN TRANSACTION statements as well as in BACKUP and RESTORE operations. AS OF SYSTEM TIME specifies that a SELECT statement or all the statements in a READ ONLY transaction should execute on a snapshot of the database at that system time. These snapshots are made available by the Multi-Version Concurrency Control (MVCC) architecture described in Chapter 2.

The time can be specified as an offset, or an absolute timestamp, as in the following two examples:

SELECT * FROM rides r
   AS OF SYSTEM TIME '-1d';

 SELECT * FROM rides r
   AS OF SYSTEM TIME '2021-5-22 18:02:52.0+00:00'

The time specified cannot be older in seconds than the replication zone configuration parameter ttlseconds, which controls the maximum age of MVCC snapshots.