database postgres 中的条件 INSERT INTO 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15710162/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conditional INSERT INTO statement in postgres
提问by The General
I'm writing a booking procedure for a mock airline booking database and what I really want to do is something like this:
我正在为模拟航空公司预订数据库编写预订程序，我真正想做的是这样的：
IF EXISTS (SELECT * FROM LeadCustomer
WHERE FirstName = 'John' AND Surname = 'Smith')
THEN
INSERT INTO LeadCustomer (Firstname, Surname, BillingAddress, email)
VALUES ('John', 'Smith', '6 Brewery close,
Buxton, Norfolk', '[email protected]');
But Postgres doesn't support IFstatements without loading the PL/pgSQL extension. I was wondering if there was a way to do some equivalent of this or if there's just going to have to be some user interaction in this step?
但是 Postgres 不支持IF没有加载 PL/pgSQL 扩展的语句。我想知道是否有办法做到这一点，或者在这一步中是否只需要一些用户交互？
回答by Clodoaldo Neto
That specific command can be done like this:
该特定命令可以这样完成：
insert into LeadCustomer (Firstname, Surname, BillingAddress, email)
select
'John', 'Smith',
'6 Brewery close, Buxton, Norfolk', '[email protected]'
where not exists (
select * from leadcustomer where firstname = 'John' and surname = 'Smith'
);
It will insert the result of the select statement, and the selectwill only return a row if that customer does not exist.
它将插入 select 语句的结果，select如果该客户不存在，它将只返回一行。
回答by Trenton Trama
As of 9.5 version of pgsql upsert is included, using INSERT ... ON CONFLICT DO UPDATE ...
从 9.5 版本开始包含 pgsql upsert，使用INSERT ... ON CONFLICT DO UPDATE ...
The answer below is no longer relevant. Postgres 9.5 was released a couple years later with a better solution.
下面的答案不再相关。几年后，Postgres 9.5 发布了一个更好的解决方案。
Postgres doesn't have "upsert" functionality without adding new functions.
What you'll have to do is run the select query and see if you have matching rows. If you do, then insert it.
Postgres 没有“ upsert”功能而不添加新功能。
您需要做的是运行选择查询并查看是否有匹配的行。如果你这样做，然后插入它。
I know you're not wanting an upsert exactly, but it's pretty much the same.
我知道你并不完全想要一个 upsert，但它几乎是一样的。
