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

Names

Naming rules in PostgreSQL-dialect databases are the same as those used inopen source PostgreSQL,except for the following:

• No two Spanner objects (schemas, tables, columns, indexes,views, role, constraints, or sequences) can be created with the same name,including names that differ only in capitalization. For example, the secondstatement in the following snippet fails because the table names differonly by case.

  CREATETABLEMyTable(col1INT64)PRIMARYKEY(col1);CREATETABLEMYTABLE(col1INT64)PRIMARYKEY(col1);

  The following snippet fails because two different objects have the samename:

  CREATETABLEMyTable(col1bigintPRIMARYKEY);CREATESCHEMAMyTable;

• Table and schema object names are case-sensitive but not case preserving.Case reverts to lowercase in the database. As an example, consider thetablesingers created with the following statement.

  CREATETABLEsingers(singeridbigintNOTNULLPRIMARYKEY,firstnamecharactervarying(1024),lastnamecharactervarying(1024),singerinfobytea,birthdatedate);

  The following command succeeds:

  CREATEINDEXsingersbyfirstlastnameONsingers(firstname,lastname)

  For the following table:

  CREATETABLEmytable2(col1bigintPRIMARYKEY);

  The following queries all succeed:

  SELECTcol1FROMMyTable2LIMIT1;SELECTCOL1FROMMYTABLE2LIMIT1;SELECTCOL1FROMmytable2LIMIT1;INSERTINTOMYTABLE2(col1)VALUES(1);

SCHEMA statements

This section has information aboutSCHEMA statements.

CREATE SCHEMA

Creates a new schema and assigns a name.

CREATESCHEMA[schema_name]

Spanner differences from open source PostgreSQL

schema_name

• Contains a name for a schema. If not used, the default schema is used, whichis the same schema referred to by usingpublic.
• When querying data, use fully qualified names (FQNs) to specify objects thatbelong to a specific schema. FQNs combine the schema name and the object nameto identify database objects. For example,products.albums for theproducts schema andalbums table. For more information, seeNamed schemas.

DROP SCHEMA

Removes a named schema.

DROPSCHEMAschema_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 aboutDATABASE statements.

CREATE DATABASE

Creates a new database and assigns an ID.

CREATEDATABASEname

ALTER DATABASE

Changes the definition of a database.

ALTERDATABASEnameSETconfiguration_parameter_defALTERDATABASEnameRESETconfiguration_parameterwheretheconfiguration_parameter_defis:{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}|spanner.default_sequence_kind{TO|=}{'bit_reversed_positive'|DEFAULT}|spanner.default_time_zone{TO|=}{'time_zone_name'|DEFAULT}|spanner.read_lease_regions{TO|=}{'read_lease_region_name'|DEFAULT}}andtheconfiguration_parameteris:{spanner.default_leader|spanner.optimizer_version|spanner.optimizer_statistics_package|spanner.version_retention_period|spanner.default_sequence_kind|spanner.default_time_zone|spanner.read_lease_regions}

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 arethe read-write regions in thedual-regionormulti-regionconfiguration. UseDEFAULT to choose the default leader region of the baseinstance configuration. For more information about leader regions andvoting replicas, seeReplication.

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

• This configuration parameter lets you specify the query optimizer version touse. UseDEFAULT for the current default version, as listed inQuery optimizer.

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

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

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

• This configuration parameter lets you specify the period for whichSpanner retains all versions of data and schema for thedatabase. 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 toDEFAULTresets the retention period to the default, which is 1 hour. You can usethis option for point-in-time recovery. For more information, seePoint-in-time Recovery.

spanner.default_sequence_kind { TO | = } { 'bit_reversed_positive' |DEFAULT }

• This configuration parameter lets you specify the default sequence kind foryour database.bit_reversed_positive is the onlyvalid sequence kind. Thebit_reversed_positive option specifies that thevalues generated by the sequence are of typebigint, are greater thanzero, and aren't sequential. You don't need to specify a sequence type whenusingdefault_sequence_kind. When you usedefault_sequence_kind for asequence or identity column, you can't change the sequence kind later. Formore information, seePrimary key default values management.

spanner.default_time_zone { TO | = } { 'time_zone_name' |DEFAULT }

• This configuration parameter lets you specify the default time zone for yourdatabase. If set toDEFAULT, the system usesAmerica/Los_Angeles.Specifying a time zone within aDATE orTIMESTAMP function overridesthis setting. Thetime_zone_name must be avalid entry from theIANA Time Zone Database. This option canonly be set on empty databases without any tables.

spanner.read_lease_regions { TO | = } { 'read_lease_region_name' |DEFAULT }

• This configuration parameter sets theread leaseregion for your database. By default, or when you set it toDEFAULT, thedatabase doesn't use any read lease regions. If you set one or more read leaseregions for your database, Spanner gives the right to servereads locally to one or more non-leader, read-write, or read-only regions.This allows the non-leader regions to directly serve strong reads and reducestrong read latency.

