REPLACE

功能描述

在表中插入或者替换新的数据。当插入的数据与原有数据存在主键/唯一键冲突时，执行REPLACE语句会先删除原有数据，再插入新的数据。

REPLACE语句有如下三种形式：

• 值替换插入。即通过VALUES或VALUE构造一行记录，并插入到表中。
• 查询替换插入。通过SELECT子句返回的结果集构造一行或多行记录，并插入到表中。
• 设定指定字段值。与值插入类似，对于没有指定的列取其默认值。

注意事项

• 执行该语句的用户需要有表的DELETE和INSERT权限。
• 主键/唯一键不冲突的情况下，可直接插入；对于主键/唯一键冲突的情况，先删除原有数据，再插入新的数据。
• REPLACE操作返回格式为REPLACE 0 X，X表示删除和插入的操作数。
• REPLACE...SELECT形式，select_list列数必须与待插入的字段数保持一致。
• REPLACE...SET语法中：
  • 若col_name有默认值，则SET col_name = col_name + 1等同于 SET col_name = col_name的默认值 + 1；
  • 若col_name无默认值且没有NOT NULL约束时，则SET col_name = col_name + 1等同于col_name = NULL；
  • 若col_name无默认值但存在NOT NULL约束时，各数据类型的默认零值如下表：

    数据类型	默认零值
    tinyint [unsigned | signed] 、smallint [unsigned | signed]、mediumint [unsigned | signed]、int [unsigned | signed]、bigint [unsigned | signed]、float4、float8、numeric、bool	0，如果numeric指定小数位，则显示小数位。如NUMERIC(10, 3)：0.000。
    char、varchar、tinytext、text、mediumtext、longtext、tinyblob、blob、mediumblob、longblob、bit、varbinary	''
    binary	''，空串长度等于指定的长度，未指定长度时长度为1。
    time	00:00:00。
    date	0000-00-00。
    timestamp	0000-00-00 00:00:00。
    datetime	0000-00-00 00:00:00。
    year	0000。

    

    • date、timestamp和datetime需在宽松模式下才能插入时间零值。
    • 函数表达式的默认值，如果在解析时可计算出结果，则默认值为计算出的常量，否则为NULL。
    • 除以上场景，默认值为NULL。

• REPLACE ... SET语法中，后面设置的col_name依赖于前面col_name的值，如果前面没有设置，则取默认值。如SET f1 = f1 + 1, f2 = f1场景下，f1等于f1的默认值（假设为0）+1，f2等于f1计算后的值1。

• 如果表中存在多个唯一约束，如果所插入数据违反多个唯一约束， 则会删除所有违反约束的数据，插入新的数据。此场景需要注意，可能会误删不需要删除的数据，该场景需要慎重操作。
• 如果插入多行，这些行均与表中同一行数据存在唯一约束冲突，则按照执行顺序，依次进行REPLACE操作。
• SET col_name = col_name + 1，col_name的长度不支持超过1。如SET col_name = table_name.col_name + 1中，table_name.col_name的长度为2，形如B.A、C.B.A、D.C.B.A格式都不支持。
• 浮点型比较时，需要注意浮点型可能存在精度丢失。

语法格式

• 值替换插入。

  REPLACE [ INTO ] table_name
      [ PARTITION ( partition_name [, ... ] ) ]
      [ ( col_name [, ... ] ) ]
      { VALUES | VALUE } ( value [, ... ] ) [, ... ]
      [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];
  
  --其中value格式为：
  { expression | DEFAULT }

• 查询替换插入。

  REPLACE [ INTO ] table_name
      [ PARTITION ( partition_name [, ... ] ) ]
      [ ( col_name [, ... ]) ]
      query
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

• 设置指定字段值。

  REPLACE [ INTO ] table_name
      [ PARTITION ( partition_name [, ... ] ) ]
      SET col_name = value [, ... ];
  
  --其中value格式为：
  { expression | DEFAULT }

参数说明

• table_name

  要插入数据的目标表名。

  取值范围：已存在的表名。

• col_name

  目标表中的字段名。

  • 字段名可以有子字段名或者数组下标修饰。
  • 没有在字段列表中出现的每个字段，将由系统默认值，或者声明时的默认值填充，若都没有则用NULL填充。例如，向一个复合类型中的某些字段插入数据，那么其他字段将是NULL。
  • 目标字段（col_name）可以按顺序排列。如果没有列出任何字段，则默认全部字段，且顺序为表声明时的顺序。
  • 如果VALUE子句和QUERY中只提供了N个字段，则目标字段为前N个字段。
  • VALUE子句和QUERY提供的值在表中从左到右关联到对应列。

  取值范围：已存在的字段名。

• PARTITION ( partition_name [, ... ] )

  指定分区插入操作。其中partition_name为分区名。

  如果VALUE子句的值和指定分区不一致，结果会提示异常。

• value

  待插入的值，value格式为：

  { expression | DEFAULT }

  1. expression表示赋予对应列一个有效表达式或值

     向表中字段插入单引号 " ' "时需要使用单引号自身进行转义。

     如果插入行的表达式不是正确的数据类型，系统试图进行类型转换，若转换不成功，则插入数据失败，系统返回错误信息。

  2. DEFAULT表示对应字段名的缺省值。如果没有缺省值，则为NULL。
• query

  一个查询语句（SELECT语句），将查询结果作为插入的数据。

• RETURNING

  返回实际插入的行，RETURNING列表的语法与SELECT的输出列表一致。

示例

--创建一个新表。
m_db=# CREATE TABLE test(f1 int primary key, f2 int, f3 int);

--插入数据。
m_db=# INSERT INTO test VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3);
INSERT 0 3

--值替换插入数据。
m_db=# REPLACE INTO test VALUES(1, 11, 11);
REPLACE 0 2

--查询值替换插入的结果
m_db=# SELECT * FROM test WHERE f1 = 1;
 f1 | f2 | f3
----+----+----
  1 | 11 | 11
(1 row)

--查询替换插入数据。
m_db=# REPLACE INTO test SELECT 2, 22, 22;
REPLACE 0 2

--查询查询替换插入的结果
m_db=# SELECT * FROM test WHERE f1 = 2;
 f1 | f2 | f3
----+----+----
  2 | 22 | 22
(1 row)

--设置指定字段替换插入数据。
m_db=# REPLACE INTO test SET f1 = f1 + 3, f2 = f1 * 10 + 3, f3 = f2;
REPLACE 0 2

--查询设置指定字段替换插入数据的结果
m_db=# SELECT * FROM test WHERE f1 = 3;
 f1 | f2 | f3
----+----+----
  3 | 33 | 33
(1 row)

--删除表格
m_db=# DROP TABLE test;