This page defines the syntax of the SQL data manipulation language (DML)statements supported for PostgreSQL-dialect databases.

INSERT statement

Use theINSERT statement to add new rows to a table.

INSERT INTOtable_name [ ASalias ]    [ (column_name [, ...] ) ]    { VALUES ( {expression | DEFAULT } [, ...] ) [, ...] |query }    [ ON CONFLICT (conflict_target)conflict_action ]    [ RETURNINGselect-list ]whereconflict_target must be the primary key column(s) in orderand separated by columns.andconflict_action is one of:    DO NOTHING    DO UPDATE SET { column_name [, ...] = excluded.column_name [, ...] }and wherequery is:    a query (SELECT statement) that supplies the rows to be inserted.andselect-list is:    { * |expression [ [ AS ]output_name ] [, ...] }

SeePostgreSQL queries for a description of theSELECT syntax.

INSERTINTOT(id,col_a,col_b)VALUES(1,DEFAULT,1);INSERTINTOT(id,col_a,col_b)VALUES(2,200,2);INSERTINTOT(id,col_b)VALUES(3,3);

For more information about default column values, see theDEFAULT (expression )clause inCREATE TABLE.

For more information about mutations, seeWhat are mutations?.

ON CONFLICT clause

TheON CONFLICT DO NOTHING clause indicates that if the row that you'reinserting already exists in the table,INSERT doesn't throw a uniqueconstraint violation of the primary key, and the row isn't inserted.

TheON CONFLICT DO UPDATE SET clause indicates that if the row that you'reinserting already exists in the table,INSERT doesn't throw a uniqueconstraint violation of the primary key, and the row is updated.

This clause has a few differences from PostgreSQL, resulting in thefollowing restrictions:

• Only permits primary key columns as theconflict_target.
• If the table has composite primary keys, you must specify all primary keycolumns inconflict_target.
• TheDO UPDATE SET clause must list all columns that you specify in theinsert list.

• ForDO UPDATE SET, the update value must be set to the insert value in thequery using the PostgreSQL special aliasexcluded. For example:

  INSERT INTO singers (SingerId, FirstName, LastName)VALUES (1, 'Marc', 'Richards')ON CONFLICT (SingerId)DO UPDATE SET SingerId = excluded.SingerId, FirstName = excluded.FirstName, LastName = excluded.LastName;

• TheWHERE clause inON CONFLICT DO UPDATE clause isn't supported.

Examples forON CONFLICT DO UPDATE SET

For the following table:

CREATETABLESingers(SingerIdintprimarykey,FirstNamevarchar(64),LastNamevarchar(64),Birthdatedate,Statusvarchar(64),SingerInfovarchar(64));

You can use the following query without a columns list:

INSERTINTOSingersVALUES(5,'Zak','Sterling','1996-03-12','active','nationality:"U.S.A."'),(7,'Edie','Silver','1998-01-23','active','nationality:"U.S.A."')ONCONFLICT(SingerId)DOUPDATESETSingerId=excluded.SingerId,FirstName=excluded.FirstName,LastName=excluded.LastName,Birthdate=excluded.Birthdate,Status=excluded.Status,SingerInfo=excluded.SingerInfo;

Or you can use the following query with a columns list:

INSERTINTOSingers(SingerId,LastName)VALUES(5,'Sterling'),(7,'Silver')ONCONFLICT(SingerId)DOUPDATESETSingerId=excluded.SingerId,LastName=excluded.LastName;

RETURNING

Use theRETURNING clause to return the results of theINSERT operation andselected data from the newly inserted rows. This clause is especially useful forretrieving values of columns with default values, generated columns, andauto-generated keys, without having to use additionalSELECT statements.

TheRETURNING clause can capture expressions based on newly inserted rows thatinclude the following:

• *: Returns all columns.
• expression: Represents a column name of the table specified by table_name oran expression that uses any combination of such column names. Column names arevalid if they belong to columns of the table_name. Excluded expressionsinclude aggregate and analytic functions.
• alias: Represents a temporary name for an expression in the query.

For example, the following query inserts two rows into theSingers table, usesRETURNING to fetch the SingerId column from these rows, and computes a newcolumn calledFullName.

INSERTINTOsingers(SingerId,FirstName,LastName)VALUES(7,'Melissa','Garcia'),(8,'Russell','Morales')RETURNINGSingerId,FirstName||' '||LastNameASFullName;

In the following query, we useON CONFLICT DO UPDATE SET is used to updateexisting rows if there is a conflict forSingerId.

INSERTINTOsingers(SingerId,FirstName,LastName)VALUES(7,'Melissa','Garcia'),(8,'Russell','Morales')ONCONFLICT(SingerId)DOUPDATESETSingerId=EXCLUDED.SingerId,FirstName=EXCLUDED.FirstName,LastName=EXCLUDED.LastNameRETURNINGSingerId,FirstName||' '||LastNameASFullName;

