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 ... 8 | 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 ... 8 | 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 values1d
,24h
,1440m
, and86400s
are equivalent. Setting the value toDEFAULT
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 PostgreSQLand Spanner.
When the
NULLS FIRST/LAST
option is not specified explicitly in theORDER BY
clause, open source PostgreSQL orders nulls before non-null values (equivalent toASC NULLS FIRST
) inASC
order and after non-null values inDESC
order (equivalent toDESC 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 toDESC 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 theCREATE 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
andskip_range_max
are bothbigint
value types. They both have a default value of null. The accepted values forskip_range_min
is any value that is lesser than or equal toskip_range_max
. The accepted values forskip_range_max
is any value that is more than or equal toskip_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 anbigint
value type. The default value is1
and it accepts positivebigint
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 asallow_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 ... SET
column-name
= DEFAULT
to 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
, andCURRENT_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 ofON 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 thetimestamp_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 typeTIMESTAMPTZ
. You can create this column in theCREATE 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 anALTER TABLE...ADD [ COLUMN ]
statement if you specifyDEFAULT
expression
orGENERATED ALWAYS AS (
expression
) STORED
for the column.If you include
DEFAULT
expression
orGENERATED 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 anADD COLUMN
statement.ALTER COLUMN
statements that you use toSET
orDROP
the default value of an existing column don't affect existing rows. There is no backfill operation onALTER COLUMN
.The
DEFAULT
clause has restrictions. See the description of this clause inCREATE 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 columnc1
is nullable and you want it to stay nullable after the execution of theALTER TABLE
statement, you need to addALTER 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.
- The statement must contain exactly two
- For all operations (ADD or DROP) except
ALTER COLUMN
, Spanner supports only a single operation perALTER 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 thetimestamp_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 typeTIMESTAMPTZ
. 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
- Removes a synonym from a table. For more information, see Remove a synonym from a table.
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, wheretable_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 values1d
,24h
,1440m
, and86400s
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 beOLD_AND_NEW_VALUES
(default),NEW_VALUES
,NEW_ROW
, ORNEW_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 tofalse
(default) ortrue
. For more information, see TTL-based deletes filter.The
exclude_insert
configuration parameter lets you filter out allINSERT
table modifications from your change stream. It can be set tofalse
(default) ortrue
. For more information, see Table modification type filters.The
exclude_update
configuration parameter lets you filter out allUPDATE
table modifications from your change stream. It can be set tofalse
(default) ortrue
. For more information, see Table modification type filters.The
exclude_delete
configuration parameter lets you filter out allDELETE
table modifications from your change stream. It can be set tofalse
(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 asCREATE CHANGE STREAM
.
DROP FOR ALL
- Suspends a change stream to watch nothing.
SET
Sets configuration parameters on the change stream (such as
retention_period
,value_capture_type
,exclude_ttl_deletes
,exclude_insert
,exclude_update
andexclude_delete
), using the same syntax asCREATE 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
, andexclude_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 namepublic
and role names starting withspanner_
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
- The name of an existing read function for a change stream. For more information, see Change stream read functions and query syntax.
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 theGRANT
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 theINSERT
privilege on that column.After granting
SELECT
on a change stream to a role, grantEXECUTE
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 grantSELECT
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
- The name of an existing read function for a change stream. For more information, see Change stream read functions and query syntax.
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 theREVOKE
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, revokeEXECUTE
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;