# 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。                                          |
       
    ![](https://support.huaweicloud.com/distributed-m-comp-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png)
    - 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;
```