PostgreSQL on duplicate update

最新推荐文章于 2024-09-29 07:27:46 发布

weixin_34307464

最新推荐文章于 2024-09-29 07:27:46 发布

阅读量5.3k

点赞数 1

文章标签：数据库 python

原文链接：https://my.oschina.net/beetlerx/blog/1554475

版权

2019独角兽企业重金招聘Python工程师标准>>>

比如现在有个表，使用来控制用户访问页面权限的，

CREATE TABLE user_pages (
  id      SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL                  DEFAULT 0 ::INTEGER,
  page_id INTEGER NOT NULL                  DEFAULT 0 ::INTEGER,
  enabled BOOLEAN NOT NULL                  DEFAULT TRUE
);
create unique index uix_user_page on user_pages(user_id,page_id);

理论上每个人每个页面只应该有一条数据，但是我更改或者插入的时候想使用 insert on duplicate update 的功能, 可以使用 postgresql 提供的FUNCTION 功能

CREATE OR REPLACE FUNCTION replace_into_user_pages(u_id INTEGER, p_id INTEGER, en BOOLEAN)
  RETURNS BOOLEAN AS $$
BEGIN
  IF EXISTS(SELECT id FROM user_pages WHERE user_id = u_id AND page_id = p_id)
  THEN
    UPDATE user_pages SET enabled = en WHERE user_id = u_id AND page_id = p_id;
  ELSE
    INSERT INTO user_pages (user_id, page_id, enabled) VALUES (u_id, p_id, en);
  END IF;
  RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

然后调用 SELECT replace_into_user_pages(1,1,false) 就行了，但是这样只能一条一条插入，在postgesql 大于等于 9.5的版本里面，提供了 on conflict 功能，

INSERT INTO user_pages (user_id, page_id, enabled)
VALUES (1, 1, TRUE), (1, 2, TRUE), (1, 3, FALSE)
ON CONFLICT (user_id, page_id)
  DO UPDATE SET enabled = EXCLUDED.enabled;

ref :

https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql

http://www.postgres.cn/docs/9.6/sql-insert.html

转载于:https://my.oschina.net/beetlerx/blog/1554475