0

Let's say I have the following schema:

CREATE TABLE foo (
  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  content text NOT NULL
)

CREATE TABLE bar (
  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  owner text NOT NULL,
  metadata text NOT NULL,
  foo_id integer NOT NULL REFERENCES foo
)

I now wish to deep clone all bar rows with a specific owner such that I also clone the associated foo rows.

The issue is that I cannot figure out how to associate the newly generated foo.id with the old bar that the new foo row is based on. I need that association when constructing a new bar, as it should consist of the old bar.metadata and the new foo.id.

I want to write something like

WITH old_bars AS (
  SELECT *
  FROM bar
  WHERE owner = 'Alice'
), new_foos AS (
  INSERT INTO foo (content)
  SELECT foo.content
  FROM foo, old_bars
  WHERE old_bars.foo_id = foo.id
  RETURNING foo.id AS new_foo_id, old_bars.id AS old_bar_id -- This doesn't work
)
INSERT INTO bar (owner, metadata, foo_id)
SELECT 'Bob', old_bars.metadata, new_foos.new_foo_id
FROM old_bars, new_foos
WHERE old_bars.id = new_foos.old_bar_id

but as noted in the comment, this is not valid Postgres SQL as old_bars is not in scope for the RETURNING part of new_foos.

Is there a decent way to achieve this in PostgreSQL 15?

When looking at some previous posts(1,2), they all seem to be about inserting a single bar and foo as it were. In that case, it is easy to figure out which new_foo_id to insert into bar, as there is only one to choose from.

Erwin's answer in (3) section "INSERT missing FK rows at the same time" seemed promising. However, I cannot figure out how to apply it to my case. The issue is that foo itself doesn't really have an equivalent to type to join on. I could join on content, but I would like to avoid that as content can be quite large and heavy (in reality, it is not a text but a geometry from PostGIS), and it feels weird to first construct the new foo row, then forget about it, and then search for it again using its payload value. This also (somewhat) breaks in the case that content is the same for two foos.

One approach I had thought about, was ensuring that old_bars and new_foos had a consistent ordering somehow, and then merge the two tables based on this ordering (similar to the zip function known from functional languages). This however feels very hacky and un-SQL like, which made hesitant to pursue it further.

Another approach would be to perform multiple queries, and then associate new_foo_id with old_bar_id in the program that calls the database, rather than inside the database. In some ways, this is what I have done in similar situations, e.g. bulk insert, where I can supply the different values as arrays, and I can ensure that these arrays are consistently ordered.

All in all this looks like a commonish problem I expect many people to have encountered over the years and I feel like I am missing some obvious tool or code pattern to solve it.

Referenced posts:

EDIT: Here is some example data and the state I would like to a "deep clone" to produce.

foo before deep clone of 'Alice' to 'Bob':

  id  |           content           
------+-----------------------------
  101 | Sing, O goddess
  102 | the anger of Achilles
  103 | son of Peleus
  104 | that brought countless ills
  105 | upon the Achaeans

bar before deep clone of 'Alice' to 'Bob':

 id |  owner  | metadata | foo_id 
----+---------+----------+--------
  1 | Alice   | abc      |    101
  2 | Alice   | def      |    102
  3 | Charlie | ghi      |    103
  4 | Alice   | jkl      |    104
  5 | Charlie | mno      |    105

foo after deep clone of 'Alice' to 'Bob':

  id  |           content           
------+-----------------------------
  101 | Sing, O goddess
  102 | the anger of Achilles
  103 | son of Peleus
  104 | that brought countless ills
  105 | upon the Achaeans
  106 | Sing, O goddess
  107 | the anger of Achilles
  108 | that brought countless ills

bar after deep clone of 'Alice' to 'Bob':

 id |  owner  | metadata | foo_id 
----+---------+----------+--------
  1 | Alice   | abc      |    101
  2 | Alice   | def      |    102
  3 | Charlie | ghi      |    103
  4 | Alice   | jkl      |    104
  5 | Charlie | mno      |    105
  6 | Bob     | abc      |    106
  7 | Bob     | def      |    107
  8 | Bob     | jkl      |    108
2
  • Tips for asking a good Structured Query Language (SQL) question. Add source/initial data (5-7 rows enough) according to #5, and desired final data state according to #3. With explanations.
    – Akina
    Commented Feb 9, 2023 at 17:33
  • @Akina thank you for linking to that page. I have added example initial and final state as you suggested.
    – cfreksen
    Commented Feb 9, 2023 at 18:45

1 Answer 1

2
WITH insert_into_foo AS (
  INSERT INTO foo (content)
  SELECT f.content
  FROM bar b
  JOIN foo f on f.id = b.foo_id
  WHERE b.owner = 'Alice'
  ORDER BY f.id
  RETURNING id, content
),
enumerate_insertion AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY id) rn
  FROM insert_into_foo
),
enumerate_source AS (
  SELECT f.id, ROW_NUMBER() OVER (ORDER BY f.id) rn
  FROM bar b
  JOIN foo f on f.id = b.foo_id
  WHERE b.owner = 'Alice'
)
INSERT INTO bar (owner, metadata, foo_id)
SELECT 'Bob', bar.metadata, enumerate_insertion.id
FROM enumerate_insertion 
JOIN enumerate_source USING (rn)
JOIN bar ON enumerate_source.id = bar.foo_id;

https://dbfiddle.uk/G4-fyQEq

1
  • Thank you for this, it helped me solve my issue. My understanding of how it works is that 1. we ensure that the added foos are ordered the same as the old bars when these bars are sorted by their ID. 2. foo.id are generated sequentially so the new foos can be ordered by foo.id to get the same order as ordering the bars by bar.id. 3. We store these orderings using ROW_NUMBER() in the enumerate_x tables. 4. We can then join on the ROW_NUMBER() to achieve the zip functionality I mentioned in my question.
    – cfreksen
    Commented Feb 10, 2023 at 9:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.