REPLACE INTO

Function

REPLACE INTO inserts new row into a table, or delete old row and insert a new one into a table

Precautions

• You must have the INSERT and DELETE permission on a table to use REPLACE INTO.
• REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Syntax

[ WITH [ RECURSIVE ] with_query [, ...] ]
REPLACE [/*+ plan_hint */] INTO table_name [partition_clause] [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES
    | VALUES {( { expression | DEFAULT } [, ...] ) }[, ...] 
    | query }
    [ ON DUPLICATE KEY UPDATE { NOTHING | { column_name = { expression | DEFAULT } } [, ...] [ WHERE condition ] }]
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

Parameter Description

• WITH [ RECURSIVE ] with_query [, …]

  Specifies one or more subqueries that can be referenced by name in the main query, which is equivalent to a temporary table.

  If RECURSIVE is specified, it allows a SELECT subquery to reference itself by name.

  Format of with_query:

  with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ]
  ( {select | values | insert | update | delete} )
  

  – with_query_name specifies the name of the result set generated by a subquery. Such names can be used to access the result sets of subqueries in a query.

  -- column_name specifies the column name displayed in the subquery result set.

  Each subquery can be a SELECT, VALUES, INSERT, UPDATE or DELETE statement.

• table_name

  Specifies the name of the target table where data will be inserted.

  Value range: an existing table name

• partition_clause

  Inserts data to a specified partition.

  PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } |
  SUBPARTITION { ( subpartition_name ) | FOR ( subpartition_value [, ...] ) }
  

  For details about the keywords, see SELECT.

  If the value of the value clause is inconsistent with the specified partition, an error is reported.

  For details, see CREATE TABLE SUBPARTITION.

• column_name

  Specifies the name of a column in a table.

  • The column name can be qualified with a subfield name or array subscript, if needed.
  • Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or NULL if there is none. Inserting into only some fields of a composite column leaves the other fields null.
  • The target column names column_name can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order.
  • The target columns are the first N column names, if there are only N columns supplied by the value clause or query.
  • The values provided by the value clause and query are associated with the corresponding columns from left to right in the table.

  Value range: an existing column

• expression

  Specifies an expression or a value to assign to the corresponding column.

  • If single-quotation marks are inserted in a column, the single-quotation marks need to be used for escape.
  • If the expression for any column is not of the correct data type, automatic type conversion will be attempted. If the attempt fails, data insertion fails, and the system returns an error message.

• DEFAULT

  Specifies the default value of a field. The value is NULL if no default value is assigned to it.

• query

  Specifies a query statement (SELECT statement) that uses the query result as the inserted data.

• RETURNING

  Returns the inserted rows. The syntax of the RETURNING list is identical to that of the output list of SELECT. Note that INSERT ON DUPLICATE KEY UPDATE does not support the RETURNING clause.

• output_expression

  Specifies an expression used to calculate the output result of the INSERT statement after each row is inserted.

  Value range: The expression can use any field in the table. You can use the asterisk (*) to return all fields of the inserted row.

• output_name

  Specifies a name to use for a returned column.

  Value range: a string. It must comply with the identifier naming convention.

Examples

-- Create the test table.
openGauss=# CREATE TABLE test (col1 int PRIMARY KEY, col2 INT);

-- Insert multi records into a table use REPLACE:
openGauss=# REPLACE INTO test values(1,2);
openGauss=# REPLACE INTO test values(2,3);
openGauss=# REPLACE INTO test values(3,4);
openGauss=# REPLACE INTO test values(4,5);

-- Check the data in test:
openGauss=# select * from test;
 col1 | col2
------+------
    1 |    2
    2 |    3
    3 |    4
    4 |    5
(4 rows)

-- Insert new records which have the same value of primary key.
openGauss=# REPLACE INTO test values(1,5);
openGauss=# REPLACE INTO test values(2,6);

-- Check the data intest.
openGauss=# select * from test;
 col1 | col2
------+------
    3 |    4
    4 |    5
    1 |    5
    2 |    6
(4 rows)