PostgreSQL on duplicate update

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

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

比如现在有个表，使用来控制用户访问页面权限的， 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