PostgreSQL data definition language

This page defines the syntax of the SQL data definition language (DDL) statements supported for PostgreSQL-dialect databases.

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 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.

SCHEMA statements

This section has information about SCHEMA statements.

CREATE SCHEMA

Creates a new schema and assigns a name.

CREATE SCHEMA [schema_name]

Spanner differences from open source PostgreSQL

schema_name

  • Contains a name for a schema. If not used, Spanner assigns the name DEFAULT to the schema.

DROP SCHEMA

Removes a named schema.

DROP SCHEMA schema_name [, ...]

Spanner differences from open source PostgreSQL

schema_name

  • Contains the name of the schema that you want to drop.
  • CASCADE is not supported.

DATABASE statements

This section has information about DATABASE statements.

CREATE DATABASE

Creates a new database and assigns an ID.

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 the configuration_parameter_def is:

    {
        spanner.default_leader { TO | = } { 'region' | DEFAULT } |
        spanner.optimizer_version { TO | = } { 1 ... 7 | DEFAULT } |
        spanner.optimizer_statistics_package { TO | = } { 'package_name' | DEFAULT } |
        spanner.version_retention_period { TO | = } { 'duration' | DEFAULT }
    }

and the configuration_parameter is:

    {
        spanner.default_leader |
        spanner.optimizer_version |
        spanner.optimizer_statistics_package |
        spanner.version_retention_period
    }

Spanner differences from open source PostgreSQL

spanner.default_leader { TO | = } { 'region' | DEFAULT }

  • This configuration parameter lets you specify the leader for your database. The only regions eligible to become the leader region for your database are the read-write regions in the dual-region or multi-region configuration. Use DEFAULT to choose the default leader region of the base instance configuration. For more information about leader regions and voting replicas, see Replication.

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

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

spanner.optimizer_statistics_package { TO | = } { 'package_name' | DEFAULT }

  • This configuration parameter lets you specify the query optimizer statistics package name to use. By default, this is the latest collected statistics package, but you can specify any available statistics package version. Use DEFAULT for the latest version. For more information, see Query statistics package versioning.

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

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

PLACEMENT statements

This section has information about PLACEMENT statements.

CREATE PLACEMENT

Use the CREATE PLACEMENT statement to define a placement to partition row data in your database. For more information, see Geo-partitioning overview.

Syntax

CREATE PLACEMENT placement_name WITH [ partition_def ]

where partition_def is:
    { ( instance_partition='partition_id' [, default_leader='leader_region_id' ] ) }

Description

CREATE PLACEMENT defines a new placement in the current database.

Parameters

placement_name

  • The name of the placement.

partition_id

  • The unique identifier of the user-created partition associated with the placement.

leader_region_id

  • This optional parameter sets the default leader region for the partition. This parameter is similar to setting the default leader at the database level, except it applies to only the partition.

INDEX statements

This section has information about INDEX statements.

CREATE INDEX

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

where predicate is:

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

Spanner differences from open source PostgreSQL

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

  • NULLS default ordering is different between open source PostgreSQL

    and Spanner.

    When the NULLS FIRST/LAST option is not specified explicitly in the ORDER BY clause, open source PostgreSQL orders nulls before non-null values (equivalent to ASC NULLS FIRST) in ASC order and after non-null values in DESC order (equivalent to DESC NULLS LAST).

    Spanner orders nulls after non-null values (equivalent to ASC NULLS LAST) in ASC order and before non-null values in DESC order (equivalent to DESC NULLS FIRST).

  • INTERLEAVE IN clause defines a table to interleave the index in (see Index options for more details). If T is the table into which the index is interleaved, then:

    • T must be a parent of the table being indexed.
    • The primary key of T must be the key prefix of the index.

