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:
- insert with
null
as parentId - Then update the parentId fields in the second statement
But is it possible to do this in a single query?
parentTitle
that is the same as a title in another row, and some have a parentTitle that is NULLinsert
statement as a subquery or something.