PostgreSQL数据库中插入数据并跳过重复记录

不止于python

发布于 2023-11-16 11:28:18

7490

发布于 2023-11-16 11:28:18

直接开始

快速预览
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语句

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. 插入重复数据, 则跳过

重复则更新

在实际开发中, 有时会使用到如果存在则更新数据的场景, 这个时候就可以使用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)