PLACEMENT statements

Preview —Geo-partitioning

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

This section has information aboutPLACEMENT statements.

CREATE PLACEMENT

Use theCREATE PLACEMENT statement to define a placement to partition rowdata in your database. For more information, seeGeo-partitioning overview.

Syntax

CREATEPLACEMENTplacement_nameWITH[partition_def]wherepartition_defis:{(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 theplacement.

leader_region_id

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

DROP PLACEMENT

Use theDROP PLACEMENT statement to delete a placement.

Syntax

DROPPLACEMENTplacement_name

Description

DROP PLACEMENT drops a placement.

Parameters

placement_name

• The name of the placement to drop.

LOCALITY GROUP statements

This section has information aboutLOCALITY GROUP statements.

CREATE LOCALITY GROUP

Use theCREATE LOCALITY GROUP statement to define a locality group to storesome columns separately or to use tiered storage. For more information,seeLocality groupsandTiered storage overview.

Syntax

CREATELOCALITYGROUPlocality_group_name[storage_def]wherestorage_defis:{STORAGE'{ ssd | hdd }'[,SSD_TO_HDD_SPILL_TIMESPAN'duration']}

Description

CREATE LOCALITY GROUP defines a new locality group in the current database.

Parameters

locality_group_name

• The name of the locality group.

storage_def

• UseSTORAGE to define the storage type of thelocality group. You can set the storage type as 'ssd' or 'hdd'.

• UseSSD_TO_HDD_SPILL_TIMESPAN to define theamount of time that data is stored in SSD storage before it moves to HDDstorage. After the specified time passes, Spanner migratesthe data to HDD storage during its normal compaction cycle, which typicallyoccurs over the course of seven days from the specified time. The durationmust be at least one hour (1h) and at most 365 days (365d) long. It canbe specified in days, hours, minutes, or seconds. For example, the values1d,24h,1440m, and86400s are equivalent.

ALTER LOCALITY GROUP

Use theALTER LOCALITY GROUP statement to change the storage option orage-based policy of a locality group.

Syntax

ALTERLOCALITYGROUPlocality_group_name[storage_def]wherestorage_defis:{STORAGE'{ ssd | hdd }'SSD_TO_HDD_SPILL_TIMESPAN'duration'}

Description

ALTER LOCALITY GROUP changes the storage option or age-based policy of alocality group. You can change these options together or individually.

Parameters

locality_group_name

• The name of the locality group.

storage_def

• UseSTORAGE to define the new storage type of thelocality group.

• Use theSSD_TO_HDD_SPILL_TIMESPAN = 'duration' option to set the newage-based policy of the locality group. The duration must be at least one hour(1h) and at most 365 days (365d) long. It can be specified in days, hours,minutes, or seconds. For example, the values1d,24h,1440m, and86400s are equivalent.

DROP LOCALITY GROUP

Use theDROP LOCALITY GROUP statement to drop the locality group. You can'tdrop a locality group if it contains data. You must first move all datathat's in the locality group to another locality group.

Syntax

DROPLOCALITYGROUPlocality_group_name

Description

DROP LOCALITY GROUP drops the locality group.

INDEX statements

This section has information aboutINDEX statements.

CREATE INDEX

CREATE[UNIQUE]INDEX[IFNOTEXISTS]name]ONtable_name({column_name}[ASC|DESC][NULLS{FIRST|LAST}][,...])[INCLUDE(column_name[,...])][INTERLEAVEINparent_table_name][WHEREpredicate][LOCALITYGROUPlocality_group_name]wherepredicateis:column_nameISNOTNULL[ANDcolumn_nameISNOTNULL][...]

Spanner differences from open source PostgreSQL

[ INTERLEAVE IN parent_table_name ]

• INTERLEAVE IN clause defines a table to interleave the index in (seeIndex optionsfor more details). If T is the table into which the index is interleaved,then the primary key of T must be the key prefix of the index, with eachkey matching in type, sort order, and nullability. Matching by name is notrequired.

[ WHEREpredicate ]

• Thepredicate can refer only to columns that arespecified earlier in theCREATE INDEX statement, not to any column in theunderlying table.

ALTER INDEX

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

ALTERINDEXindex_name{ADD|DROP}INCLUDECOLUMNcolumn_name

DROP INDEX

Removes a secondary index.

DROPINDEX[IFEXISTS]name

SEARCH INDEX statements

This section has information aboutSEARCH INDEX statements.

CREATE SEARCH INDEX

Use theCREATE SEARCH INDEX statement to define search indexes. For moreinformation, seeSearch indexes.

Syntax

CREATESEARCHINDEXindex_nameONtable_name(tokenlist_column_name*)[INCLUDE(column_name[,...])][PARTITIONBYcolumn_name[,...][ORDERBYcolumn_name[ASC|DESC][INTERLEAVEINparent_table_name][WHEREcolumn_nameISNOTNULL[AND...]][WITH(search_index_options)])whereindex_nameis:{a—z|A—Z}[{a—z|A—Z|0—9|_}+]andtokenlist_columnis:column_name[,...]andsearch_index_optionsare:{sort_order_sharding={true|false}}

Description

You can useCREATE SEARCH INDEX to create search indexes forTOKENLISTcolumns. Adding a search index on a column makes it more efficient to searchdata in the source column of theTOKENLIST.

Parameters

index_name

• The name to assign to the search index.

table_name

• The name of the table to be indexed for search.

tokenlist_column_name

• A list ofTOKENLIST columns to be indexed for search.

INCLUDE

• Provides a mechanism for duplicating data from the table into the searchindex.

PARTITION BY

• Indicates a list of columns to partition the search index by. Partitioncolumns subdivide the index into smaller units, one for each uniquepartition. Queries can only search within a single partition at a time.Queries against partitioned indexes are generally more efficient thanqueries against unpartitioned indexes because only splits from a singlepartition need to be read.

ORDER BY

• A list ofbigint columns used to sort the rows within each partition ofthe search index. The column must beNOT NULL, or the index must defineWHERE IS NOT NULL. This property can support at most one column.

WHEREcolumn_name IS NOT NULL

• Rows that containNULL in any of the columns listed in this clause don'tget included in the index. The columns must be present in theORDER BY orINCLUDE clause.

INTERLEAVE IN

• Similar toINTERLEAVE IN for secondary indexes, search indexes can beinterleaved in an ancestor table of the base table. The primary reason touse interleaved search indexes is to colocate base table data with indexdata for small partitions.

• Interleaved search indexes have the following limitations:

  • Only sort-order sharded indexes can be interleaved.
  • Search indexes can only be created on top-level tables. They can't beinterleaved with child tables.
  • Like interleaved tables and secondary indexes, the parent table's keymust be a prefix of the partitioning columns of the interleaved searchindex.

WITH

• Specifies options to use when creating search indexes:

  • sort_order_sharding: Permits specifying sharding of the index. The defaultvalue isfalse in which case the index is uniformly sharded.

ALTER SEARCH INDEX

Use theALTER SEARCH INDEX statement to add or remove columns from the searchindexes.

Syntax

ALTERSEARCHINDEXindex_name{ADD|DROP}INCLUDECOLUMNcolumn_name

Description

Add aTOKENLIST column into a search index or remove an existingTOKENLISTcolumn from a search index. UseINCLUDE COLUMN to add or remove stored columnsfrom a search index.

Parameters

index_name

• The name of the search index to alter.

column_name

• The name of the column to add or remove from the search index.

DROP SEARCH INDEX

Removes a search index.

Syntax

DROPSEARCHINDEX[IFEXISTS]index_name

Description

Use theDROP SEARCH INDEX statement to drop a search index.

Parameters

IF EXISTS

• TheDROP statement has no effect if the specified search index doesn'texist.

index_name

• The name of the search index to drop.

SEQUENCE statements

This section describesSEQUENCE statements.

CREATE SEQUENCE

Creates a sequence object with the specified attributes.

CREATESEQUENCE[IFNOTEXISTS]sequence_name[sequence_kind][NOMINVALUE][NOMAXVALUE][SKIPRANGEskip_range_minskip_range_max][STARTCOUNTER[WITH]start_with_counter][NOCYCLE][OWNEDBYNONE]

Spanner differences from open source PostgreSQL

Bit-reversed positive sequences don't support the followingopen source PostgreSQLSEQUENCE clauses:

• [ ASdata_type ]
• [ INCREMENT [ BY ]increment ]
• [ MINVALUEminvalue ] [ MAXVALUEmaxvalue ]
• [ START [ WITH ]start ]
• [ CACHEcache ]
• [ 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 sequencekind.

[ SKIP RANGEskip_range_minskip_range_max ]

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

• skip_range_min andskip_range_max are bothbigint value types. They bothhave a default value of null. The accepted values forskip_range_min is anyvalue that is lesser than or equal toskip_range_max. The accepted valuesforskip_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 nexttime that Spanner obtains a value from the bit-reversedsequence, it begins withstart_with_counter. Spanner bitreverses this value before returning it to the client.

• start_with_counter is anbigint value type. The default value is1 andit accepts positivebigint values.

• When the counter reaches the maximum in thebigint number space, thesequence 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 usenextval, the sequence generator function,as the default value of the primary key column,SingerId. Values the sequencegenerates are positive and bit-reversed.

CREATESEQUENCEmysequencebit_reversed_positive;CREATETABLEsingers(singeridbigintDEFAULTnextval('mysequence'),namebigint,PRIMARYKEY(singerid));

ALTER SEQUENCE

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

After you executeALTER SEQUENCE, the specified sequence uses the new schemaoptions.

ALTERSEQUENCE[IFEXISTS]sequence_name[NOMINVALUE][NOMAXVALUE][SKIPRANGEskip_range_minskip_range_max][RESTARTCOUNTER[WITH]counter_restart][NOCYCLE]

Spanner differences from open source PostgreSQL

Bit-reversed positive sequences don't support the followingopen source PostgreSQLALTER SEQUENCE clauses:

• [ ASdata_type]
• [ INCREMENT [ BY ]increment]
• [ MINVALUEminvalue]
• [ MAXVALUEmaxvalue]
• [ START [ WITH ]start ]
• [ RESTART [ WITH ]restart ]
• [ CACHEcache ]
• 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 TOnew_name
• ALTER SEQUENCE [ IF EXISTS ]name SET SCHEMAnew_schema

Spanner extends open source PostgreSQL with the following:

[ SKIP RANGEskip_range_minskip_range_max ]

• Restricts the sequence from generating values in the specified range. Sincepositive bit-reversed sequences only generate positive values, setting anegativeSKIP 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.

ALTERSEQUENCEmysequenceSKIPRANGE11234567;

Set the current sequence counter to 1000.

ALTERSEQUENCEmysequenceRESTARTCOUNTERWITH1000;

DROP SEQUENCE

Drops a sequence.

Syntax

DROPSEQUENCE[IFEXISTS]sequence_name

Spanner differences from open source PostgreSQL

Bit-reversed positive sequences don't support the followingopen source PostgreSQLDROP SEQUENCE clauses:

• [CASCADE]
• [RESTRICT]

Description

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

STATISTICS statements

This section has information aboutSTATISTICS statements.

ALTER STATISTICS

Sets optional attributes of a query optimizer statistics package.

Syntax

ALTERSTATISTICSspanner."<package_name>"actionwherepackage_nameis:{a—z}[{a—z|0—9|_|-}+]{a—z|0—9}andactionis:SETOPTIONS(options_def)andoptions_defis:{allow_gc={true|false}}

Description

ALTER STATISTICS sets optional attributes of a query optimizer statisticspackage.

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 attributesyou want to alter.

  To fetch existing statistics packages:

  SELECTs.package_nameASpackage_name,s.allow_gcASallow_gcFROMINFORMATION_SCHEMA.SPANNER_STATISTICSs;

options_def

• Theallow_gc = { true | false } option lets you specify whether a givenstatistics package undergoes garbage collection. A package must be set asallow_gc=false if the package is used in a query hint. For more information,seeGarbage 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 aboutTABLE statements.

CREATE TABLE

Defines a new table.

CREATETABLE[IFNOTEXISTS]table_name({column_namedata_type[column_constraint[...]]|table_constraint|synonym_definition}[,...],PRIMARYKEY(column_name))[{LOCALITYGROUPlocality_group_name|INTERLEAVEIN[PARENT]parent_table_name[ONDELETE(CASCADE|NOACTION)]|TTLINTERVALinterval_specONtimestamp_column_name}]wherecolumn_constraintis:[CONSTRAINTconstraint_name]{NOTNULL|NULL|CHECK(expression)|DEFAULTexpression|GENERATEDALWAYSAS(expression){STORED|VIRTUAL}|GENERATEDBYDEFAULTASIDENTITY[(sequence_option_clause...)]|PRIMARYKEY|REFERENCESreftable(refcolumn)[ONDELETE{CASCADE|NOACTION}]}andtable_constraintis:[CONSTRAINTconstraint_name]{CHECK(expression)|PRIMARYKEY(column_name[,...])|FOREIGNKEY(column_name[,...])REFERENCESreftable(refcolumn[,...])[ONDELETE{CASCADE|NOACTION}]}andsynonym_definitionis:[SYNONYM(synonym)]andsequence_option_clauseis:{BIT_REVERSED_POSITIVE|NOMINVALUE|NOMAXVALUE|SKIPRANGEskip_range_minskip_range_max|STARTCOUNTER[WITH]start_with_counter|NOCYCLE}

PRIMARY KEY(column_name)

In Spanner a primary key is required when creating a new table.

DEFAULTexpression

• 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 useUPDATE ...SETcolumn-name= DEFAULTto reset a non-keycolumn to its default value.
• A generated column or a check constraint can depend on a column with adefault value.
• A column with a default value can't be a commit timestamp column. You can'tuseSPANNER.PENDING_COMMIT_TIMESTAMP() as a default value.

• You can use a literal or any valid SQL expression that is assignable to thecolumn data type as anexpression, with thefollowing 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 BY DEFAULT AS IDENTITY [ (sequence_option_clause... )]

• This clause auto-generates integer values for the column.
• BIT_REVERSED_POSITIVE is the only valid type.
• An identity column can be a key or non-key column.
• An identity column can't have a default value or be a generated column.
• You can insert your own value into an identity column. You can also reset anon-key column to use generated value by usingUPDATE ... SETcolumn-name= DEFAULT.
• A generated column or a check constraint can depend on an identity column.
• An identity column accepts the following option clauses:
  • BIT_REVERSED_POSITIVE indicates the type of identity column.
  • SKIP RANGEskip_range_min,skip_range_maxallows the underlying sequence to skip the numbers in this range whencallingGET_NEXT_SEQUENCE_VALUE. The skipped range is an integer valueand inclusive. The accepted values forskip_range_min is anyvalue that is less than or equal toskip_range_max. The accepted valuesforskip_range_max is any value that is greater than or equal toskip_range_min.
  • START COUNTER WITHstart_with_counter is apositivebigint value that Spanner uses to set the nextvalue for the internal sequence counter. For example, whenSpanner obtains a value from the bit-reversed sequence, itbegins withstart_with_counter. Spanner bit reverses thisvalue before returning it. The default value is1.

GENERATED ALWAYS AS (expression) { STORED | VIRTUAL }

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

• You can use a literal or any valid SQL expression that is assignable to thecolumn data type as anexpression, with thefollowing 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 asSPANNER.PENDING_COMMIT_TIMESTAMP(),CURRENT_DATE, andCURRENT_TIMESTAMP.

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

• TheSTORED attribute that follows the expression causesSpanner to store the result of the function along with othercolumns of the table. Subsequent updates to any of the referenced columnscauses Spanner to re-evaluate and store the expression.

• TheVIRTUAL attribute that follows the expression inSpanner doesn't store the result of the expression in thetable.

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

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

• You can't change the data type of a STORED generated column, or of anycolumns 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 aDEFAULT 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 constraintthan would open source PostgreSQL. Therefore, if you depend on constraintnames, useCONSTRAINTconstraint_name to specify themexplicitly.

Spanner extends open source PostgreSQL with the following:

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

• This clause defines achild-to-parent tablerelationship, which results in a physical interleaving of parent and childrows. The primary-key columns of a parent must positionally match, both inname and type, a prefix of the primary-key columns of any child. Adding rowsto the child table fails if the corresponding parent row doesn't exist. Theparent row can either already exist in the database or can be insertedbefore the insertion of the child rows in the same transaction.

• The optionalON DELETE clause defines the behavior of rows in a childtable when a transaction attempts to delete the parent row. The supportedoptions are:

  • CASCADE which deletes the child rows.

  • NO ACTION which doesn't delete the child rows. If deleting a parentwould leave behind child rows, thus violating parent-child referentialintegrity, the transaction attempt fails.

  If you omit theON DELETE clause, the behavior is that ofON DELETE NOACTION.

INTERLEAVE INparent_table_name

• INTERLEAVE IN defines the same parent-child relationship and physicalinterleaving of parent and child rows asINTERLEAVE IN PARENT, but theparent-child referential integrity constraint isn't enforced. Rows in thechild table can be inserted before the corresponding rows in the parenttable. Like withIN PARENT, the primary-key columns of a parent mustpositionally match, both in name and type, a prefix of the primary-key columnsof any child.

TTL INTERVALinterval_spec ONtimestamp_column_name

• This clause defines atime to live (TTL) policy on thetable, which lets Spanner periodically delete data from thetable.

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

  • locality_group_name is the name of the localitygroup.

LOCALITY GROUPlocality_group_name

• This clause defines alocality groupfor the table, which determines its tiered storage policy.

  • locality_group_name is the name of the localitygroup.

[ SYNONYM (synonym)]

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

ALTER TABLE

Changes the definition of a table.

ALTERTABLE[IFEXISTS][ONLY]nameactionwhereactionisoneof:ADDSYNONYMsynonymDROPSYNONYMsynonymRENAMETOnew_table_name[,ALTERTABLE[IFEXISTS][ONLY]new_table_nameRENAMETOnew_table_name...]RENAMEWITHSYNONYMTOnew_table_nameSETLOCALITYGROUPlocality_group_nameADD[COLUMN][IFNOTEXISTS]column_namedata_type[column_expression]DROP[COLUMN]column_nameADDtable_constraintDROPCONSTRAINTconstraint_name[RESTRICT|CASCADE]ALTERCOLUMNcolumn_name{[SETDATA]TYPEdata_type|{SET|DROP}NOTNULL|SETDEFAULTexpression|DROPDEFAULT|SET{NOMINVALUE|NOMAXVALUE|{SKIPRANGEskip_range_minskip_range_max|NOSKIPRANGE}|NOCYCLE|LOCALITYGROUPlocality_group_name}|RESTARTCOUNTER[WITH]counter_restart}ADDTTLINTERVALinterval_specONtimestamp_column_nameALTERTTLINTERVALinterval_specONtimestamp_column_nameSETINTERLEAVEIN[PARENT]parent_table_name[ONDELETE{CASCADE|NOACTION}]andcolumn_expressionis:[NOTNULL]{DEFAULTexpression|GENERATEDALWAYSAS(expression)STORED|GENERATEDBYDEFAULTASIDENTITY[(sequence_option_clause...)]}andtable_constraintis:[CONSTRAINTconstraint_name]{CHECK(expression)|FOREIGNKEY(column_name[,...])REFERENCESreftable(refcolumn[,...])[ONDELETE{CASCADE|NOACTION}]}andsequence_option_clauseis:{BIT_REVERSED_POSITIVE|NOMINVALUE|NOMAXVALUE|SKIPRANGEskip_range_minskip_range_max|STARTCOUNTER[WITH]start_with_counter|NOCYCLE}

• You can specifyNOT NULL in anALTER TABLE...ADD [ COLUMN ] statement ifyou specifyDEFAULTexpression orGENERATEDALWAYS AS (expression) STORED for the column.

• If you includeDEFAULTexpression orGENERATEDALWAYS AS (expression) STORED,Spanner evaluates the expression and backfills the computedvalue for existing rows. The backfill operation is asynchronous. Thisbackfill operation only happens when Spanner issues anADDCOLUMN statement.

• ALTER COLUMN statements that you use toSET orDROP the default valueof an existing column don't affect existing rows. There is no backfilloperation onALTER COLUMN.

• TheDEFAULT 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 anonymousconstraint than open source PostgreSQL. Therefore, if you depend onconstraint names, specify them explicitly usingCONSTRAINTconstraint_name.
• The following Spanner restrictions apply when altering acolumn (using theALTER COLUMN clause):
  • The statement must contain exactly twoALTER COLUMN clauses applied tothe same column. One clause must alter (or keep as is) the column's datatype and another clause must alter (or keep as is) the column'snullability. For example, if columnc1 is nullable and you want it tostay nullable after the execution of theALTER TABLE statement, youneed to addALTER COLUMN c1 DROP NOT NULL. For example:ALTER TABLE t1 ALTER COLUMN c1 TYPE VARCHAR(10), ALTER COLUMN c1DROP NOT NULL;
  • Spanner only supports operations that theSupported schema updatessection describes.
• For all operations (ADD or DROP) exceptALTER COLUMN,Spanner supports only a single operation perALTER TABLEstatement.

Spanner extends open source PostgreSQL with the following:

ADD TTL INTERVAL,ALTER TTL INTERVAL

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

ADD SYNONYM

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

DROP SYNONYM

• Removes a synonym from a table. For moreinformation, seeRemove a synonym from a table.

RENAME TO

• Renames a table without creating a synonym. In addition, you can concatenatemultipleALTER TABLE RENAME TO statements (delimited by a comma) toatomically rename multiple tables. For moreinformation, seeRename a table.

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

  ALTERTABLEsingersRENAMETOartists,ALTERTABLEalbumsRENAMETOrecordings;

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 toupdate applications with the new table name while still allowing themto access the table with the old name. For more information, seeRename a table and add a synonym.

SET INTERLEAVE IN

• SET INTERLEAVE IN PARENT migrates an interleaved table to useIN PARENTsemantics, which require that the parent row exist for each child row. Whileexecuting this schema change, the child rows are validated to ensure thereare no referential integrity violations. If there are, the schema changefails. If noON DELETE clause is specified,NO ACTION is the default.Note that directly migrating from anINTERLEAVE IN table toIN PARENT ON DELETE CASCADE is not supported. This must be done in twosteps. The first step is to migrateINTERLEAVE IN toINTERLEAVE IN PARENT T [ON DELETE NO ACTION] and the second step is tomigrate toINTERLEAVE IN PARENT T ON DELETE CASCADE. If referential integrityvalidation fails, use a query like the following to identify missing parentrows.

  SELECTpk1,pk2FROMchildEXCEPTDISTINCTSELECTpk1,pk2FROMparent;

• SET INTERLEAVE IN, likeSET INTERLEAVE IN PARENT, migrates anINTERLEAVE IN PARENT interleaved table toINTERLEAVE IN, thus removing theparent-child enforcement between the two tables.

• TheON DELETE clause is only supported when migrating toINTERLEAVE IN PARENT.

DROP TABLE

Removes a table.

DROPTABLE[IFEXISTS]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 aboutVIEW statements.

CREATE OR REPLACE VIEW

Defines a new view or replaces an existing view. IfCREATE VIEW is used andthe view already exists, the statement fails. UseCREATE OR REPLACE VIEW toreplace the view or security type of a view. For more information, seeAboutviews.

{CREATE|CREATEORREPLACE}VIEWview_nameSQLSECURITY{INVOKER|DEFINER}ASquery

DROP VIEW

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

DROPVIEWname

CHANGE STREAM statements

This section has information aboutCHANGE STREAM statements.

CREATE CHANGE STREAM

Defines a newchange stream. For more information,seeCreate a change stream.

CREATECHANGESTREAM[IFNOTEXISTS]change_stream_name[FOR{table_columns[,...]|ALL}][WITH(configuration_parameter_def[,...])]wheretable_columnsis:table_name[([column_name,...])]andconfiguration_parameter_defis:{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 }

• TheFOR clause defines the tables and columns that the change streamwatches.

• You can specify a list oftable_columns to watch,wheretable_columns can be either of thefollowing:

  • 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 optionallyspecify 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 thetable. With an empty list of non-key columns,table_name() watches only the primary key.

  Note: The change stream always watches the primary key columns. You onlyneed to list the non-key columns. Listing any primary key columns is notallowed.

• 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 theFOR clause, the change stream watches nothing.

WITH (configuration_parameter_def[, ... ] )

• Theretention_period = 'duration' configuration parameter lets you specifyhow long a change stream retains its data. For duration you must use therange[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, seeData retention.

• Thevalue_capture_type configuration parameter controls which values tocapture for a changed row. It can beOLD_AND_NEW_VALUES (default),NEW_VALUES,NEW_ROW, ORNEW_ROW_AND_OLD_VALUES. For details, seeValue capture type.

• Theexclude_ttl_deletes configuration parameter lets you filter outtimeto live based deletes from your change stream. Whenyou set this filter, only future TTL-based deletes are removed. It can beset tofalse (default) ortrue. For more information, seeTTL-baseddeletes filter.

• Theexclude_insert configuration parameter lets you filter out allINSERT table modifications from your change stream. It can beset tofalse (default) ortrue. For more information, seeTable modification type filters.

• Theexclude_update configuration parameter lets you filter out allUPDATE table modifications from your change stream. It can beset tofalse (default) ortrue. For more information, seeTable modification type filters.

• Theexclude_delete configuration parameter lets you filter out allDELETE table modifications from your change stream. It can beset tofalse (default) ortrue. For more information, seeTable modification type filters.

ALTER CHANGE STREAM

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

ALTERCHANGESTREAMname actionwhereactionis:{SETFOR{table_columns[,...]|ALL}|DROPFORALL|SET(configuration_parameter_def[,...])|RESET(configuration_parameter[,...])}andtable_columnsis:table_name[([column_name,...])]andconfiguration_parameter_defis:{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}}andconfiguration_parameteris:{retention_period|value_capture_type|exclude_ttl_deletes|exclude_insert|exclude_update|exclude_delete}

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

• Sets a newFOR clause to modify what the change stream watches, using thesame syntax asCREATE CHANGE STREAM.

DROP FOR ALL

• Suspends a change stream towatch nothing.

SET

• Sets configuration parameters on the change stream (such asretention_period,value_capture_type,exclude_ttl_deletes,exclude_insert,exclude_update andexclude_delete), using the samesyntax asCREATE CHANGE STREAM.

• Setting an option tonull is equivalent to setting it to the defaultvalue.

RESET

• Resets configuration parameters on the change stream (such asretention_period,value_capture_type,exclude_ttl_deletes,exclude_insert,exclude_update, andexclude_delete) to the defaultvalues.

DROP CHANGE STREAM

Removes a change stream and deletes its data change records.

DROPCHANGESTREAM[IFEXISTS]name

ROLE statements

This section has information aboutROLE statements.

CREATE ROLE

Defines a new database role.

Syntax

CREATEROLEdatabase_role_name

Description

CREATE ROLE defines a new database role. Database roles are collections offine-grained access control privileges. You cancreate only one role with this statement.

Parameters

database_role_name

• The name of the database role to create. Role names can't start withpg_.The role namepublic and role names starting withspanner_ are reservedforsystem roles.

Example

This example creates the database rolehr_manager.

CREATEROLEhr_manager;

DROP ROLE

Drops a database role.

Syntax

DROPROLEdatabase_role_name

Description

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

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

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

You can't dropsystem roles.

Parameters

database_role_name

• The name of the database role to drop.

Example

This example drops the database rolehr_manager.

DROPROLEhr_manager;

GRANT and REVOKE statements

This section has information aboutGRANT andREVOKE statements.

GRANT

Grants roles to database objects.

Syntax

GRANT{SELECT|INSERT|UPDATE|DELETE}ON[TABLE]table_listTOrole_listGRANT{SELECT|INSERT|UPDATE}(column_list)ON[TABLE]table_listTOrole_listGRANTSELECTON[TABLE]view_listTOrole_listGRANTSELECTONCHANGESTREAMchange_stream_listTOrole_listGRANTEXECUTEONFUNCTIONfunction_listTOrole_listGRANTrole_listTOrole_listGRANTUSAGEONSCHEMAschema_name_listTOrole_listwheretable_listis:table_name[,...]andcolumn_listis:column_name[,...]andview_listis:view_name[,...]andchange_stream_listis:change_stream_name[,...]andfunction_listis:change_stream_read_function_name[,...]andschema_name_listis:schema_name[,...]androle_listis:database_role_name[,...]

Description

Forfine-grained access control, grants privilegeson one or more tables, views, change streams, or change stream read functions todatabase roles. Also grants database roles to other database roles to create adatabase role hierarchy with inheritance. When grantingSELECT,INSERT, orUPDATE on a table, optionally grants privileges on only a subset of tablecolumns.

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 moreinformation, seeChange 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 theGRANT statement must use thecase that was specified when the object was created. For example, if youcreated a table with a name that is in all lowercase with a capitalizedfirst letter, you must use that same case in theGRANT statement. For eachchange stream, PostgreSQL automatically creates a change streamread function with a name that consists of a prefix added to the changestream name, so ensure that you use the proper case for both the prefix andthe change stream name. For more information about change stream readfunctions, seeChange stream query syntax.

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

• If a table contains a column that is markedNOT NULL and has no defaultvalue, you can't insert into the table unless you have theINSERTprivilege on that column.

• After grantingSELECT on a change stream to a role, grantEXECUTE on thechange stream's read function to that role. For information about changestream read functions, seeChange stream read functions and query syntax.

• GrantingSELECT on a table doesn't grantSELECT on the change streamthat tracks it. You must make a separate grant for the change stream.

Examples

The following example grantsSELECT on theemployees table to thehr_reprole. Grantees of thehr_rep role can read all columns ofemployees.

GRANTSELECTONTABLEemployeesTOhr_rep;

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

GRANT SELECT ON employees TO hr_rep;

The next example grantsSELECT on a subset of columns of thecontractorstable to thehr_rep role. Grantees of thehr_rep role can only readthenamed columns.

GRANTSELECT(name,address,phone)ONTABLEcontractorsTOhr_rep;

The next example mixes table-level and column-level grants.hr_manager canread all table columns, but can update only thelocation column.

GRANTSELECT,UPDATE(location)ONTABLEemployeesTOhr_manager;

The next example makes column-level grants on two tables. Both tables mustcontain thename,level, andlocation columns.

GRANTSELECT(name,level,location),UPDATE(location)ONTABLEemployees,contractorsTOhr_manager;

The next example grantsINSERT on a subset of columns of theemployeestable.

GRANTINSERT(name,cost_center,location,manager)ONTABLEemployeesTOhr_manager;

The next example grants the database rolepii_access to the roleshr_managerandhr_director. Thehr_manager andhr_director roles aremembers ofpii_access and inherit the privileges that were granted topii_access. Formore information, seeDatabase role hierarchies and inheritance.

GRANTpii_accessTOhr_manager,hr_director;

REVOKE

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

Syntax

REVOKE{SELECT|INSERT|UPDATE|DELETE}ON[TABLE]table_listFROMrole_listREVOKE{SELECT|INSERT|UPDATE}(column_list)ON[TABLE]table_listFROMrole_listREVOKESELECTON[TABLE]view_listFROMrole_listREVOKESELECTONCHANGESTREAMchange_stream_listFROMrole_listREVOKEEXECUTEONFUNCTIONfunction_listFROMrole_listREVOKErole_listFROMrole_listandtable_listis:table_name[,...]andcolumn_listis:column_name[,...]andview_listis:view_name[,...]andchange_stream_listis:change_stream_name[,...]andfunction_listis:change_stream_read_function_name[,...]androle_listis:database_role_name[,...]

Description

Forfine-grained access control, revokes privilegeson one or more tables, views, change streams, or change stream read functionsfrom database roles. Also revokes database roles from other database roles. WhenrevokingSELECT,INSERT, orUPDATE on a table, optionally revokesprivileges 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 moreinformation, seeChange 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 theREVOKE statement must usethe case that was specified when the object was created. For example, if youcreated a table with a name that is in all lowercase with a capitalizedfirst letter, you must use that same case in theREVOKE statement. Foreach change stream, PostgreSQL automatically creates a changestream read function with a name that consists of a prefix added to thechange stream name, so ensure that you use the proper case for both theprefix and the change stream name. For more information about change streamread functions, seeChange stream query syntax.

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

• AREVOKE statement at the column level has no effect if privileges weregranted at the table level.

• After revokingSELECT on a change stream from a role, revokeEXECUTE onthe change stream's read function from that role.

• RevokingSELECT on a change stream doesn't revoke any privileges on thetable that it tracks.

Examples

The following example revokesSELECT on theemployees table from the rolehr_rep.

REVOKESELECTONTABLEemployeesFROMhr_rep;

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

REVOKESELECTONemployeesFROMhr_rep;

The next example revokesSELECT on a subset of columns of thecontractorstable from the rolehr_rep.

REVOKESELECT(name,address,phone)ONTABLEcontractorsFROMhr_rep;

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

REVOKESELECT,UPDATE(location)ONTABLEemployeesFROMhr_manager;

The next example revokes column-level grants on two tables. Both tables mustcontain thename,level, andlocation columns.

REVOKESELECT(name,level,location),UPDATE(location)ONTABLEemployees,contractors

The next example revokesINSERT on a subset of columns.

REVOKEINSERT(name,cost_center,location,manager)ONTABLEemployeesFROMhr_manager;

The following example revokes the database rolepii_access from thehr_manager andhr_director database roles. Thehr_manager andhr_director roles lose any privileges that they inherited frompii_access.

REVOKEpii_accessFROMhr_manager,hr_director;