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(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            |
 *----------------*/

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);