Conditional INSERT INTO statement in postgres
DatabasePostgresqlConditional StatementsSql InsertDatabase Problem Overview
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?
Database Solutions
Solution 1 - Database
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.
Solution 2 - Database
> 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.
Solution 3 - Database
-- 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');