Query syntax in PostgreSQL

This page defines the syntax of the SQL statement 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.

CALL

Use the CALL statement to invoke a stored system procedure.

CALL procedure_name (procedure_argument[, …])

CALL executes a stored system procedure procedure_name. You can't create your own stored system procedure. You can only use system procedures. For more information, see stored system procedures.

Parameters

The CALL statement uses the following parameters:

procedure_name: The name of the stored system procedure.
procedure_argument: An argument expression for the stored system procedure call.

SELECT

Use the SELECT statement to retrieve data from a database.

[ /*@ hint_expression [, ...] */ ] [ WITH cte[, ...] ] [, ...] ] select

where cte is:

     cte_name AS ( select )

and select is:

    SELECT select-list
        [ FROM from_item [, ...] ]
        [ WHERE condition ]
        [ GROUP BY grouping_element [, ...] ]
        [ HAVING condition ]
        [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
        [ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
        [ LIMIT count ]
        [ OFFSET start ]
        [ FOR UPDATE ]

and select-list is:

    [ { ALL | DISTINCT } ] { * | expression [ [ AS ] output_name ] [, ...] }

and from_item is one of:

    table_name [ /*@ table_hint_expression [, ...] */ ]
         [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
         [ tablesample_operator ]
    ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    from_item join_type [ /*@ join_hint_expression [, ...] */ ]
        from_item [ ON join_condition | USING ( join_column [, ...] ) ]
    from_item unnest_operator

and join_type is one of:

    [ INNER ] JOIN
    LEFT [ OUTER ] JOIN
    RIGHT [ OUTER ] JOIN
    FULL [ OUTER ] JOIN
    CROSS JOIN

and grouping_element is one of:

    ( )
    expression
    ( expression [, ...] )

and hint_expression is one of:

    statement_hint_key = statement_hint_value
    table_hint_key = table_hint_value
    join_hint_key = join_hint_value

and table_hint_expression is:

    table_hint_key = table_hint_value

and join_hint_expression is:

    join_hint_key = join_hint_value

and statement_hint_key is:

    USE_ADDITIONAL_PARALLELISM | LOCK_SCANNED_RANGES | SCAN_METHOD |
    EXECUTION_METHOD | ALLOW_TIMESTAMP_PREDICATE_PUSHDOWN |
    OPTIMIZER_VERSION | OPTIMIZER_STATISTICS_PACKAGE

and table_hint_key is:

    FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION | SCAN_METHOD

and join_hint_key is:

    FORCE_JOIN_ORDER | JOIN_METHOD | HASH_JOIN_BUILD_SIDE | BATCH_MODE

Common table expressions (CTEs)

cte_name AS ( select )

A common table expression (CTE) includes a CTE name and SELECT statement.

A CTE cannot reference itself.
A CTE can be referenced by the query expression that contains the WITH clause, but rules apply. Those rules are described later in this topic.

Examples

In this example, a WITH clause defines two CTEs that are referenced in the related set operation, where one CTE is referenced by each of the set operation's input query expressions:

WITH subQ1 AS (SELECT SchoolID FROM Roster),
     subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2

WITH is not supported in a subquery. This returns an error:

SELECT account
FROM (
  WITH result AS (SELECT * FROM NPCs)
  SELECT *
  FROM result)

The WITH clause is not supported in DML statements.

Temporary tables defined by the WITH clause are stored in memory. Spanner dynamically allocates memory for all temporary tables created by a query. If the available resources are not sufficient then the query will fail.

CTE rules and constraints

Common table expressions (CTEs) can be referenced inside the query expression that contains the WITH clause.

Here are some general rules and constraints to consider when working with CTEs:

Each CTE in the same WITH clause must have a unique name.
A CTE defined in a WITH clause is only visible to other CTEs in the same WITH clause that were defined after it.
A local CTE overrides an outer CTE or table with the same name.
A CTE on a subquery may not reference correlated columns from the outer query.

CTE visibility

References between CTEs in the WITH clause can be backward references, but not forward references.

The following is what happens when you have two CTEs that reference themselves or each other in a WITH clause. Assume that A is the first CTE and B is the second CTE in the clause:

A references A = Invalid
A references B = Invalid
B references A = Valid
A references B references A = Invalid (cycles are not allowed)

This produces an error. A cannot reference itself because self-references are not supported:

WITH
  A AS (SELECT 1 AS n UNION ALL (SELECT n + 1 FROM A WHERE n < 3))
SELECT * FROM A

-- Error

This produces an error. A cannot reference B because references between CTEs can go backwards but not forwards:

WITH
  A AS (SELECT * FROM B),
  B AS (SELECT 1 AS n)
SELECT * FROM B

-- Error

B can reference A because references between CTEs can go backwards:

WITH
  A AS (SELECT 1 AS n),
  B AS (SELECT * FROM A)
SELECT * FROM B

+---+
| n |
+---+
| 1 |
+---+

This produces an error. A and B reference each other, which creates a cycle:

WITH
  A AS (SELECT * FROM B),
  B AS (SELECT * FROM A)
SELECT * FROM B

-- Error

`FOR UPDATE` clause

SELECT
...
FOR UPDATE;

In serializable isolation, when you use the SELECT query to scan a table, add a FOR UPDATE clause to enable exclusive locks at the row-and-column granularity level, otherwise known as cell-level. The lock remains in place for the lifetime of the read-write transaction. During this time, the FOR UPDATE clause prevents other transactions from modifying the locked cells until the current transaction completes. For more information, see Use SELECT FOR UPDATE in serializable isolation.

Unlike in serializable isolation, FOR UPDATE doesn't acquire locks under repeatable read isolation. For more information, see Use SELECT FOR UPDATE in repeatable read isolation.

To be consistent with other PostgreSQL-dialect databases, you can't use the FOR UPDATE clause with set operators UNION, INTERSECT, and EXCEPT, combined with GROUP BY, HAVING, and DISTINCT clauses, and aggregate functions like array_agg().

Example:

SELECT marketingbudget
FROM albums
WHERE singerid = 1 and albumid = 1
FOR UPDATE;

UPDATE albums
SET marketingbudget = 100000
WHERE singerid = 1 and albumid = 1;

You can't use the FOR UPDATE clause in the following ways:

In combination with the LOCK_SCANNED_RANGES hint
In read-only transactions
Within DDL statements

For more information, see Use SELECT FOR UPDATE.

Spanner query hint extensions to open source PostgreSQL

Spanner has extensions for statement hints, table hints, and join hints.

Statement hints

[ /*@ statement_hint_key = statement_hint_value [, ...] */ ]

where statement_hint_key is:

    USE_ADDITIONAL_PARALLELISM | LOCK_SCANNED_RANGES | SCAN_METHOD |
    EXECUTION_METHOD | ALLOW_TIMESTAMP_PREDICATE_PUSHDOWN |
    OPTIMIZER_VERSION | OPTIMIZER_STATISTICS_PACKAGE

Spanner supports the following statement hints as extensions to open source PostgreSQL.

Hint key	Possible values	Description
`USE_ADDITIONAL_PARALLELISM`	`TRUE` \| `FALSE` (default)	If `TRUE`, the execution engine favors using more parallelism when possible. Because this can reduce resources available to other operations, you might want to avoid this hint if you run latency-sensitive operations on the same instance.
`OPTIMIZER_VERSION`	Integer or `"latest"`	Specifies the query optimizer version to use. This lets you test new optimizer versions or pin to a specific version. See Manage the query optimizer. Example: `/@ OPTIMIZER_VERSION = 8 / SELECT ...`
`OPTIMIZER_STATISTICS_PACKAGE`	String (package name) or `"latest"`	Specifies the optimizer statistics package to use for query compilation. Use this to ensure plan stability or test the effect of different statistics. The specified package must have garbage collection disabled. For more information, see Manage the query optimizer. Example: `/@ OPTIMIZER_STATISTICS_PACKAGE = auto_20260501_10_00_00UTC / SELECT ...`
`LOCK_SCANNED_RANGES`	`exclusive` \| `shared` (default)	Use this hint to request an exclusive lock on a set of ranges scanned by a transaction. Acquiring an exclusive lock helps in scenarios when you observe high write contention, that is, you notice that multiple transactions are concurrently trying to read and write to the same data, resulting in a large number of aborts. Without the hint, it's possible that multiple simultaneous transactions will acquire shared locks, and then try to upgrade to exclusive locks. This scenario will cause a deadlock, because each transaction's shared lock is preventing the other transactions from upgrading to exclusive. Spanner aborts all but one of the transactions. When requesting an exclusive lock using this hint, one transaction acquires the lock and proceeds to execute, while other transactions wait their turn for the lock. Throughput is still limited because the conflicting transactions can only be performed one at a time, but in this case Spanner is always making progress on one transaction, saving time that would otherwise be spent aborting and retrying transactions. This hint is supported on all statement types, both query and DML. Spanner always enforces serializability. Lock mode hints can affect which transactions wait or abort in contended workloads, but don't change the isolation level. Because this lock mode is only a hint, it shouldn't be considered equivalent to a mutex. In other words, you shouldn't use Spanner exclusive locks as a mutual exclusion mechanism for the execution of code outside of Spanner. For more information, see Locking. You can't use both the `FOR UPDATE` clause and the `LOCK_SCANNED_RANGES` hint in the same query. The query returns an error. For more information, see Use SELECT FOR UPDATE.
`SCAN_METHOD`	`AUTO` (default) \| `BATCH` \| `ROW` \| `COLUMNAR` \| `NO_COLUMNAR`	Use this hint to enforce the query scan method. The default Spanner scan method is `AUTO` (automatic). The `AUTO` setting specifies that batch-oriented query processing and columnar formatted data, if available, could be used to improve query performance. The `ROW` scan method forces the scan to read row formatted data and use row-oriented execution, whereas the `BATCH` scan method uses row formatted data and batch-oriented execution. The `COLUMNAR` scan method will read columnar formatted data, if available, and utilize batch-oriented execution. The `NO_COLUMNAR` scan method disables automatic selection of columnar formatted data but allows automatic selection of batch-oriented query processing, similar to the `AUTO` scan method. You can't manually set the scan method to `AUTO`, but if you remove the hint, then Spanner uses the `AUTO` scan method. For more information, see Optimize scans.
`EXECUTION_METHOD`	`DEFAULT` \| `BATCH` \| `ROW`	Use this hint to enforce the query execution method. The default Spanner query execution method is `DEFAULT`. The `DEFAULT` setting specifies that batch-oriented execution might be used to improve query performance, depending on the heuristics of the query. If you want to change the default execution method, you can use a statement hint to enforce the `BATCH`-oriented or `ROW`-oriented execution method. You can't manually set the query execution method to `DEFAULT`. However, if you remove the statement hint, then Spanner uses the `DEFAULT` execution method. For more information, see Optimize query execution.
`ALLOW_TIMESTAMP_PREDICATE_PUSHDOWN`	`TRUE` \| `FALSE` (default)	If set to `TRUE`, the query execution engine uses the timestamp predicate pushdown optimization technique. This technique improves the efficiency of queries that use timestamps and data with an age-based tiered storage policy. For more information, see Optimize queries with timestamp predicate pushdown.

Table hints

[ /*@ table_hint_key = table_hint_value [, ...] */ ]

where table_hint_key is:

    FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION | SCAN_METHOD

Spanner supports the following table hints as extensions to open source PostgreSQL.

Hint key Possible values Description

Hint key	Possible values	Description
`FORCE_INDEX`	String. The name of an existing index in the database or `_BASE_TABLE` to use the base table rather than an index.	If set to the name of an index, use that index instead of the base table. If the index cannot provide all needed columns, perform a back join with the base table. If set to the string `_BASE_TABLE`, use the base table for the index strategy instead of an index. Note that this is the only valid value when `FORCE_INDEX` is used in a statement hint expression. Note: `FORCE_INDEX` is actually a directive, not a hint, which means Spanner raises an error if the index doesn't exist.
`GROUPBY_SCAN_OPTIMIZATION`	`TRUE` \| `FALSE`	The group by scan optimization can make queries faster if they use `GROUP BY`. It can be applied if the grouping keys can form a prefix of the underlying table or index key, and if the query requires only the first row from each group. The optimizer applies the optimization if it estimates that it will make the query more efficient. The hint overrides that decision. If the hint is set to `FALSE`, the optimizer doesn't consider the optimization. If the hint is set to `TRUE`, the optimizer applies the optimization as long as it is legal to do so.
`SCAN_METHOD`	`AUTO` (default) \| `BATCH` \| `ROW` \| `COLUMNAR` \| `NO_COLUMNAR`	Use this hint to enforce the query scan method. The default Spanner scan method is `AUTO` (automatic). The `AUTO` setting specifies that batch-oriented query processing and columnar formatted data, if available, could be used to improve query performance. The `ROW` scan method forces the scan to read row formatted data and use row-oriented execution, whereas the `BATCH` scan method uses row formatted data and batch-oriented execution. The `COLUMNAR` scan method will read columnar formatted data, if available, and utilize batch-oriented execution. The `NO_COLUMNAR` scan method disables automatic selection of columnar formatted data but allows automatic selection of batch-oriented query processing, similar to the `AUTO` scan method. You can't manually set the scan method to `AUTO`, but if you remove the hint, then Spanner uses the `AUTO` scan method. For more information, see Optimize scans.

FORCE_INDEX

String. The name of an existing index in the database or _BASE_TABLE to use the base table rather than an index.

If set to the name of an index, use that index instead of the base table. If the index cannot provide all needed columns, perform a back join with the base table.
If set to the string _BASE_TABLE, use the base table for the index strategy instead of an index. Note that this is the only valid value when FORCE_INDEX is used in a statement hint expression.

Note: FORCE_INDEX is actually a directive, not a hint, which means Spanner raises an error if the index doesn't exist.

GROUPBY_SCAN_OPTIMIZATION

TRUE |
FALSE

The group by scan optimization can make queries faster if they use GROUP BY. It can be applied if the grouping keys can form a prefix of the underlying table or index key, and if the query requires only the first row from each group.

The optimizer applies the optimization if it estimates that it will make the query more efficient. The hint overrides that decision. If the hint is set to FALSE, the optimizer doesn't consider the optimization. If the hint is set to TRUE, the optimizer applies the optimization as long as it is legal to do so.

SCAN_METHOD

AUTO (default) |
BATCH |
ROW |
COLUMNAR |
NO_COLUMNAR

Use this hint to enforce the query scan method.

The default Spanner scan method is AUTO (automatic). The AUTO setting specifies that batch-oriented query processing and columnar formatted data, if available, could be used to improve query performance. The ROW scan method forces the scan to read row formatted data and use row-oriented execution, whereas the BATCH scan method uses row formatted data and batch-oriented execution. The COLUMNAR scan method will read columnar formatted data, if available, and utilize batch-oriented execution. The NO_COLUMNAR scan method disables automatic selection of columnar formatted data but allows automatic selection of batch-oriented query processing, similar to the AUTO scan method. You can't manually set the scan method to AUTO, but if you remove the hint, then Spanner uses the AUTO scan method. For more information, see Optimize scans.

Join hints

[ /*@ join_hint_key = join_hint_value [, ...] */ ]

where join_hint_key is:

    FORCE_JOIN_ORDER | JOIN_METHOD | HASH_JOIN_BUILD_SIDE | BATCH_MODE

Spanner supports the following join hints as extensions to open source PostgreSQL.

Hint key	Possible values	Description
`FORCE_JOIN_ORDER`	`TRUE` \| `FALSE` (default)	If set to true, use the join order that's specified in the query.
`JOIN_METHOD`	`HASH_JOIN` \| `APPLY_JOIN` \| `MERGE_JOIN` \| `PUSH_BROADCAST_HASH_JOIN`	When implementing a logical join, choose a specific alternative to use for the underlying join method. For more information, see Join methods.
`HASH_JOIN_BUILD_SIDE`	`BUILD_LEFT` \| `BUILD_RIGHT`	Specifies which side of the hash join is used as the build side. Can only be used with `JOIN_METHOD=HASH_JOIN`
`BATCH_MODE`	`TRUE (default)` \| `FALSE`	Used to disable batched apply join in favor of row-at-a-time apply join. Can only be used with `JOIN_METHOD=APPLY_JOIN`.

Function hints

function_name() [ /*@ function_hint_key = function_hint_value [, ...] */ ]

where function_hint_key is:

    DISABLE_INLINE

Spanner supports the following function hints as extensions to open source PostgreSQL.

Hint key Possible values Description

Hint key	Possible values	Description
`DISABLE_INLINE`	`TRUE` \| `FALSE` (default)	If you set this hint to true, the query computes the function once instead of each time another part of a query references it. `DISABLE_INLINE` works with top-level functions. You can't use `DISABLE_INLINE` with a few functions, including those that don't produce a scalar value and casting. Although you can't use `DISABLE_INLINE` with a casting function, you can use it with the first expression inside the function.

DISABLE_INLINE

TRUE |
FALSE (default)

If you set this hint to true, the query computes the function once instead of each time another part of a query references it.

DISABLE_INLINE works with top-level functions.

You can't use DISABLE_INLINE with a few functions, including those that don't produce a scalar value and casting. Although you can't use DISABLE_INLINE with a casting function, you can use it with the first expression inside the function.

Examples

In the following example, inline expressions are enabled by default for x. The query computes x twice, once by each reference:

SELECT
  SUBSTRING(x, 2, 5) AS w,
  SUBSTRING(x, 3, 7) AS y
FROM (SELECT SHA512(z) AS x FROM t) AS subquery

In the following example, inline expressions are disabled for x. The query computes x once, and each reference uses the result:

SELECT
  SUBSTRING(x, 2, 5) AS w,
  SUBSTRING(x, 3, 7) AS y
FROM (SELECT SHA512(z) /*@ DISABLE_INLINE = TRUE */ AS x FROM t) AS subquery

Join methods

Join methods are specific implementations of the various logical join types. Some join methods are available only for certain join types. The choice of which join method to use depends on the specifics of your query and of the data being queried. The best way to figure out if a particular join method helps with the performance of your query is to try the method and view the resulting query execution plan. See Query Execution Operators for more details.

Join method	Description	Operands
`HASH_JOIN`	The hash join operator builds a hash table out of one side (the build side), and probes in the hash table for all the elements in the other side (the probe side).	Spanner uses different variants for various join types. View the query execution plan for your query to see which variant is used. For more information, see Hash join operator.
`APPLY_JOIN`	The apply join operator gets each item from one side (the input side), and evaluates the subquery on other side (the map side) using the values of the item from the input side.	Spanner uses different variants for various join types. Inner joins use cross apply, and left joins use outer apply. For more information, see Cross apply and Outer apply operators.
`MERGE_JOIN`	The merge join operator joins two streams of sorted data. The optimizer will add Sort operators to the plan if the data is not already providing the required sort property for the given join condition. The engine provides a distributed merge sort by default, which when coupled with merge join might allow for larger joins, potentially avoiding disk spilling and improving scale and latency.	Spanner uses different variants for various join types. View the query execution plan for your query to see which variant is used. For more information, see Merge join operator.
`PUSH_BROADCAST_HASH_JOIN`	The push broadcast hash join operator builds a batch of data from the build side of the join. Spanner then sends the batch in parallel to all the local splits of the probe side of the join. On each of the local servers, Spanner executes a hash join between the batch and the local data. This join is most likely to be beneficial when the input can fit within one batch, but is not strict. Another potential area of benefit is when operations can be distributed to the local servers, such as an aggregation that occurs after a join. A push broadcast hash join can distribute some aggregation where a hash join cannot.	Spanner uses different variants for various join types. View the query execution plan for your query to see which variant is used. For more information, see Push broadcast hash join operator.

`TABLESAMPLE` operator

tablesample_operator:
  {
    TABLESAMPLE sample_method (sample_size)
  }

where sample_method is one of:
  BERNOULLI | SPANNER.RESERVOIR

and sample_size is one of:
  { numeric_literal | numeric_parameter }

You can use the TABLESAMPLE operator to select a random sample of a dataset. This operator is useful when you're working with tables that have large amounts of data and you don't need precise answers. The TABLESAMPLE operator uses the following parameters:

sample_method: You must specify the sampling algorithm that the TABLESAMPLE operator uses:
- BERNOULLI: The algorithm independently selects each row with the probability given in the sample_size clause. As a result, you get approximately N * sample_size/100 rows.
- SPANNER.RESERVOIR: The algorithm takes an actual sample size K (expressed as a number of rows) as a parameter. If the input is smaller than K, the algorithm outputs the entire input relation. If the input is larger than K, SPANNER.RESERVOIR sampling outputs a sample of size exactly K, where any sample of size K is equally likely.
sample_size: The size of the sample. If you choose the BERNOULLI sampling algorithm, the value must be between 0 and 100, inclusive. If you choose the SPANNER.RESERVOIR sampling algorithm, the value must be greater than or equal to 0.

Examples

The following examples illustrate the use of the TABLESAMPLE operator.

Select from a table using the SPANNER.RESERVOIR sampling method with a sample size of 100 rows:

SELECT MessageId
FROM Messages TABLESAMPLE SPANNER.RESERVOIR (100);

Select from a table using the BERNOULLI sampling method with a sample size of approximately 0.1%:

SELECT MessageId
FROM Messages TABLESAMPLE BERNOULLI (0.1);

Use a TABLESAMPLE operation with a join to another table:

SELECT T.Subject, M.MessageId
FROM Threads AS T TABLESAMPLE SPANNER.RESERVOIR(10),
     Messages AS M TABLESAMPLE BERNOULLI(50)
WHERE T.ServerId='test' AND T.ThreadId = M.ThreadId;

Casting a literal to a numeric literal is supported:

SELECT MessageId
FROM Messages TABLESAMPLE SPANNER.RESERVOIR (CAST(45.56 AS BIGINT));

SELECT MessageId
FROM Messages TABLESAMPLE BERNOULLI ('50'::DOUBLE PRECISION);

Implicit coercion of a literal to a numeric literal is supported:

SELECT MessageId FROM Messages TABLESAMPLE BERNOULLI ('50');

Operator limitations

The TABLESAMPLE operator doesn't support the following operations:

Nested casting of sample size literals.
Casting of sample size parameters.
Sampling on temporary tables derived from a subquery. This limitation is in contrast with the GoogleSQL dialect.

TABLESAMPLE can be applied to a base table or a view inside subqueries:

SELECT Subject
FROM (SELECT MessageId, Subject
      FROM Messages TABLESAMPLE BERNOULLI(50) WHERE ServerId = 'test') Messages2
WHERE MessageId > 3;

SELECT MessageId
FROM Messages
WHERE Subject IN (SELECT Messages.Subject
                  FROM Messages TABLESAMPLE SPANNER.RESERVOIR(3));

SELECT Roster.LastName, TM.Mascot
FROM Roster TABLESAMPLE SPANNER.RESERVOIR(5)
     JOIN (SELECT Mascot, SchoolID
           FROM TeamMascot TABLESAMPLE BERNOULLI(50)) TM ON Roster.SchoolID = TM.SchoolID;

`UNNEST` operator

unnest_operator:
  {
    UNNEST( array_expression )
    | UNNEST( array_path )

  }
  [ table_hint_expr ]
  [ as_alias ]

as_alias:
  [AS] alias

The UNNEST operator takes an array and returns a table, with one row for each element in the array. For input arrays of most element types, the output of UNNEST generally has one column.

The UNNEST operator takes the following input values:

array_expression: an expression that produces an array.
table_name: the name of a table.

array_path: the path to an ARRAY type.