5

If I have a table A, like so:

A {
 id SERIAL
 title TEXT
 ...
 parentId INT references A.id via a foreign key constraint
}

I am pulling data from a source table - A_SOURCE - where there isn't a parentId column. Instead there is a parentTitle column. So the source table looks something like this:

A_SOURCE {
  title TEXT
  parentTitle TEXT
}

I started writing an upsert statement to insert into table A via a selection from table A_SOURCE before I realized that I can't easily resolve the parentTitle column in the source to a parentId in the target.

Since I can't be sure that the parent will have been inserted at the time the child being processed, a join or a subquery could return no results.

My upsert statement is looking something like this:

with source as (
  select
   title
   parentTitle
  from A_SOURCE 
)

insert into A
select
title
... I don't think I can resolve to parentId here?
from source
on concflict ...;

I know that I can run two separate statements:

  1. insert with null as parentId
  2. Then update the parentId fields in the second statement

But is it possible to do this in a single query?

12
  • What do you want to do if the parent title is not in the table? Insert 2 rows, one for the parent and another for the child? Aug 28 '19 at 13:58
  • yes - there are N rows in the source table, some of these N rows have a non-null parentTitle that is the same as a title in another row, and some have a parentTitle that is NULL
    – Zach Smith
    Aug 28 '19 at 14:03
  • 1
    Sorry I have to delete my answer. Now I understand better your problem when you say A_SOURCE is coming from CSV file, I see the ids still not exists, as you are using the sequence to generate them... You will need two queries to do the insert effectively. I have a similar case in some system here... Aug 28 '19 at 14:32
  • I thought it was worth asking in case there was a way that I could have an insert statement as a subquery or something.
    – Zach Smith
    Aug 28 '19 at 14:52
3

Prepare the values to insert with a recursive CTE that pre-calculates the ids and defines an order. Then insert it in that order:

WITH RECURSIVE a_sort AS (
      /* get all entries without a parent (rank 1) */
      SELECT nextval('a_id_seq')::integer AS id,
             title,
             NULL::integer AS parentid,
             parenttitle,
             1 AS rank
      FROM a_source
      WHERE parenttitle IS NULL
   UNION ALL
      /* recursively get all the immediate children and increase rank */
      SELECT nextval('a_id_seq')::integer,
             src.title,
             a_sort.id,
             a_sort.title,
             a_sort.rank + 1
      FROM a_source AS src
         JOIN a_sort ON a_sort.title = src.parenttitle
)
INSERT INTO a
SELECT id,
       title,
       parentid
FROM a_sort
ORDER BY rank;

The beauty of the solution is that this uses the sequence behind a.id (a_id_seq in the example), so the sequence automatically has the correct value after we are done.

This solution assumes that the data in a_source are correct, i.e., do not contain cycles.

3
  • That's pretty cool. I don't think that takes into account the currently existing data int A, but I can see that wouldn't be too difficult to add? I.e. just start the rank at the current max + 1 in A (if I'm reading that correctly)
    – Zach Smith
    Aug 29 '19 at 8:59
  • 1
    No, it does that automatically. I should have added that a_id_seq is the serial sequence for a.id. Aug 29 '19 at 9:08
  • well it's currently above my understanding. thank you - i will come back to this for a detailed look!
    – Zach Smith
    Aug 29 '19 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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