3

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?

Matley
  • 1,953
  • 4
  • 35
  • 73
  • duplicate of https://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already – Erik K Mar 12 '19 at 14:34
  • not exactly the same - in my case I want to insert data from another table (first position) – Matley Mar 12 '19 at 14:36

3 Answers3

4

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;
2

Like this:

INSERT INTO tableA(id_client, opr_wpr, data_wpr)
SELECT b.id_client, 212121, now() 
FROM 
  tableB b 
  LEFT JOIN tableA a 
  ON b.id_client = a.id_client
WHERE b.id = 272 AND a.id_client is null;

The select query will only return one row when there is:

  • a record in tableB with id 272
  • no related record in tableA for that b.id_client

When there is no row, the left join will return a null in tableA.id_client. As soon as a row is inserted into tableA for that id_client, the left join will cease to return a null and return the related row instead. It is a condition of the WHERE that the a.id_client be null, which it no longer is. This causes the SELECT statement to return 0 rows instead, meaning nothing is inserted

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • OK and what with my last 2 values: 212121, now()? – Matley Mar 12 '19 at 14:39
  • I don't really understand the question.. You said you only don't want to insert the row if there is another row with the same id_client. You didn't say anything about 21212 or now(). If you don't want to insert a row that has the same id_client/212121/now() then you need to add them to the JOIN's ON clause – Caius Jard Mar 12 '19 at 14:41
0
INSERT INTO tableA(id_client, opr_wpr, data_wpr)
    VALUES((SELECT id_client FROM tableB WHERE id != null), 212121, now());
Nicolas Aoki
  • 78
  • 1
  • 7