For instructions and code samples, seeModify data with the returning DMLstatements.

DELETE statement

Use theDELETE statement to delete rows from a table.

[ /* @statement_hint_expr [, ...] */ ] DELETE FROMtable_name[ /* @table_hint_expr [, ...] */ ]    [ [ AS ]alias ]    [ WHEREcondition ]    [ RETURNINGselect-list ]whereselect-list is:    { * |expression [ [ AS ]output_name ] [, ...] }andstatement_hint_expr is:statement_hint_key = statement_hint_valueandtable_hint_expr is:table_hint_key = table_hint_value

RETURNING

With the optionalRETURNING clause, you can obtain data from rows that arebeing deleted in a table. For example, the following query deletes all rows intheSingers table that contains a singer calledMelissa and returns thedeleted rows.

DELETEFROMSingersWHEREFirstname='Melissa'RETURNING*;

To learn more about the values that you can use in this clause, seeINSERT RETURNING.

UPDATE statement

Use theUPDATE statement to update existing rows in a table.

[ /* @statement_hint_expr [, ...] */ ] UPDATE [ ONLY ]table_name[ /* @table_hint_expr [, ...] */ ] [ * ]    [ [ AS ]alias ]    SET {column_name = {expression | DEFAULT } |        (column_name [, ...] ) = [ ROW ] ( {expression | DEFAULT } [, ...] )    } [, ...]    [ WHEREcondition ]    [ RETURNINGselect-list ]whereselect-list is:    { * |expression [ [ AS ]output_name ] [, ...] }andstatement_hint_expr is:statement_hint_key = statement_hint_valueandtable_hint_expr is:table_hint_key = table_hint_value

Where:

• table_name is the name of a table to update.
• TheSET clause specifies how to modify columns in the rows that matchtheWHERE clause's condition. It's a list of column names or expressionpairs.
• expression is an update expression. The expression can be a literal, a SQLexpression, or a SQL subquery.

• statement_hint_expr is a statement-level hint. The following hints aresupported:

  statement_hint_key	statement_hint_value	Description
  PDML_MAX_PARALLELISM	An integer between 1 to 1000	Sets the maximum parallelism forPartitioned DML queries. This hint is only valid with thePartitioned DML query execution mode.

• table_hint_expr is a hint for accessing the table. The following hints aresupported:

  table_hint_key	table_hint_value	Description
  FORCE_INDEX	Index name	Use specified index when querying rows to be updated.
  FORCE_INDEX	_BASE_TABLE	Don't use an index when querying. Instead, scan the base table.

UPDATE statements must comply with the following rules:

• A column can appear only once in theSET clause.
• The columns in theSET 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 asunique secondary indexes or non-nullable columns.
• Updates with joins are not supported.
• You cannot update primary key columns.

Default values

TheDEFAULT keyword sets the value of a column to its default value. If thecolumn has no defined default value, theDEFAULT keyword sets it toNULL.

The use of default values is subject to current Spanner limits, including the mutation limit. If a column has a default value and it is used in an insert or update, the column is counted as one mutation. For example, assume that in tableT,col_a has a default value. The following updates each result in two mutations. One comes from the primary key, and another comes from either the explicit value (1000) or the default value.

UPDATETSETcol_a=1000WHEREid=1;UPDATETSETcol_a=DEFAULTWHEREid=3;

For more information about default column values, see theDEFAULT (expression )clause inCREATE TABLE.

For more information about mutations, seeWhat are mutations?.

WHERE clause

TheWHERE clause is required. This requirement can help prevent accidentallyupdating all the rows in a table. To update all rows in a table, set thecondition totrue.

TheWHERE clause can contain any valid SQL boolean expression, including asubquery that refers to other tables.

Aliases

TheWHERE clause has an implicit alias totarget_name. This alias lets youreference columns intarget_name without qualifying them withtarget_name. For example, if your statement starts withUPDATE Singers, thenyou can access any columns ofSingers in theWHERE clause. In this example,FirstName andLastName are columns in theSingers table:

UPDATEsingersSETbirthdate='1990-10-10'WHEREfirstname='Marc'ANDlastname='Richards';

You can also create an explicit alias using the optionalAS keyword.

RETURNING

With the optionalRETURNING clause, you can obtain data from rows that arebeing updated in a table. For example, the following query updates all rowswhere the singer first name is equal toRussell and returns the updatedrows.

UPDATESingersSETBirthDate='1990-10-10'WHEREFirstName='Russell'RETURNING*;

To learn more about the values that you can use inthis clause, seeINSERT RETURNING.