Query syntax in PostgreSQL

This page defines the syntax of the SQL statement supported forPostgreSQL-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 precedingitem can repeat in a comma-separated list.
- item [, ...] indicates one or more items, and
- [item, ...] indicates zero or more items.
Purple-colored text, such asitem, 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 asINSERT, are keywords.

CALL

Use theCALL statement to invoke a stored system procedure.

CALLprocedure_name (procedure_argument[, …])

CALL executes a stored system procedureprocedure_name. Youcan't create your own stored system procedure. You can only use systemprocedures. For more information, seestored system procedures.

Parameters

TheCALL statement uses the following parameters:

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

SELECT

Use theSELECT statement to retrieve data from a database.

[ /*@hint_expression [, ...] */ ] [ WITHcte[, ...] ] [, ...] ]selectwherecte is:cte_name AS (select )andselect is:    SELECTselect-list        [ FROMfrom_item [, ...] ]        [ WHEREcondition ]        [ GROUP BYgrouping_element [, ...] ]        [ HAVINGcondition ]        [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ]select ]        [ ORDER BYexpression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]        [ LIMITcount ]        [ OFFSETstart ]        [ FOR UPDATE ]andselect-list is:    [ { ALL | DISTINCT } ] { * |expression [ [ AS ]output_name ] [, ...] }andfrom_item is one of:table_name[ /*@table_hint_expression [, ...] */ ]         [ [ AS ]alias [ (column_alias [, ...] ) ] ]    (select ) [ AS ]alias [ (column_alias [, ...] ) ]from_itemjoin_type[ /*@join_hint_expression [, ...] */ ]from_item [ ONjoin_condition | USING (join_column [, ...] ) ]from_itemunnest_operatorandjoin_type is one of:    [ INNER ] JOIN    LEFT [ OUTER ] JOIN    RIGHT [ OUTER ] JOIN    FULL [ OUTER ] JOIN    CROSS JOINandgrouping_element is one of:    ( )expression    (expression [, ...] )andhint_expression is one of:statement_hint_key =statement_hint_valuetable_hint_key =table_hint_valuejoin_hint_key =join_hint_valueandtable_hint_expression is:table_hint_key =table_hint_valueandjoin_hint_expression is:join_hint_key =join_hint_valueandstatement_hint_key is:    USE_ADDITIONAL_PARALLELISM | LOCK_SCANNED_RANGES | SCAN_METHOD |    EXECUTION_METHOD | ALLOW_TIMESTAMP_PREDICATE_PUSHDOWNandtable_hint_key is:    FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION | SCAN_METHODandjoin_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 andSELECT statement.

A CTE cannot reference itself.
A CTE can be referenced by the query expression thatcontains theWITH clause, but rules apply. Those rules are describedlater in this topic.

Examples

In this example, aWITH clause defines two CTEs thatare referenced in the related set operation, where one CTE is referenced byeach of the set operation's input query expressions:

WITHsubQ1AS(SELECTSchoolIDFROMRoster),subQ2AS(SELECTOpponentIDFROMPlayerStats)SELECT*FROMsubQ1UNIONALLSELECT*FROMsubQ2

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

SELECTaccountFROM(WITHresultAS(SELECT*FROMNPCs)SELECT*FROMresult)

TheWITH clause is not supported in DML statements.

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

CTE rules and constraints

Common table expressions (CTEs) can be referenced inside the query expressionthat contains theWITH clause.

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

Each CTE in the sameWITH clause must have a unique name.
A CTE defined in aWITH clause is only visible to other CTEs in the sameWITH 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 theWITH clause can be backward references, but notforward references.

The following is what happens when you have two CTEs that referencethemselves or each other in aWITH clause. Assume that A is the first CTE and Bis 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 arenot supported:

WITHAAS(SELECT1ASnUNIONALL(SELECTn+1FROMAWHEREn <3))SELECT*FROMA-- Error

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

WITHAAS(SELECT*FROMB),BAS(SELECT1ASn)SELECT*FROMB-- Error

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

