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?
-
duplicate of stackoverflow.com/questions/4069718/…Erik K– Erik K2019-03-12 14:34:05 +00:00Commented Mar 12, 2019 at 14:34
-
not exactly the same - in my case I want to insert data from another table (first position)Matexon– Matexon2019-03-12 14:36:36 +00:00Commented Mar 12, 2019 at 14:36
Add a comment
|
3 Answers
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;
1 Comment
Matexon
Excellent answer! Thank you! Works as expected
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
2 Comments
Matexon
OK and what with my last 2 values: 212121, now()?
Caius Jard
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
INSERT INTO tableA(id_client, opr_wpr, data_wpr)
VALUES((SELECT id_client FROM tableB WHERE id != null), 212121, now());