Conditional INSERT INTO statement in postgres

Question

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 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?

@MattBall: Working with the installation of postgres on the lab computers. Using that extension isn't in the spec. — The General
– The General, Commented Mar 29, 2013 at 19:31

Raghav Garg · Accepted Answer · 2020-10-25 10:26:55Z

164

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

edited Oct 25, 2020 at 10:26

Raghav Garg

3,7072 gold badges26 silver badges33 bronze badges

answered Mar 29, 2013 at 20:01

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Kevin Burke Over a year ago

I assume there is a (very small) race here? The select subquery runs, returns 0 rows, an insert occurs in another thread, and then the LeadCustomer insert occurs?

Clodoaldo Neto Over a year ago

@Kevin A primary key exception will be raised and the application will decide what to do.

Kevin Burke Over a year ago

I'm confused, the question doesn't mention primary keys anywhere. If there was a primary key on (firstname, surname) why wouldn't you just try the insert first?

Clodoaldo Neto Over a year ago

@Kevin I would try the insert first. The question is just about syntax.

Callum Rogers Over a year ago

Is there a way to insert multiple rows like this?

|

Trenton Trama · Accepted Answer · 2017-11-15 21:20:22Z

8

As of 9.5 version of pgsql upsert is included, using 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 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.

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

edited Nov 15, 2017 at 21:20

answered Mar 29, 2013 at 19:38

Trenton Trama

4,9301 gold badge25 silver badges27 bronze badges

3 Comments

rohanagarwal Over a year ago

this will keep on increasing the auto incremented id even when there is a conflict(as insertion is attempted)

Charles Duffy Over a year ago

"Check the results, and conditionally insert" is prone to race conditions: If you have two threads doing it at the same time, you can get two insertions.

Kamil Tomšík Over a year ago

@CharlesDuffy not in transaction (or not if it's select for update)

Rahul Nigam · Accepted Answer · 2022-03-31 15:43:15Z

-5

-- Use follwing format to insert data in any table like this --

create table user ( user_id varchar(25) primary key, phone_num numeric(15), failed_login int not null default 0, Login time timestamp );

INSERT INTO USER(user_id, phone_num, failed_login, Login time) VALUES ('12345','123456789','3',' 2021-01-16 04:24:01.755');

answered Mar 31, 2022 at 15:43

Rahul Nigam

1

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Conditional INSERT INTO statement in postgres

3 Answers 3

8 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related