Postgres DB Insert data to a table based on another table using Common Table Expression

Question

Table A
-------
id married salaried
1  true    true
2  false   true

Table B
-------
user-id id_a 
45      1
50      2
55      1

Suppose I want to update user with id 55 as not married and not salaried ​into Table B

Steps:

• Check if there is an entry in Table A having married false and salaried false
• If yes will return that id from Table A and add a row in Table B with the returned id
• If no will add a new row in Table A and add a row in Table B using that inserted row id

I need to achieve this using Common Table Expression What I have tried is shown below:

WITH cte as
(select id from A
where
married = false and
salaried = false )
INSERT INTO A(married, salaried)
SELECT  false, false
WHERE NOT EXISTS (select id from cte)

This much part works, I tried to add the entries into Table B as shown below, but it is not working

WITH cte1 as
(select id from A
where
married = false and
salaried = false ),
cte2 as
(INSERT INTO A(married, salaried)
SELECT  false, false
WHERE NOT EXISTS (select id from cte1)
RETURNING id)
UPDATE A set id_a = coalesce(select id from cte1, select id from cte2)
where id = 55

Expected output is as shown below

Table A
-------
id married salaried
1  true    true
2  false   true
3  false   false

Table B
-------
user-id id_a 
45      1
50      2
55      3

Any help is appreciated Thanks in advance

Answer 1

This elaborates on your approach:

WITH cte1 as (
      select id
      from A
      where not married and not salaried
     ),
     cte2 as (
      INSERT INTO A (married, salaried)
          SELECT false, false
          WHERE NOT EXISTS (select id from cte1)
          RETURNING id
     )
update B
    set id_a = coalesce((select id from cte1), (select id from cte2))
    where id = 55;

I might suggest, though, that you put a unique constraint on table A and then use on conflict for the insertion.