SQL check if exists and insert

Question

I'm using a table 'Customer' with the following schema

id INTEGER NOT NULL UNIQUE,
name TEXT NOT NULL,
auth BOOLEAN DEFAULT FALSE

Now, I want to add a record if does not exist, I can do the following

IF NOT EXISTS (SELECT name from Customer where id=220)
BEGIN
    INSERT into Customer (name,id) values ('Jon', 220)
END;

But at the same time, I also want to know if the id really did not exist along with the insertion i.e. True/False result of the select query. I can split it into two queries, from the first I can know if it exists and if id did not then I can insert it. But how can I do this in a single query?

Answer 1

You need to use INSERT with the RETURNING clause (PostgreSQL INSERT).

Answer 2

'on conflict' clause used with INSERT can be customized to serve your purpose.

INSERT INTO <table_name>(<column_name_list))   values(<column_values>)  ON CONFLICT(<constraint_column>) DO NOTHING;

ref: https://www.postgresqltutorial.com/postgresql-upsert/

Set up

Step 1: Create the table:

create table test
(
id INTEGER NOT NULL UNIQUE,
name TEXT NOT NULL,
auth BOOLEAN DEFAULT FALSE
);

Step 2: Load the table with some sample rows:

insert into test(id,name) values(1,'vincent'),(2,'gabriel'),(3,'sebastian');

Step 3: Test with an INSERT of a row with existing id i.e 1 , the insert does not go through as the ID already exists:

INSERT INTO test(id,name)   values(1,'xavier')  ON CONFLICT(id) DO NOTHING;

Step 4: Now test with a row with ID that does not exist.i.e 4. It gets through.

INSERT INTO test(id,name)   values(4,'xavier')  ON CONFLICT(id) DO NOTHING;

Demo:

postgres=# select * from test;
 id |   name    | auth
----+-----------+------
  1 | vincent   | f
  2 | gabriel   | f
  3 | sebastian | f
(3 rows)


postgres=# INSERT INTO test(id,name)   values(1,'xavier')  ON CONFLICT(id) DO NOTHING;
INSERT 0 0
postgres=#

postgres=# select * from test;
 id |   name    | auth
----+-----------+------
  1 | vincent   | f
  2 | gabriel   | f
  3 | sebastian | f
(3 rows)
--- NOTE: no row inserted as ID 1 already exists.

postgres=# INSERT INTO test(id,name)   values(4,'xavier')  ON CONFLICT(id) DO NOTHING;
INSERT 0 1
postgres=# select * from test;
 id |   name    | auth
----+-----------+------
  1 | vincent   | f
  2 | gabriel   | f
  3 | sebastian | f
  4 | xavier    | f -------> new row inserted.
(4 rows)

Answer 3

you can use the following :

INSERT into Customer SELECT 'Jon', 220
Where Not EXISTS (SELECT 1 
                    from Customer
                   where id=220);

Select Cast(@@ROWCOUNT as bit);