sqlpostgresqlinsert-into

SQL Postgres - INSERT INTO WHERE NOT EXISTS

I'd like to insert new row into tableA if doesn't exist row with the given id_client (in PostgreSQL):

INSERT INTO tableA(id_client, opr_wpr, data_wpr)
    VALUES((SELECT id_client FROM tableB WHERE id = 272), 212121, now());

How can I do that?

Solution

  • Use a SELECT as the source of the INSERT:

    with data as (
  SELECT id_client, 212121 as opr_wpr, now() as data_wpr
  FROM tableB 
  WHERE id = 272
)
INSERT INTO tablea(id_client, opr_wpr, data_wpr)
SELECT *
FROM data
WHERE not exists (select * 
                  from tablea
                  where id_client in (select id_client
                                      from data));

    The common table expression is used so that the source condition only needs to be provided once.

    Alternatively if you have a unique constraint on tablea.id_client, then you can simply do:

    INSERT INTO tablea(id_client, opr_wpr, data_wpr)
SELECT id_client, 212121, now()
FROM tableB 
WHERE id = 272
on conflict (id_client) do nothing;