[ 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.

ALTER INDEX

Adds or removes a non-key column from an index.

ALTER INDEX index_name {ADD|DROP} INCLUDE COLUMN column_name

DROP INDEX

Removes a secondary index.

DROP INDEX [ IF EXISTS ] name

SEQUENCE statements

This section describes SEQUENCE statements.

CREATE SEQUENCE

Creates a sequence object with the specified attributes.

CREATE SEQUENCE [ IF NOT EXISTS ] sequence_name
sequence_kind
[ NO MINVALUE ]
[ NO MAXVALUE ]
[ SKIP RANGE skip_range_min skip_range_max ]
[ START COUNTER [ WITH ] start_with_counter ]
[ NO CYCLE ]
[ OWNED BY NONE ]

Spanner differences from open source PostgreSQL

Bit-reversed positive sequences don't support the following open source PostgreSQL SEQUENCE clauses:

  • [ AS data_type ]
  • [ INCREMENT [ BY ] increment ]
  • [ MINVALUE minvalue ] [ MAXVALUE maxvalue ]
  • [ START [ WITH ] start ]
  • [ CACHE cache ]
  • [ CYCLE ]

Spanner extends open source PostgreSQL with the following:

sequence_kind

  • Inputs a string to indicate the type of sequence to use. At this time, bit_reversed_positive is the only valid sequence kind.

[ SKIP RANGE skip_range_min skip_range_max ]

  • Restricts the sequence from generating values in that range. The skipped range is inclusive. Since bit-reversed positive sequences only generate positive values, setting a negative SKIP RANGE has no effect.

  • skip_range_min and skip_range_max are both bigint value types. They both have a default value of null. The accepted values for skip_range_min is any value that is lesser than or equal to skip_range_max. The accepted values for skip_range_max is any value that is more than or equal to skip_range_min.

[ START COUNTER [ WITH ] start_with_counter ]

  • Sets the next value for the internal sequence counter. For example, the next time that Spanner obtains a value from the bit-reversed sequence, it begins with start_with_counter. Spanner bit reverses this value before returning it to the client.

  • start_with_counter is an bigint value type. The default value is 1 and it accepts positive bigint values.

  • When the counter reaches the maximum in the bigint number space, the sequence no longer generate values. The sequence generator function, nextval() returns an error when it reaches the maximum number of values.

Examples

In the following example, you create a positive bit-reversed positive sequence. When you create a table, you can use nextval, the sequence generator function, as the default value of the primary key column, SingerId. Values the sequence generates are positive and bit-reversed.

CREATE SEQUENCE mysequence bit_reversed_positive;

CREATE TABLE Singers (
  SingerId bigint DEFAULT nextval('mysequence'),
  Name bigint,
  PRIMARY KEY (SingerId)
);

ALTER SEQUENCE

ALTER SEQUENCE makes changes to the specified sequence. Executing this statement doesn't affect values that the sequence previously generated. If the ALTER SEQUENCE statement doesn't include an option, the current value of the option remains the same.

After you execute ALTER SEQUENCE, the specified sequence uses the new schema options.

ALTER SEQUENCE [ IF EXISTS ] sequence_name
[ NO MINVALUE ]
[ NO MAXVALUE ]
[ SKIP RANGE skip_range_min skip_range_max ]
[ RESTART COUNTER [ WITH ] counter_restart ]
[ NO CYCLE ]

Spanner differences from open source PostgreSQL

Bit-reversed positive sequences don't support the following open source PostgreSQL ALTER SEQUENCE clauses:

  • [ AS data_type]
  • [ INCREMENT [ BY ] increment]
  • [ MINVALUE minvalue]
  • [ MAXVALUE maxvalue]
  • [ START [ WITH ] start ]
  • [ RESTART [ WITH ] restart ]
  • [ CACHE cache ]
  • ALTER SEQUENCE [ IF EXISTS ] name SET { LOGGED | UNLOGGED }
  • ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
  • ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name
  • ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema

Spanner extends open source PostgreSQL with the following:

[ SKIP RANGE skip_range_min skip_range_max ]

  • Restricts the sequence from generating values in the specified range. Since positive bit-reversed sequences only generate positive values, setting a negative SKIP RANGE has no effect.

[ RESTART COUNTER [WITH] counter_restart ]

  • Sets the current sequence counter to the user-specified value.

Examples

# Alter a sequence to include an skipped range.
ALTER SEQUENCE mysequence SKIP RANGE 1 1234567;

# Set the current sequence counter to 1000.
ALTER SEQUENCE mysequence RESTART COUNTER WITH 1000;

DROP SEQUENCE

Drops a sequence.

Syntax

DROP SEQUENCE [IF EXISTS] sequence_name

Spanner differences from open source PostgreSQL

Bit-reversed positive sequences don't support the following open source PostgreSQL DROP SEQUENCE clauses:

  • [CASCADE]
  • [RESTRICT]

Description

DROP SEQUENCE drops a specific sequence. Spanner can't drop a sequence if its name appears in a sequence function used in a column default value or view.

STATISTICS statements

This section has information about STATISTICS statements.

ALTER STATISTICS

Sets optional attributes of a query optimizer statistics package.

Syntax

ALTER STATISTICS spanner."<package_name>"
    action

where package_name is:
    {a—z}[{a—z|0—9|_|-}+]{a—z|0—9}

and action is:
    SET OPTIONS ( options_def )

and options_def is:
    { allow_gc = { true | false } }

Description

ALTER STATISTICS sets optional attributes of a query optimizer statistics package.

SET OPTIONS

  • Use this clause to set an option on the specified statistics package.

Parameters

package_name

  • The name of an existing query optimizer statistics package whose attributes you want to alter.

    To fetch existing statistics packages:

    SELECT s.package_name AS package_name, s.allow_gc AS allow_gc FROM INFORMATION_SCHEMA.SPANNER_STATISTICS s;

options_def

  • The allow_gc = { true | false } option lets you specify whether a given statistics package undergoes garbage collection. A package must be set as allow_gc=false if the package is used in a query hint. For more information, see Garbage collection of statistics packages.

ANALYZE

Starts a new query optimizer statistics package construction.

Syntax

ANALYZE

Description

ANALYZE starts a new query optimizer statistics package construction.

TABLE statements

This section has information about TABLE statements.

CREATE TABLE

Defines a new table.

CREATE TABLE [ IF NOT EXISTS ] table_name (

      {
        column_name data_type
        [ column_constraint [ ... ] ] | table_constraint |
        synonym_definition
      } [, ... ]
    )
    [ { INTERLEAVE IN PARENT parent_table_name [ ON DELETE ( CASCADE | NO ACTION ) ] |
    TTL INTERVAL interval_spec ON timestamp_column_name } ]

where column_constraint is:

    [ CONSTRAINT constraint_name ] {
        NOT NULL |
        NULL |
        CHECK ( expression ) |
        DEFAULT expression |
        GENERATED ALWAYS AS ( expression ) { STORED | VIRTUAL}|
        PRIMARY KEY |
        REFERENCES reftable ( refcolumn )
            [ ON DELETE {CASCADE | NO ACTION} ]
    }

and table_constraint is:

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

and synonym_definition is:

    [ SYNONYM (synonym) ]

DEFAULT expression

  • This clause sets a default value for the column.
  • You can use a key or non-key column for a column with a default value..
  • You can't create a column with a default value if it's a generated column.
  • You can insert your own value into a column that has a default value, overriding the default value. You can also use UPDATE ... SETcolumn-name= DEFAULTto reset a non-key column to its default value.
  • A generated column or a check constraint can depend on a column with a default value.
  • A column with a default value can't be a commit timestamp column. You can't use SPANNER.PENDING_COMMIT_TIMESTAMP() as a default value.

  • You can use a literal or any valid SQL expression that is assignable to the column data type as an expression, with the following properties and restrictions:

    • The expression can be nondeterministic.
    • The expression can't reference other columns.
    • The expression can't contain subqueries, query parameters, aggregates, or analytic functions.

GENERATED ALWAYS AS (expression) { STORED | VIRTUAL }

  • This clause creates a column as a generated column. Its value is defined as a function of other columns in the same row.

  • You can use a literal or any valid SQL expression that is assignable to the column data type as an expression, with the following restrictions:

    • The expression can only reference columns in the same table.

    • The expression can't contain subqueries.

    • The expression can't contain nondeterministic functions such as SPANNER.PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE, and CURRENT_TIMESTAMP.

    • You can't modify the expression of a generated column.

  • The STORED attribute that follows the expression causes Spanner to store the result of the function along with other columns of the table. Subsequent updates to any of the referenced columns causes Spanner to re-evaluate and store the expression.

  • The VIRTUAL attribute that follows the expression in Spanner doesn't store the result of the expression in the table.

  • Spanner doesn't allow direct writes to generated columns.

  • You can't use a commit timestamp column as a generated column, nor can any of the columns that the generated columns references.

  • You can't change the data type of a STORED generated column, or of any columns that the generated column references.

  • You can't drop a column the generated column references.

  • The following rules apply when using generated key columns:

    • The generated primary key can't reference other generated columns.

    • The generated primary key can reference, at most, one non-key column.

    • The generated primary key can't depend on a non-key column with a DEFAULT clause.

    • The DML doesn't let you explicitly write to generated primary keys.

Spanner differences from open source PostgreSQL

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

Spanner extends open source PostgreSQL with the following:

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 doesn't 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 which deletes the child rows.

    • NO ACTION which doesn't delete the child rows. 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.

TTL INTERVAL interval_spec ON timestamp_column_name

  • This clause defines a time to live (TTL) policy on the table, which lets Spanner periodically delete data from the table.

    • interval_spec is the number of days past the timestamp in the timestamp_column_name in which Spanner marks the row for deletion. You must use a non-negative integer for the value and it must evaluate to a whole number of days. For example, '3 days' is allowed, but '3 days - 2 minutes' returns an error.

    • timestamp_column_name is a column with data type TIMESTAMPTZ. You can create this column in the CREATE TABLE statement. Columns with commit timestamps are valid, as are generated columns. However, you can't specify a generated column that references a commit timestamp column.

[ SYNONYM (synonym)]

  • Defines a synonym for a table, which is an additional name that an application can use to access the table. A table can have one synonym. You can only use a synonym in queries and DML. You can't use the synonym for DDL or schema changes. You can see the synonym in the DDL representation of the table.

ALTER TABLE

Changes the definition of a table.

ALTER TABLE [ IF EXISTS ] [ ONLY ] name action

where action is one of:

    ADD SYNONYM synonym
    DROP SYNONYM synonym
    RENAME TO new_table_name [, ALTER TABLE [ IF EXISTS ] [ ONLY ] new_table_name RENAME TO new_table_name ...]
    RENAME WITH SYNONYM TO new_table_name
    ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ column_expression ]
    DROP [ COLUMN ] column_name
    ADD table_constraint
    DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
    ALTER COLUMN column_name
      {
        [ SET DATA ] TYPE data_type
        | { SET | DROP } NOT NULL
        | SET  DEFAULT expression
        | DROP DEFAULT
      }
    ADD TTL INTERVAL interval_spec ON timestamp_column_name 
    ALTER TTL INTERVAL interval_spec ON timestamp_column_name 

