PostgreSQL data definition language

This page defines the syntax of the SQL data definition language (DDL) statements supported for PostgreSQL databases in the preview release of the PostgreSQL interface feature.

Notations used in the syntax

  • Square brackets [ ] indicate optional clauses.
  • Curly braces { } enclose a set of options.
  • The vertical bar | indicates a logical OR.
  • A comma followed by an ellipsis indicates that the preceding item can repeat in a comma-separated list.
    • item [, ...] indicates one or more items, and
    • [item, ...] indicates zero or more items.
  • Purple-colored text, such as item, marks Cloud Spanner extensions to open-source PostgreSQL.
  • Parentheses ( ) indicate literal parentheses.
  • A comma , indicates the literal comma.
  • Angle brackets <> indicate literal angle brackets.
  • Uppercase words, such as INSERT, are keywords.

DATABASE statements

CREATE DATABASE

When creating a Cloud Spanner database, you must provide a CREATE DATABASE statement, which defines the ID of the database.

CREATE DATABASE name

ALTER DATABASE

Changes the definition of a database.

ALTER DATABASE name SET configuration_parameter_def

ALTER DATABASE name RESET configuration_parameter



where configuration_parameter_def is:

    {
        spanner.optimizer_version { TO | = } { 1 ... 3 | DEFAULT } |
        spanner.version_retention_period { TO | = } { 'duration' | DEFAULT }
    }


and configuration_parameter is:

    {
        spanner.optimizer_version |
        spanner.version_retention_period
    }

Cloud Spanner extensions to open-source PostgreSQL

spanner.optimizer_version { TO | = } { 1 ... 3 | DEFAULT }

  • This configuration parameter allows you to specify the query optimizer version to use. Specify DEFAULT to use the current default version, as listed in Query optimizer.

spanner.version_retention_period { TO | = } { 'duration' | NULL }

  • This configuration parameter allows you to specify the period for which Cloud Spanner retains all versions of data and schema for the database. The duration must be in the range [1h, 7d] and can be specified in days, hours, minutes, or seconds. For example, the values 1d, 24h, 1440m, and 86400s are equivalent. Setting the value to NULL resets the retention period to the default, which is 1 hour. This option can be used for point-in-time recovery. For more information, see Point-in-time Recovery.

TABLE statements

CREATE TABLE

Defines a new table.

CREATE TABLE table_name (
        [ {
            column_name data_type [ column_constraint [ ... ] ] |
            table_constraint
        } [, ... ] ]
    )
    [ INTERLEAVE IN PARENT parent_table_name [ ON DELETE ( CASCADE | NO ACTION ) ] ]


where column_constraint is:

    [ CONSTRAINT constraint_name ] {
        NOT NULL |
        NULL |
        CHECK ( expression ) |
        PRIMARY KEY |
        REFERENCES reftable ( refcolumn )
            [ ON DELETE NO ACTION ]
    }


and table_constraint is:

    [ CONSTRAINT constraint_name ] {
        CHECK ( expression ) |
        PRIMARY KEY ( column_name [, ... ] ) |
        FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable ( refcolumn [, ... ] )
            [ ON DELETE NO ACTION ]
    }

Cloud Spanner extensions to open-source PostgreSQL

INTERLEAVE IN PARENT parent_table_name [ ON DELETE ( CASCADE | NO ACTION ) ]

  • This clause defines a child-to-parent table relationship, which results in a physical interleaving of parent and child rows. The primary-key columns of a parent must positionally match, both in name and type, a prefix of the primary-key columns of any child. Adding rows to the child table fails if the corresponding parent row does not exist. The parent row can either already exist in the database or can be inserted before the insertion of the child rows in the same transaction.

  • The optional ON DELETE clause defines the behavior of rows in a child table when a transaction attempts to delete the parent row. The supported options are:

    • CASCADE: the child rows are deleted.

    • NO ACTION: the child rows are not deleted. If deleting a parent would leave behind child rows, thus violating parent-child referential integrity, the transaction attempt fails.

    If you omit the ON DELETE clause, the behavior is that of ON DELETE NO ACTION.

Cloud Spanner differences from open-source PostgreSQL

Cloud Spanner might choose a different name for an anonymous constraint than would open-source PostgreSQL. Therefore, if you depend on constraint names, specify them explicitly using CONSTRAINT constraint_name.

ALTER TABLE

Changes the definition of a table.

ALTER TABLE name action [, ... ]


where action is one of:

    ADD [ COLUMN ] column_name data_type [ NULL ]

    DROP [ COLUMN ] column_name

    ADD table_constraint

    DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]


and table_constraint is:

    [ CONSTRAINT constraint_name ] {
        CHECK ( expression ) |
        FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable ( refcolumn [, ... ] )
            [ ON DELETE NO ACTION ]
    }

Cloud Spanner differences from open-source PostgreSQL

  • These Cloud Spanner restrictions apply when dropping a column:
    • You cannot drop a column referenced by a CHECK constraint.
    • You cannot drop a primary key column.
  • Cloud Spanner might choose a different name for an anonymous constraint than would open-source PostgreSQL. Therefore, if you depend on constraint names, specify them explicitly using CONSTRAINT constraint_name.

DROP TABLE

Removes a table.

DROP TABLE name

INDEX statements

CREATE INDEX

CREATE [ UNIQUE ] INDEX name ] ON table_name
    ( { column_name } [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WHERE predicate ]


where predicate is:

    column_name IS NOT NULL
        [ AND column_name IS NOT NULL ] [ ... ]

Cloud Spanner differences from open-source PostgreSQL

[ ASC | DESC ] [ NULLS { FIRST | LAST } ]

  • Cloud Spanner does not support the ASC NULLS LAST and DESC NULLS FIRST combinations.
  • Cloud Spanner treats ASC without a NULLS specification as equivalent to ASC NULLS FIRST, and DESC without a NULLS specification as equivalent to DESC NULLS LAST.

[ WHERE predicate ]

  • The predicate can refer only to columns that are specified earlier in the CREATE INDEX statement, not to any column in the underlying table.

DROP INDEX

Removes a secondary index.

DROP INDEX name