PostgreSQL数据库中插入数据并跳过重复记录 - 不止于python

直接开始

1
2
3

快速预览  
1. DO UPDATE SET: 重复则更新  
2. DO NOTHING: 重复则跳过

创建表

首先，创建一个表（people），并且主键由字段 name、age 和 gender 组成，以及其它字段（例如 address、comment）等。

SQL语句

CREATE TABLE people (  
    name VARCHAR(100),  
    age INT,  
    gender CHAR(1),  
    address TEXT,  
    comment TEXT,  
    PRIMARY KEY (name, gender, age)  
);

查看表结构

test=# \d people  
                       Table "public.people"  
 Column  |          Type          | Collation | Nullable | Default  
---------+------------------------+-----------+----------+---------  
 name    | character varying(100) |           | not null |  
 age     | integer                |           | not null |  
 gender  | character(1)           |           | not null |  
 address | text                   |           |          |  
 comment | text                   |           |          |  
Indexes:  
    "people_pkey" PRIMARY KEY, btree (name, gender, age)

\d 可以查看表结构，这样具有三个字段组合作为主键的表就建好了。

执行插入测试

正常插入数据

SQL语句

1 2	INSERT INTO people (name, age, gender, address, comment) VALUES ('张三', 30, 'M', '唧唧王国', '程序员')

查看数据

test=# select * from people;  
 name | age | gender | address  | comment  
------+-----+--------+----------+---------  
 张三 |  30 | M      | 唧唧王国 | 程序员  
(1 row)

可以看到数据已经插入到表中了, 当再次插入时就会报错如下:

SQL语句

test=# INSERT INTO people (name, age, gender, address, comment)  
VALUES ('张三', 30, 'M', '唧唧王国', '程序员');  
ERROR:  duplicate key value violates unique constraint "people_pkey"  
DETAIL:  Key (name, gender, age)=(张三, M, 30) already exists.

NOTE

主键重复插入报错, 解决这个问题有三个方案  
1. 不插入重复数据  
2. 插入重复数据更新, 不存在插入  
3. 插入重复数据, 则跳过

1就不说了, 在大批量入库时无法保证, 是最直接也是最难的解决办法, 而且还需要数据的主键进行去重, 下面就在讲一下第2，3中方案

重复则更新

在实际开发中, 有时会使用到如果存在则更新数据的场景, 这个时候就可以使用DO UPDATE SET关键字

SQL语句

INSERT INTO people (name, age, gender, address, comment)  
VALUES ('张三', 30, 'M', '唧唧王国', '老程序员')  
ON CONFLICT (name, gender, age)   
DO UPDATE SET   
    address = EXCLUDED.address,  
    comment = EXCLUDED.comment;

查看数据

test=# select * from people;  
 name | age | gender | address  | comment  
------+-----+--------+----------+----------  
 张三 |  30 | M      | 唧唧王国 | 老程序员  
(1 row)

可以看到数据已经被更新了, 再来插入一条**不存在**的数据测试

SQL语句

INSERT INTO people (name, age, gender, address, comment)  
VALUES ('李四', 25, 'M', '毛里求斯', '程序员')  
ON CONFLICT (name, gender, age)   
DO UPDATE SET   
    address = EXCLUDED.address,  
    comment = EXCLUDED.comment;

查看数据

test=# select * from people;  
 name | age | gender | address  | comment  
------+-----+--------+----------+----------  
 张三 |  30 | M      | 唧唧王国 | 老程序员  
 李四 |  25 | M      | 毛里求斯 | 程序员  
(2 rows)

这条语句可以实现不存在则插入, 存在则更新功能

重复则跳过

还有些时候, 需要这种操作, 如果重复就跳过, 不希望报错也不需要更新更不能影响代码流程, 就可以使用DO NOTHING关键字

SQL语句

INSERT INTO people (name, age, gender, address, comment)  
VALUES ('张三', 30, 'M', '唧唧王国', '程序员')  
ON CONFLICT (name, gender, age)   
DO NOTHING;

查看数据

test=# select * from people;  
 name | age | gender | address  | comment  
------+-----+--------+----------+----------  
 张三 |  30 | M      | 唧唧王国 | 老程序员  
 李四 |  25 | M      | 毛里求斯 | 程序员  
(2 rows)

执行了sql语句后, 没有报错, 而且数据也并没有被更新, 同样, 插入一条不存在的数据测试

SQL语句

INSERT INTO people (name, age, gender, address, comment)  
VALUES ('王五', 28, 'M', '青青草原', '村长')  
ON CONFLICT (name, gender, age)   
DO NOTHING;

查看数据

test=# select * from people;  
 name | age | gender | address  | comment  
------+-----+--------+----------+----------  
 张三 |  30 | M      | 唧唧王国 | 老程序员  
 李四 |  25 | M      | 毛里求斯 | 程序员  
 王五 |  28 | M      | 青青草原 | 村长  
(3 rows)