2017-10-14T21:32:03

insert into when not exists sql

Example SQL insert into with an exists condition.

db=> BEGIN;
BEGIN
db=> CREATE TABLE role (
db(>     id SERIAL PRIMARY KEY,
db(>     ident TEXT UNIQUE
db(> );
CREATE TABLE
db=>
db=> INSERT INTO role (ident)
db->     SELECT 'developer' WHERE NOT EXISTS (
db(>         SELECT 1 FROM role WHERE ident='developer');
INSERT 0 1
db=> INSERT INTO role (ident)
db->     SELECT 'developer' WHERE NOT EXISTS (
db(>         SELECT 1 FROM role WHERE ident='developer');
INSERT 0 0
db=> INSERT INTO role (ident)
db->     SELECT 'developer' WHERE NOT EXISTS (
db(>         SELECT 1 FROM role WHERE ident='developer');
INSERT 0 0
db=> INSERT INTO role (ident)
db->     SELECT 'cto' WHERE NOT EXISTS (
db(>         SELECT 1 FROM role WHERE ident='cto');
INSERT 0 1
db=> INSERT INTO role (ident)
db->     SELECT 'ceo' WHERE NOT EXISTS (
db(>         SELECT 1 FROM role WHERE ident='ceo');
INSERT 0 1
db=>
db=> SELECT * FROM role ORDER BY ID;
 id |   ident
----+-----------
  1 | developer
  2 | cto
  3 | ceo
(3 rows)
=> ROLLBACK;
ROLLBACK