and column_expression is:
    [ NOT NULL ] { DEFAULT expression | GENERATED ALWAYS AS ( expression ) STORED }

and table_constraint is:

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

  • You can specify NOT NULL in an ALTER TABLE...ADD [ COLUMN ] statement if you specify DEFAULTexpression or GENERATED ALWAYS AS (expression) STORED for the column.

  • If you include DEFAULTexpression or GENERATED ALWAYS AS (expression) STORED, Spanner evaluates the expression and backfills the computed value for existing rows. The backfill operation is asynchronous. This backfill operation only happens when Spanner issues an ADD COLUMN statement.

  • ALTER COLUMN statements that you use to SET or DROP the default value of an existing column don't affect existing rows. There is no backfill operation on ALTER COLUMN.

  • The DEFAULT clause has restrictions. See the description of this clause in CREATE TABLE.

Spanner differences from open source PostgreSQL

  • These Spanner restrictions apply when dropping a column:
    • You can't drop a column that a CHECK constraint references.
    • You can't drop a primary key column.
  • Spanner might choose a different name for an anonymous constraint than open source PostgreSQL. Therefore, if you depend on constraint names, specify them explicitly using CONSTRAINT constraint_name.
  • The following Spanner restrictions apply when altering a column (using the ALTER COLUMN clause):
    • The statement must contain exactly two ALTER COLUMN clauses applied to the same column. One clause must alter (or keep as is) the column's data type and another clause must alter (or keep as is) the column's nullability. For example, if column c1 is nullable and you want it to stay nullable after the execution of the ALTER TABLE statement, you need to add ALTER COLUMN c1 DROP NOT NULL. For example: ALTER TABLE t1 ALTER COLUMN c1 TYPE VARCHAR(10), ALTER COLUMN c1 DROP NOT NULL;
    • Spanner only supports operations that the Supported schema updates section describes.
  • For all operations (ADD or DROP) except ALTER COLUMN, Spanner supports only a single operation per ALTER TABLE statement.

