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 ] 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 [, ...] ) ] ] ( 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 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 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 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
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
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 may want to avoid 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 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 will cause a deadlock, because each transaction's shared lock is preventing the other transaction(s) 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 is just 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. |
SCAN_METHOD |
AUTO (default) |BATCH |ROW |
Use this hint to enforce the query scan method.
By default, Spanner sets the scan method as |
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 |
---|---|---|
FORCE_INDEX |
String. The name of an existing index in the database or
_BASE_TABLE to use the base table rather than an index. |
Note: |
GROUPBY_SCAN_OPTIMIZATION |
TRUE |FALSE |
The group by scan optimization can make queries faster if they use
The optimization is applied if the optimizer estimates that it will make the
query more efficient. The hint overrides that decision. If the hint is set to
|
SCAN_METHOD |
AUTO (default) |BATCH |ROW |
Use this hint to enforce the query scan method.
By default, Spanner sets the scan method as |
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. Learn more in 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 .
|
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). | Different variants are used for various join types. View the query execution plan for your query to see which variant is used. Read more about the 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. | Different variants are used for various join types. Cross apply is used for inner join, and outer apply is used for left joins. Read more about the 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 may allow for larger joins, potentially avoiding disk spilling and improving scale and latency. | Different variants are used for various join types. View the query execution plan for your query to see which variant is used. Read more about the 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. The batch is then sent in parallel to all the local splits of the probe side of the join. On each of the local servers, a hash join is executed 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. | Different variants are used for various join types. View the query execution plan for your query to see which variant is used. Read more about the Push broadcast hash join operator. |
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.
Input values:
array_expression
: an expression that produces an array.table_name
: The name of a table.array_path
: The path to anARRAY
type.Example:
SELECT * FROM UNNEST (ARRAY[10,20,30]) as numbers; /*---------* | numbers | +---------+ | 10 | | 20 | | 30 | *---------*/
alias
: An alias for a value table. An input array that produces a single column can have an optional alias, which you can use to refer to the column elsewhere in the query.