Sequence functions in GoogleSQL

GoogleSQL for Spanner supports the following sequence functions.

Function list

Name	Summary
`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`	Gets the current sequence internal counter before bit reversal.

`GET_NEXT_SEQUENCE_VALUE`

GET_NEXT_SEQUENCE_VALUE(SEQUENCE sequence_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.

CREATE TABLE Singers (
  SingerId INT64 DEFAULT (GET_NEXT_SEQUENCE_VALUE(SEQUENCE MySequence)),
  a STRING(MAX),
) PRIMARY KEY (SingerId);

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

SELECT GET_NEXT_SEQUENCE_VALUE(SEQUENCE MySequence) as next_id;
INSERT INTO Singers(SingerId, a) VALUES (next_id, 1);

Use the sequence functions independently in the GoogleSQL DML.

INSERT INTO Singers (SingerId) VALUES (GET_NEXT_SEQUENCE_VALUE(SEQUENCE MySequence);

`GET_INTERNAL_SEQUENCE_STATE`

GET_INTERNAL_SEQUENCE_STATE(SEQUENCE sequence_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

SELECT GET_NEXT_SEQUENCE_VALUE(SEQUENCE MySequence) AS next_value;

/*---------------------*
 | next_value          |
 +---------------------+
 | 5980780305148018688 |
 *---------------------*/

SELECT GET_INTERNAL_SEQUENCE_STATE(SEQUENCE MySequence) AS sequence_state;

/*----------------*
 | sequence_state |
 +----------------+
 | 399            |
 *----------------*/