Spanner extends open source PostgreSQL with the following:

ADD TTL INTERVAL, ALTER TTL INTERVAL

  • This clause defines or alters a time to live (TTL) policy on the table, which lets Spanner periodically delete data from the table.
  • interval_spec is the number of days past the timestamp in the timestamp_column_name in which Spanner marks the row for deletion. You must use a non-negative integer for its value and it must evaluate to a whole number of days. For example, Spanner permits you to use '3 days', but '3 days - 2 minutes' returns an error.
  • timestamp_column_name is a column with the data type TIMESTAMPTZ. You need to create this column if it doesn't exist already. Columns with commit timestamps are valid, as are generated columns. However, you can't specify a generated column that references a commit timestamp column.

ADD SYNONYM

  • Adds a synonym to a table to give it an alternate name. You can use the synonym for reads, writes, queries, and for use with DML. You can't use ADD SYNONYM with DDL, such as to create an index. A table can have one synonym. For more information, see Add a synonym to a table.

DROP SYNONYM

RENAME TO

  • Renames a table without creating a synonym. In addition, you can concatenate multiple ALTER TABLE RENAME TO statements (delimited by a comma) to atomically rename multiple tables. For more information, see Rename a table.

    For example, to change the names of multiple tables atomically, do the following:

    ALTER TABLE Singers
    RENAME TO Artists,
    ALTER TABLE Albums
            RENAME TO Recordings;