WITHAAS(SELECT1ASn),BAS(SELECT*FROMA)SELECT*FROMB+---+|n|+---+|1|+---+

This produces an error.A andB reference each other, which creates acycle:

WITHAAS(SELECT*FROMB),BAS(SELECT*FROMA)SELECT*FROMB-- Error

`FOR UPDATE` clause

SELECT...FORUPDATE;

Inserializable isolation, whenyou use theSELECT query to scan a table, add aFOR UPDATE clause toenable exclusive locks at the row-and-column granularity level, otherwise knownas cell-level. The lock remains in place for the lifetime of the read-writetransaction. During this time, theFOR UPDATE clause prevents othertransactions from modifying the locked cells until the current transactioncompletes. For more information, seeUse SELECT FOR UPDATE in serializable isolation.

Unlike in serializable isolation,FOR UPDATE doesn't acquire locks underrepeatable read isolation. For more information, seeUse SELECT FOR UPDATE in repeatable read isolation.

To be consistent with other PostgreSQL-dialect databases, you can't use theFOR UPDATE clausewith set operatorsUNION,INTERSECT andEXCEPT, combined withGROUP BY,HAVING andDISTINCT clauses, and aggregate functions likearray_agg().

Example:

SELECTmarketingbudgetFROMalbumsWHEREsingerid=1andalbumid=1FORUPDATE;UPDATEalbumsSETmarketingbudget=100000WHEREsingerid=1andalbumid=1;

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

In combination with theLOCK_SCANNED_RANGES hint
In read-only transactions
Within DDL statements

For more information, seeUse SELECT FOR UPDATE.

Spanner query hint extensions to open source PostgreSQL

Spanner has extensions forstatement hints,table hints, andjoin hints.

Statement hints

[ /*@statement_hint_key =statement_hint_value [, ...] */ ]wherestatement_hint_key is:    USE_ADDITIONAL_PARALLELISM | LOCK_SCANNED_RANGES | SCAN_METHOD |    EXECUTION_METHOD | ALLOW_TIMESTAMP_PREDICATE_PUSHDOWN

Spanner supports the following statement hints as extensionsto open source PostgreSQL.

Hint key	Possible values	Description
`USE_ADDITIONAL_PARALLELISM`	`TRUE` \| `FALSE` (default)	If`TRUE`, the execution engine favors using moreparallelism when possible. Because this can reduce resources available to other operations, you may want toavoid this hint if you run latency-sensitive operations on the same instance.
`LOCK_SCANNED_RANGES`	`exclusive` \| `shared` (default)	Use this hint to request an exclusive lock on a set of ranges scanned by atransaction. Acquiring an exclusive lock helps in scenarios when you observehigh write contention, that is, you notice that multiple transactions areconcurrently trying to read and write to the same data, resulting in a largenumber of aborts. Without the hint, it's possible that multiple simultaneous transactions willacquire shared locks, and then try to upgrade to exclusive locks. This willcause a deadlock, because each transaction's shared lock is preventing the othertransaction(s) from upgrading to exclusive. Spanner abortsall but one of the transactions. When requesting an exclusive lock using this hint, one transaction acquires thelock and proceeds to execute, while other transactions wait their turn for thelock. Throughput is still limited because the conflicting transactions can onlybe performed one at a time, but in this case Spanner isalways making progress on one transaction, saving time that would otherwise bespent aborting and retrying transactions. This hint is supported on all statement types, both query and DML. Spanner always enforcesserializability.Lock mode hints can affect which transactions wait or abort in contendedworkloads, but don't change the isolation level. Because this is just a hint, it shouldn't be considered equivalent to a mutex.In other words, you shouldn't use Spanner exclusive locksas a mutual exclusion mechanism for the execution of code outside of Spanner.For more information, seeLocking. You can't use both the`FOR UPDATE` clause and the`LOCK_SCANNED_RANGES` hint in the same query. An error is returned.For more information, seeUse SELECT FOR UPDATE.
`SCAN_METHOD`	`AUTO` (default) \| `BATCH` \| `ROW`	Use this hint to enforce the query scan method. The default Spanner scan method is`AUTO` (automatic).The`AUTO` setting specifies that depending on the heuristics of thequery, batch or row-oriented query processing might be used to improve queryperformance. If you want to change the default scanning method, you can use astatement hint to enforce the`BATCH`-oriented or`ROW`-oriented processing method. You can't manually set the scanmethod to`AUTO`. However, if you remove the statement hint, thenSpanner uses the`AUTO` scan method. For moreinformation, seeOptimize 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 thatbatch-oriented execution might be used to improve query performance, dependingon the heuristics of the query. If you want to change the default executionmethod, you can use a statement hint to enforce the`BATCH`-orientedor`ROW`-oriented execution method. You can't manually set the queryexecution method to`DEFAULT`. However, if you remove the statementhint, then Spanner uses the`DEFAULT` executionmethod. For more information, seeOptimize query execution.
`ALLOW_TIMESTAMP_PREDICATE_PUSHDOWN`	`TRUE` \| `FALSE` (default)	If set to`TRUE`, the query execution engine uses the timestamppredicate pushdown optimization technique. This technique improves theefficiency of queries that use timestamps and data with an age-based tieredstorage policy. For more information, seeOptimize queries with timestamp predicate pushdown.

