PostgreSQL data manipulation language

This page defines the syntax of the SQL data manipulation language (DML) statements supported for PostgreSQL databases in the preview release of the PostgreSQL interface feature.

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 Cloud 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.

INSERT statement

Use the INSERT statement to add new rows to a table.

INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    VALUES ( { expression | DEFAULT } [, ...] ) [, ...]

Default values

Use the DEFAULT keyword to insert the default value of a column. Cloud Spanner assigns the default value of NULL to columns that are not included in the column list.

DELETE statement

Use the DELETE statement to delete rows from a table.

DELETE FROM table_name [ [ AS ] alias ]
    [ WHERE condition ]

UPDATE statement

Use the UPDATE statement to update existing rows in a table.

UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET {
        column_name = { expression | DEFAULT } |
        ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] )
    } [, ...]
    [ WHERE condition ]

UPDATE statements must comply with the following rules:

  • A column can appear only once in the SET clause.
  • The columns in the SET clause can be listed in any order.
  • Each value must be type compatible with its associated column.
  • The values must comply with any constraints in the schema, such as unique secondary indexes or non-nullable columns.
  • Updates with joins are not supported.
  • You cannot update primary key columns.