PostgreSQL中的upsert和returning的功能实操

叶同学专栏 2022-06-23

1124

UPSERT

更新插入，即实现有则更新、无则插入的功能，在PostgreSQL数据库中，它不是通过UPSERT关键字，而是通过INSERT ... ON CONFLICT DO UPDATE ...的方式实现，当ON CONFLICT子句为出现唯一性违背或排除约束违背错误时提供另一种可供选择的动作,并且它能保证一个原子的INSERT或者UPDATE的结果。

下面是实操演示：

创建测试表

DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
  customer_id serial PRIMARY KEY,
  name VARCHAR UNIQUE,
  email VARCHAR NOT NULL,
  active bool NOT NULL DEFAULT TRUE
);
复制

查看表，name上有唯一约束

postgres=# \d+ customers
                                                            Table "public.customers"
   Column    |       Type        | Collation | Nullable |                    Default                     | Storage  | Stats target | Description 
-------------+-------------------+-----------+----------+------------------------------------------------+----------+--------------+-------------
 customer_id | integer           |           | not null | nextval('customers_customer_id_seq'::regclass) | plain    |              | 
 name        | character varying |           |          |                                                | extended |              | 
 email       | character varying |           | not null |                                                | extended |              | 
 active      | boolean           |           | not null | true                                           | plain    |              | 
Indexes:
    "customers_pkey" PRIMARY KEY, btree (customer_id)
    "customers_name_key" UNIQUE CONSTRAINT, btree (name)
Access method: heap
复制

正常插入数据

postgres=# INSERT INTO customers (name, email) VALUES ('yejf1', 'yejf1@test.com'),('yejf2', 'yejf2@test.com');
INSERT 0 2
复制

正常插入有违反name唯一约束的数据，报错，数据未插入

postgres=# INSERT INTO customers (name, email) VALUES ('yejf1', 'yejf1@test.com'),('yejf3', 'yejf3@test.com');
ERROR:  duplicate key value violates unique constraint "customers_name_key"
DETAIL:  Key (name)=(yejf1) already exists.
postgres=# select * from customers;
 customer_id | name  |     email      | active 
-------------+-------+----------------+--------
           1 | yejf1 | yejf1@test.com | t
           2 | yejf2 | yejf2@test.com | t
(2 rows)
复制

插入时使用upsert功能，有冲突的数据指定DO NOTHING，未冲突的数据能够正常插入

postgres=# INSERT INTO customers (name, email) VALUES ('yejf1', 'yejf1@test.com'),('yejf3', 'yejf3@test.com')
postgres-# ON CONFLICT ON CONSTRAINT customers_name_key DO NOTHING ;
INSERT 0 1
postgres=# select * from customers;
 customer_id | name  |     email      | active 
-------------+-------+----------------+--------
           1 | yejf1 | yejf1@test.com | t
           2 | yejf2 | yejf2@test.com | t
           5 | yejf3 | yejf3@test.com | t
(3 rows)
复制

上面的sql等价于

postgres=# INSERT INTO customers (name, email) VALUES ('yejf1', 'yejf1@test.com'),('yejf3', 'yejf3@test.com') ON CONFLICT (name) DO NOTHING ;
INSERT 0 0
复制

使用upsert功能，对冲突的数据指定更新的内容，如下name='yejf1'有冲突，但插入时更新了email的内容

postgres=# INSERT INTO customers (name, email) VALUES ('yejf1', 'yejf11@test.com') 
postgres-# ON CONFLICT(name) 
postgres-# DO UPDATE SET email = EXCLUDED.email; 
INSERT 0 1
postgres=# select * from customers;
 customer_id | name  |      email      | active 
-------------+-------+-----------------+--------
           2 | yejf2 | yejf2@test.com  | t
           5 | yejf3 | yejf3@test.com  | t
           1 | yejf1 | yejf11@test.com | t
(3 rows)
复制

同时也可以在upsert时限制条件

postgres=# INSERT INTO customers (name, email) VALUES ('yejf1', 'yejf111@test.com') 
postgres-# ON CONFLICT(name) 
postgres-# DO UPDATE SET email = EXCLUDED.email where customers.customer_id<>1; 
INSERT 0 0
postgres=# select * from customers;                                
 customer_id | name  |      email      | active 
-------------+-------+-----------------+--------
           2 | yejf2 | yejf2@test.com  | t
           5 | yejf3 | yejf3@test.com  | t
           1 | yejf1 | yejf11@test.com | t
(3 rows)
复制

RETURNING

使用RETURNING可以避免执行额外的数据库查询来收集数据，并且在否则难以可靠地识别修改的行时尤其有用。

下面是使用returning返回操作的数据示例：

insert

postgres=# INSERT INTO customers (name, email) VALUES ('yejf4', 'yejf4@test.com') returning name;
 name  
-------
 yejf4
(1 row)
INSERT 0 1
复制

delete

postgres=# delete from customers where name='yejf1' returning name,email;
 name  |      email      
-------+-----------------
 yejf1 | yejf11@test.com
(1 row)
DELETE 1


postgres=# select * from customers where name='yejf1';
 customer_id | name | email | active 
-------------+------+-------+--------
(0 rows)
复制

postgres=# delete from customers where name in('yejf2','yejf3') returning *;
 customer_id | name  |     email      | active 
-------------+-------+----------------+--------
           2 | yejf2 | aaa@test.com   | t
           5 | yejf3 | yejf3@test.com | t
(2 rows)


DELETE 2
复制

update

postgres=# update customers set email='aaa@test.com' where name='yejf2' returning email;
    email     
--------------
 aaa@test.com
(1 row)


UPDATE 1
复制

upsert+returning

postgres=# INSERT INTO customers (name, email) VALUES ('yejf1', 'yejf11@test.com') 
postgres-# ON CONFLICT(name) 
postgres-# DO UPDATE SET email = EXCLUDED.email
postgres-# returning name; 
 name  
-------
 yejf1
(1 row)


INSERT 0 1
复制

postgresql email

文章转载自叶同学专栏，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL中的upsert和returning的功能实操

评论

相关阅读