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

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

score 141 · Accepted Answer · edited Oct 25 '20 at 10:26

141

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.

edited Oct 25 '20 at 10:26

Raghav Garg

3,601
2
23
32

answered Mar 29 '13 at 20:01

Clodoaldo Neto

118,695
26
233
260

3

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? – Kevin Burke Aug 07 '15 at 04:30
@Kevin A primary key exception will be raised and the application will decide what to do. – Clodoaldo Neto Aug 07 '15 at 12:18
5

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? – Kevin Burke Aug 07 '15 at 15:05
1

@Kevin I would try the insert first. The question is just about syntax. – Clodoaldo Neto Aug 07 '15 at 16:20
4

Is there a way to insert multiple rows like this? – Callum Rogers Jul 12 '17 at 07:50
@CallumRogers Multiple or Single is fine. it depends on your where clause. – chuongtv Mar 21 '19 at 03:57
@CallumRogers with multiple rows this is not working – thepurpleowl Aug 21 '19 at 08:21
Is this query slower than simple insert query? – Sunbula Khasanova Jan 21 '23 at 14:39

Trenton Trama · Answer 2 · 2017-11-15T21:20:22.370

7

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 '17 at 21:20

answered Mar 29 '13 at 19:38

Trenton Trama

4,890
1
22
27

2

this will keep on increasing the auto incremented id even when there is a conflict(as insertion is attempted) – rohanagarwal Sep 15 '17 at 08:43
"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. – Charles Duffy Nov 15 '17 at 20:37
@CharlesDuffy not in transaction (or not if it's select for update) – Kamil Tomšík Apr 10 '18 at 22:12

score -4 · Answer 3 · answered Mar 31 '22 at 15:43

-4

-- 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 '22 at 15:43

Rahul Nigam

1

1

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](/help/how-to-answer). – Community Mar 31 '22 at 15:45

Conditional INSERT INTO statement in postgres

3 Answers3

Linked

Related