Table hints

[ /*@table_hint_key =table_hint_value [, ...] */ ]wheretable_hint_key is:    FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION | SCAN_METHOD

Spanner supports the following table hints as extensionsto 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 basetable. If the index cannot provide all needed columns, perform a backjoin with the base table. If set to the string`_BASE_TABLE`, use the base tablefor the index strategy instead of an index. Note that this is the onlyvalid value when`FORCE_INDEX` is used in a statement hintexpression. Note:`FORCE_INDEX` is actually a directive, not a hint,which means an error is raised if the index does not 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 thegrouping keys can form a prefix of the underlying table or index key, and if thequery requires only the first row from each group. The optimization is applied if the optimizer estimates that it will make thequery more efficient. The hint overrides that decision. If the hint is set to`FALSE`, the optimization is not considered. If the hint is set to`TRUE`, the optimization will be applied as long as it is legal to doso.
`SCAN_METHOD`	`AUTO` (default) \| `BATCH` \| `ROW`	Use this hint to enforce the query scan method. By default, Spanner sets the scan method as`AUTO`(automatic) which means depending on the heuristics of the query,batch-oriented query processing might be used to improve query performance. Ifyou want to change the default scanning method from`AUTO`, you canuse the hint to enforce a`ROW` or`BATCH` orientedprocessing method. For more information seeOptimize 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 basetable. If the index cannot provide all needed columns, perform a backjoin with the base table.
If set to the string_BASE_TABLE, use the base tablefor the index strategy instead of an index. Note that this is the onlyvalid value whenFORCE_INDEX is used in a statement hintexpression.

Note:FORCE_INDEX is actually a directive, not a hint,which means an error is raised if the index does not exist.

GROUPBY_SCAN_OPTIMIZATION

TRUE |
FALSE

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

The optimization is applied if the optimizer estimates that it will make thequery more efficient. The hint overrides that decision. If the hint is set toFALSE, the optimization is not considered. If the hint is set toTRUE, the optimization will be applied as long as it is legal to doso.

SCAN_METHOD

AUTO (default) |
BATCH |
ROW

Use this hint to enforce the query scan method.

By default, Spanner sets the scan method asAUTO(automatic) which means depending on the heuristics of the query,batch-oriented query processing might be used to improve query performance. Ifyou want to change the default scanning method fromAUTO, you canuse the hint to enforce aROW orBATCH orientedprocessing method. For more information seeOptimize scans.

Join hints

