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

Gordon Linoff · Accepted Answer · 2021-09-10 15:53:40Z

3

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.

answered Sep 10, 2021 at 15:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rahul Biswas Over a year ago

it's really nice answer and I learn a lot from this. Thanks a lot.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related