Sequence functions in GoogleSQL

GoogleSQL for Spanner supports the following sequence functions.

Function list

Name	Summary
`GET_INTERNAL_SEQUENCE_STATE`	Gets the current sequence internal counter before bit reversal.
`GET_NEXT_SEQUENCE_VALUE`	Takes in a sequence identifier and returns the next value. This function is only allowed in read-write transactions.

`GET_INTERNAL_SEQUENCE_STATE`

GET_INTERNAL_SEQUENCE_STATE(SEQUENCEsequence_identifier)

Description

Gets the current sequence internal counter before bit reversal. This function is useful for import or export, and migrations. If GET_NEXT_SEQUENCE_VALUE is never called on the sequence, then this function returns NULL.

Arguments

sequence_identifier: The ID for the sequence.

Return Data Type

INT64

Example

SELECTGET_NEXT_SEQUENCE_VALUE(SEQUENCEMySequence)ASnext_value;
/*---------------------+
 | next_value |
 +---------------------+
 | 5980780305148018688 |
 +---------------------*/

SELECTGET_INTERNAL_SEQUENCE_STATE(SEQUENCEMySequence)ASsequence_state;
/*----------------+
 | sequence_state |
 +----------------+
 | 399 |
 +----------------*/

`GET_NEXT_SEQUENCE_VALUE`

GET_NEXT_SEQUENCE_VALUE(SEQUENCEsequence_identifier)

Description

Gets the next integer in a sequence.

Arguments

sequence_identifier: The ID for the sequence.

Return Data Type

INT64

Example

Create a table where its key column uses the sequence as a default value.

CREATETABLESingers(
SingerIdINT64DEFAULT(GET_NEXT_SEQUENCE_VALUE(SEQUENCEMySequence)),
aSTRING(MAX),
)PRIMARYKEY(SingerId);

Obtain a sequence value in a read-write transaction and use it in an INSERT statement.

SELECTGET_NEXT_SEQUENCE_VALUE(SEQUENCEMySequence)asnext_id;
INSERTINTOSingers(SingerId,a)VALUES(next_id,1);

Use the sequence functions independently in the GoogleSQL DML.

INSERTINTOSingers(SingerId)VALUES(GET_NEXT_SEQUENCE_VALUE(SEQUENCEMySequence);