[ /*@join_hint_key =join_hint_value [, ...] */ ]wherejoin_hint_key is:    FORCE_JOIN_ORDER | JOIN_METHOD | HASH_JOIN_BUILD_SIDE | BATCH_MODE

Spanner supports the following join hints as extensionsto 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 touse for the underlying join method. Learn more inJoin methods.
`HASH_JOIN_BUILD_SIDE`	`BUILD_LEFT` \| `BUILD_RIGHT`	Specifies which side of the hash join is used as the buildside. 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. Canonly be used with`JOIN_METHOD=APPLY_JOIN`.

Function hints

function_name() [ /*@function_hint_key =function_hint_value [, ...] */ ]wherefunction_hint_key is:    DISABLE_INLINE

Spanner supports the following function hints as extensionsto open source PostgreSQL.

Hint key Possible values Description

Hint key	Possible values	Description
`DISABLE_INLINE`	`TRUE` \| `FALSE` (default)	If set to true, the function is computed once instead of each time anotherpart 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 set to true, the function is computed once instead of each time anotherpart of a query references it.

DISABLE_INLINE works with top-level functions.

You can't useDISABLE_INLINE with a few functions, including those that don't produce a scalar value and casting. Although you can't useDISABLE_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 forx.x is computed twice, once by each reference:

SELECTSUBSTRING(x,2,5)ASw,SUBSTRING(x,3,7)ASyFROM(SELECTSHA512(z)ASxFROMt)ASsubquery

In the following example, inline expressions are disabled forx.x iscomputed once, and the result is used by each reference:

SELECTSUBSTRING(x,2,5)ASw,SUBSTRING(x,3,7)ASyFROM(SELECTSHA512(z)/*@ DISABLE_INLINE = TRUE */ASxFROMt)ASsubquery

Join methods

Join methods are specific implementations of the various logicaljoin types. Some join methods are available only for certain jointypes. The choice of which join method to use depends on the specificsof your query and of the data being queried. The best way to figureout if a particular join method helps with the performance of yourquery is to try the method and view the resultingquery executionplan. SeeQueryExecution Operators for more details.

Join Method	Description	Operands
`HASH_JOIN`	The hash join operator builds a hash table out of one side (thebuild side), and probes in the hash table for all the elements in theother side (the probe side).	Different variants are used for various join types. View the queryexecution plan for your query to see which variant is used. Read moreabout theHash joinoperator.
`APPLY_JOIN`	The apply join operator gets each item from one side (the inputside), and evaluates the subquery on other side (the map side) usingthe values of the item from the input side.	Different variants are used for various join types. Cross apply isused for inner join, and outer apply is used for left joins. Readmore about theCross apply andOuter applyoperators.
`MERGE_JOIN`	The merge join operator joins two streams of sorted data. The optimizerwill add Sort operators to the plan if the data is not already providingthe required sort property for the given join condition. The engine providesa distributed merge sort by default, which when coupled with merge join mayallow for larger joins, potentially avoiding disk spilling and improvingscale and latency.	Different variants are used for various join types. View the queryexecution plan for your query to see which variant is used. Read moreabout theMerge joinoperator.
`PUSH_BROADCAST_HASH_JOIN`	The push broadcast hash join operator builds a batch of data from the buildside of the join. The batch is then sent in parallel to all the local splits ofthe probe side of the join. On each of the local servers, a hash join isexecuted between the batch and the local data. This join is most likelyto be beneficial when the input can fit within one batch, but is notstrict. Another potential area of benefit is when operations can bedistributed to the local servers, such as an aggregation that occurs aftera join. A push broadcast hash join can distribute some aggregation where ahash join cannot.	Different variants are used for various join types. View the queryexecution plan for your query to see which variant is used. Read moreabout thePushbroadcast hash join operator.

`UNNEST` operator

unnest_operator:  {UNNEST(array_expression )    | UNNEST(array_path )  }  [table_hint_expr ]  [as_alias ]as_alias:  [AS]alias

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

Input values:

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

array_path: Thepath to anARRAY type.