条件插入到postgres中的语句中

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', cmp.testing@example.com');

But Postgres doesn't support IF statements 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扩展。我想知道是否有方法可以做一些类似的事情或者在这个步骤中是否需要一些用户交互?

2 个解决方案

#1

That specific command can be done like this:

具体的命令可以这样做:

insert into LeadCustomer (Firstname, Surname, BillingAddress, email)
select 
    'John', 'Smith', 
    '6 Brewery close, Buxton, Norfolk', 'cmp.testing@example.com'
where not exists (
    select * from leadcustomer where firstname = 'John' and surname = 'Smith'
);

It will insert the result of the select statement, and the select will only return a row if that customer does not exist.

它将插入select语句的结果，如果该客户不存在，select将只返回一行。

#2

As of 9.5 version of pgsql upsert is included, using INSERT ... ON CONFLICT DO UPDATE ...

从9.5版本的pgsql upsert包括在内，使用INSERT…关于冲突，请更新……

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就没有“更新”功能。您需要做的是运行select查询，看看是否有匹配的行。如果您这样做，那么插入它。

I know you're not wanting an upsert exactly, but it's pretty much the same.

我知道你并不想要一个维护人员，但这几乎是一样的。

#1