RENAME WITH SYNONYM TO

  • Adds a synonym to a table so that when you rename the table, you can add the old table name to the synonym. This gives you time to update applications with the new table name while still allowing them to access the table with the old name. For more information, see Rename a table and add a synonym.

DROP TABLE

Removes a table.

DROP TABLE [ IF EXISTS ] name

Spanner differences from open source PostgreSQL

Spanner can't drop a table that has indexes. However, in open source PostgreSQL, when you drop a table, all related indexes are also dropped.

VIEW statements

This section has information about VIEW statements.

CREATE [OR REPLACE] VIEW

Defines a new view or replaces an existing view. If CREATE VIEW is used and the view already exists, the statement fails. Use CREATE OR REPLACE VIEW to replace the view or security type of a view. For more information, see About views.

{ CREATE | CREATE OR REPLACE } VIEW view_name
  SQL SECURITY { INVOKER | DEFINER }
  AS query

DROP VIEW

Removes a view. Only the view is dropped; the objects that it references are not.

DROP VIEW name

CHANGE STREAM statements

This section has information about CHANGE STREAM statements.

CREATE CHANGE STREAM

Defines a new change stream. For more information, see Create a change stream.

CREATE CHANGE STREAM change_stream_name
[ FOR { table_columns [, ... ] | ALL } ]
[ WITH ( configuration_parameter_def [, ... ] ) ]

where table_columns is:
    table_name [ ( [ column_name, ... ] ) ]

and configuration_parameter_def is:
    { retention_period = 'duration' |
      value_capture_type = { 'OLD_AND_NEW_VALUES' | 'NEW_ROW' |'NEW_VALUES' | 'NEW_ROW_AND_OLD_VALUES' } |
      exclude_ttl_deletes = { false | true } |
      exclude_insert = { false | true } |
      exclude_update = { false | true } |
      exclude_delete = { false | true } }

