PostgreSQL - insert a row in Table A, then update row in Table B with returned id

Question

I have the following tables in a Postgres database (trivialized example):

Table A

person_id    | UNIQUE SERIAL integer
first_name   | text
last_name    | text
full_name_id | integer[]

Table B

full_name_id | UNIQUE SERIAL integer
full_name    | text

For each row of Table A, I would like to insert a new row into Table B with full_name = first_name || last_name, and update the new full_name_id in the corresponding row in Table A.

Is it possible to write a single query that does this?

This is what I have so far (again, trivialized):

WITH a AS (
  SELECT first_name, last_name
  FROM table_a
) 
INSERT
  INTO table_b(full_name)
  SELECT first_name || last_name AS full_name
  FROM a
  RETURNING full_name_id;

In my real-world scenario, a similar query successfully inserts correct rows in Table B. However, I'm not sure what to do next, to update full_name_id in Table A.

Note: the full_name_id column in Table A is an array to better reflect my real-world scenario (one row in Table A can reference multiple rows in Table B). To the best of my knowledge, this means I can't use foreign keys.

Gordon Linoff · Accepted Answer · 2018-02-26 22:13:01Z

2

Yes, you can do:

with i as (
      insert into b (full_name)
          select distinct first_name || ' ' || last_name
          from a
          returning *
    )
update a
    set full_name_id = i.full_name_id
    from i
    where i.full_name = a.first_name || ' ' || a.last_name;

answered Feb 26, 2018 at 22:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL - insert a row in Table A, then update row in Table B with returned id

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related