INSERT
。例如,MySQL 提供了 INSERT IGNORE
。如何使用 PostgreSQL 模拟
INSERT IGNORE
和 ON DUPLICATE KEY UPDATE
的最佳方法是什么?INSERT
。例如,MySQL 提供了 INSERT IGNORE
。INSERT IGNORE
和 ON DUPLICATE KEY UPDATE
的最佳方法是什么?在PostgreSQL 9.5中,现在有了本地功能(就像MySQL几年前就有的):
INSERT ... ON CONFLICT DO NOTHING/UPDATE ("UPSERT")
9.5为"UPSERT"操作提供支持。 INSERT被扩展以接受ON CONFLICT DO UPDATE / IGNORE子句。此子句指定在出现重复冲突时采取的替代操作。
...
进一步的新语法示例:INSERT INTO user_logins (username, logins)
VALUES ('Naomi',1),('James',1)
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;
编辑:如果您错过了warren的回答,PG9.5现在已经原生支持此功能;是时候升级了!
在Bill Karwin的回答基础上,为了解释一个基于规则的方法会是什么样子(从同一数据库中的另一个模式转移,并且有多列主键):
CREATE RULE "my_table_on_duplicate_ignore" AS ON INSERT TO "my_table"
WHERE EXISTS(SELECT 1 FROM my_table
WHERE (pk_col_1, pk_col_2)=(NEW.pk_col_1, NEW.pk_col_2))
DO INSTEAD NOTHING;
INSERT INTO my_table SELECT * FROM another_schema.my_table WHERE some_cond;
DROP RULE "my_table_on_duplicate_ignore" ON "my_table";
注意:该规则适用于所有的INSERT
操作,直到该规则被删除,所以并不完全是临时的。
INSERT INTO "my_table" SELECT DISTINCT ON (pk_col_1, pk_col_2) * FROM the_tmp_table;
。 - EoghanMDELETE FROM my_table WHERE ctid IN (SELECT ctid FROM (SELECT ctid,ROW_NUMBER() OVER (PARTITION BY pk_col_1,pk_col_2) AS rn FROM my_table) AS dups WHERE dups.rn > 1);
删除重复项。 - sema对于那些使用Postgres 9.5或更高版本的用户,新的ON CONFLICT DO NOTHING语法应该可以工作:
INSERT INTO target_table (field_one, field_two, field_three )
SELECT field_one, field_two, field_three
FROM source_table
ON CONFLICT (field_one) DO NOTHING;
对于那些使用早期版本的人,可以使用右连接(right join)代替:
INSERT INTO target_table (field_one, field_two, field_three )
SELECT source_table.field_one, source_table.field_two, source_table.field_three
FROM source_table
LEFT JOIN target_table ON source_table.field_one = target_table.field_one
WHERE target_table.field_one IS NULL;
target_table
插入了另一行,并且它们的键确实重复,则会出现“唯一性冲突:7 ERROR:违反唯一约束”的错误。我相信锁定target_table
会有所帮助,但并发性显然会受到影响。 - G. KashtanovMERGE
并不是并发安全的 upsert,除非您首先使用 LOCK TABLE
。虽然许多人这样使用它,但这是错误的。 - Craig RingerINSERT INTO manager.vin_manufacturer
(SELECT * FROM( VALUES
('935',' Citroën Brazil','Citroën'),
('ABC', 'Toyota', 'Toyota'),
('ZOM',' OM','OM')
) as tmp (vin_manufacturer_id, manufacturer_desc, make_desc)
WHERE NOT EXISTS (
--ignore anything that has already been inserted
SELECT 1 FROM manager.vin_manufacturer m where m.vin_manufacturer_id = tmp.vin_manufacturer_id)
)
INSERT INTO mytable(col1,col2)
SELECT 'val1','val2'
WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE col1='val1')
query = "INSERT INTO db_table_name(column_name)
VALUES(%s) ON CONFLICT (column_name) DO NOTHING;"
q = 'ALTER id_column serial RESTART WITH 1'
看起来PostgreSQL支持称为“规则”的模式对象。
http://www.postgresql.org/docs/current/static/rules-update.html
您可以为给定表创建一个ON INSERT
的规则,使其在具有给定主键值的行已存在时执行NOTHING
操作,否则使其执行UPDATE
而不是INSERT
操作。
我自己没有尝试过,因此无法从经验中讲述或提供示例。
BEGIN
INSERT INTO tableA (unique_column,c2,c3) VALUES (1,2,3);
EXCEPTION
WHEN unique_violation THEN
UPDATE tableA SET c2 = 2, c3 = 3 WHERE unique_column = 1;
END;
但它有一个性能缺陷(请参见PostgreSQL.org):
包含EXCEPTION子句的语句块比不含该子句的语句块在进入和退出时显著地更耗费性能。因此,不要没有必要就使用EXCEPTION。
在批量操作中,您可以在插入之前始终删除行。删除不存在的行不会导致错误,因此可以安全地跳过。
DEFERRABLE INITIALLY DEFERRED
标志创建外键,那么外键就不会成为问题。 - temoto