FOR {table_columns[, ... ] | ALL }

  • The FOR clause defines the tables and columns that the change stream watches.

  • You can specify a list of table_columns to watch, where table_columns can be either of the following:

    • table_name: This watches the entire table, including all of the future columns when they are added to this table.

    • table_name ( [column_name, ... ] ): You can optionally specify a list of zero or more non-key columns following the table name. This watches only the primary key and the listed non-key columns of the table. With an empty list of non-key columns, table_name() watches only the primary key.

  • ALL lets you watch all tables and columns in the entire database, including all of the future tables and columns as soon as they are created.

  • When you omit the FOR clause, the change stream watches nothing.

WITH (configuration_parameter_def[, ... ] )

  • The retention_period = 'duration' configuration parameter lets you specify how long a change stream retains its data. For duration you must use the range [1d, 7d] which you can specify in days, hours, minutes, or seconds. For example, the values 1d, 24h, 1440m, and 86400s are equivalent. The default is 1 day. For details, see Data retention.

  • The value_capture_type configuration parameter controls which values to capture for a changed row. It can be OLD_AND_NEW_VALUES (default), NEW_VALUES, NEW_ROW, OR NEW_ROW_AND_OLD_VALUES. For details, see Value capture type.

  • The exclude_ttl_deletes configuration parameter lets you filter out time to live based deletes from your change stream. When you set this filter, only future TTL-based deletes are removed. It can be set to false (default) or true. For more information, see TTL-based deletes filter.

  • The exclude_insert configuration parameter lets you filter out all INSERT table modifications from your change stream. It can be set to false (default) or true. For more information, see Table modification type filters.

  • The exclude_update configuration parameter lets you filter out all UPDATE table modifications from your change stream. It can be set to false (default) ortrue. For more information, see Table modification type filters.

  • The exclude_delete configuration parameter lets you filter out all DELETE table modifications from your change stream. It can be set to false (default) ortrue. For more information, see Table modification type filters.

ALTER CHANGE STREAM

Changes the definition of a change stream. For more information, see Modify a change stream.

ALTER CHANGE STREAM name action

where action is:
    { SET FOR { table_columns [, ... ] | ALL } |
      DROP FOR ALL |
      SET ( configuration_parameter_def [, ... ] ) |
      RESET ( configuration_parameter [, ... ] ) }

and table_columns is:
    table_name [ ( [ column_name, ... ] ) ]

and configuration_parameter_def is:
    { retention_period = { 'duration' | null } |
      value_capture_type = { 'OLD_AND_NEW_VALUES' | 'NEW_ROW' | 'NEW_VALUES' | 'NEW_ROW_AND_OLD_VALUES' | null } |
      exclude_ttl_deletes = { false | true | null } |
      exclude_insert = { false | true | null } |
      exclude_update = { false | true | null } |
      exclude_delete = { false | true | null } }

and configuration_parameter is:
    { retention_period | value_capture_type | exclude_ttl_deletes | exclude_insert | exclude_update |
      exclude_delete }

SET FOR {table_columns[, ... ] | ALL }

  • Sets a new FOR clause to modify what the change stream watches, using the same syntax as CREATE CHANGE STREAM.

DROP FOR ALL

SET

  • Sets configuration parameters on the change stream (such as retention_period, value_capture_type, exclude_ttl_deletes, exclude_insert, exclude_update and exclude_delete), using the same syntax as CREATE CHANGE STREAM.

  • Setting an option to null is equivalent to setting it to the default value.

RESET

  • Resets configuration parameters on the change stream (such as retention_period, value_capture_type, exclude_ttl_deletes, exclude_insert, exclude_update, and exclude_delete) to the default values.

DROP CHANGE STREAM

Removes a change stream and deletes its data change records.

DROP CHANGE STREAM name

ROLE statements

This section has information about ROLE statements.

CREATE ROLE

Defines a new database role.

Syntax

