Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

database - Conditional INSERT INTO statement in 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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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 1 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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...