CREATE ROLE database_role_name

Description

CREATE ROLE defines a new database role. Database roles are collections of fine-grained access control privileges. You can create only one role with this statement.

Parameters

database_role_name

  • The name of the database role to create. Role names can't start with pg_. The role name public and role names starting with spanner_ are reserved for system roles.

Example

This example creates the database role hr_manager.

CREATE ROLE hr_manager

DROP ROLE

Drops a database role.

Syntax

DROP ROLE database_role_name

Description

DROP ROLE drops a database role. You can drop only one role with this statement.

You can't drop a database role if it has any privileges granted to it. All privileges granted to a database role must be revoked before the role can be dropped. You can drop a database role whether or not access to it is granted to IAM principals.

Dropping a role automatically revokes its membership in other roles and revokes the membership of its members.

You can't drop system roles.

Parameters

database_role_name

  • The name of the database role to drop.

Example

This example drops the database role hr_manager.

DROP ROLE hr_manager

GRANT and REVOKE statements

This section has information about GRANT and REVOKE statements.

GRANT

Grants roles to database objects.

Syntax

GRANT { SELECT | INSERT | UPDATE | DELETE } ON [TABLE] table_list TO role_list

GRANT { SELECT | INSERT | UPDATE }(column_list) ON [TABLE] table_list TO role_list

GRANT SELECT ON [TABLE] view_list TO role_list

GRANT SELECT ON CHANGE STREAM change_stream_list TO role_list

GRANT EXECUTE ON FUNCTION function_list TO role_list

GRANT role_list TO role_list

GRANT USAGE ON SCHEMA schema_name_list TO role_list

where table_list is:
      table_name [, ...]

and column_list is:
    column_name [,...]

and view_list is:
    view_name [, ...]

and change_stream_list is:
    change_stream_name [, ...]

and function_list is:
    change_stream_read_function_name [, ...]

and schema_name_list is:
    schema_name [, ...]

and role_list is:
    database_role_name [, ...]

Description

For fine-grained access control, grants privileges on one or more tables, views, change streams, or change stream read functions to database roles. Also grants database roles to other database roles to create a database role hierarchy with inheritance. When granting SELECT, INSERT, or UPDATE on a table, optionally grants privileges on only a subset of table columns.

Parameters

table_name

  • The name of an existing table.

column_name

  • The name of an existing column in the specified table.

view_name

  • The name of an existing view.

change_stream_name

  • The name of an existing change stream.

change_stream_read_function_name

database_role_name

  • The name of an existing database role.

Notes and restrictions

  • Identifiers for database objects named in the GRANT statement must use the case that was specified when the object was created. For example, if you created a table with a name that is in all lower case with a capitalized first letter, you must use that same case in the GRANT statement. For each change stream, PostgreSQL automatically creates a change stream read function with a name that consists of a prefix added to the change stream name, so ensure that you use the proper case for both the prefix and the change stream name. For more information about change stream read functions, see Change stream query syntax.

  • When granting column-level privileges on multiple tables, each table must contain the named columns.

  • If a table contains a column that is marked NOT NULL and has no default value, you can't insert into the table unless you have the INSERT privilege on that column.

  • After granting SELECT on a change stream to a role, grant EXECUTE on the change stream's read function to that role. For information about change stream read functions, see Change stream read functions and query syntax.

  • Granting SELECT on a table doesn't grant SELECT on the change stream that tracks it. You must make a separate grant for the change stream.

Examples

The following example grants SELECT on the employees table to the hr_rep role. Grantees of the hr_rep role can read all columns of employees.

GRANT SELECT ON TABLE employees TO hr_rep;

The next example is the same as the previous example, but with the optional TABLE keyword omitted.

GRANT SELECT ON employees TO hr_rep;

The next example grants SELECT on a subset of columns of the contractors table to the hr_rep role. Grantees of the hr_rep role can only read the named columns.

GRANT SELECT(name, address, phone) ON TABLE contractors TO hr_rep;

The next example mixes table-level and column-level grants. hr_manager can read all table columns, but can update only the location column.

GRANT SELECT, UPDATE(location) ON TABLE employees TO hr_manager;

The next example makes column-level grants on two tables. Both tables must contain the name, level, and location columns.

GRANT SELECT(name, level, location), UPDATE(location) ON TABLE employees, contractors TO hr_manager;

The next example grants INSERT on a subset of columns of the employees table.

GRANT INSERT(name, cost_center, location, manager) ON TABLE employees TO hr_manager;

The next example grants the database role pii_access to the roles hr_manager and hr_director. The hr_manager and hr_director roles are members of pii_access and inherit the privileges that were granted to pii_access. For more information, see Database role hierarchies and inheritance.

GRANT pii_access TO hr_manager, hr_director;

REVOKE

Revokes privileges on one or more tables, views, change streams, or change stream read functions.

Syntax

REVOKE { SELECT | INSERT | UPDATE | DELETE } ON [TABLE] table_list FROM role_list

REVOKE { SELECT | INSERT | UPDATE }(column_list) ON [TABLE] table_list FROM role_list

REVOKE SELECT ON [TABLE] view_list FROM role_list

REVOKE SELECT ON CHANGE STREAM change_stream_list FROM role_list

REVOKE EXECUTE ON FUNCTION function_list FROM role_list

REVOKE role_list FROM role_list

and table_list is:
    table_name [, ...]

and column_list is:
    column_name [,...]

and view_list is:
    view_name [, ...]

and change_stream_list is:
    change_stream_name [, ...]

and function_list is:
    change_stream_read_function_name [, ...]

and role_list is:
    database_role_name [, ...]

Description

For fine-grained access control, revokes privileges on one or more tables, views, change streams, or change stream read functions from database roles. Also revokes database roles from other database roles. When revoking SELECT, INSERT, or UPDATE on a table, optionally revokes privileges on only a subset of table columns.

Parameters

table_name

  • The name of an existing table.

column_name

  • The name of an existing column in the previously specified table.

view_name

  • The name of an existing view.

change_stream_name

  • The name of an existing change stream.

change_stream_read_function_name

database_role_name

  • The name of an existing database role.

Notes and restrictions

  • Identifiers for database objects named in the REVOKE statement must use the case that was specified when the object was created. For example, if you created a table with a name that is in all lower case with a capitalized first letter, you must use that same case in the REVOKE statement. For each change stream, PostgreSQL automatically creates a change stream read function with a name that consists of a prefix added to the change stream name, so ensure that you use the proper case for both the prefix and the change stream name. For more information about change stream read functions, see Change stream query syntax.

  • When revoking column-level privileges on multiple tables, each table must contain the named columns.

  • A REVOKE statement at the column level has no effect if privileges were granted at the table level.

  • After revoking SELECT on a change stream from a role, revoke EXECUTE on the change stream's read function from that role.

  • Revoking SELECT on a change stream doesn't revoke any privileges on the table that it tracks.

Examples

The following example revokes SELECT on the employees table from the role hr_rep.

REVOKE SELECT ON TABLE employees FROM hr_rep;

The next example is the same as the previous example, but with the optional TABLE keyword omitted.

REVOKE SELECT ON employees FROM hr_rep;

The next example revokes SELECT on a subset of columns of the contractors table from the role hr_rep.

REVOKE SELECT(name, address, phone) ON TABLE contractors FROM hr_rep;

The next example shows revoking both table-level and column-level privileges in a single statement.

REVOKE SELECT, UPDATE(location) ON TABLE employees FROM hr_manager;

The next example revokes column-level grants on two tables. Both tables must contain the name, level, and location columns.

REVOKE SELECT(name, level, location), UPDATE(location) ON TABLE employees, contractors FROM hr_manager;

The next example revokes INSERT on a subset of columns.

REVOKE INSERT(name, cost_center, location, manager) ON TABLE employees FROM hr_manager;

The following example revokes the database role pii_access from the hr_manager and hr_director database roles. The hr_manager and hr_director roles lose any privileges that they inherited from pii_access.

REVOKE pii_access FROM hr_manager, hr_director;