Is it possible to load data into Postgres using COPY, where the data includes a SELECT statement?

Question

(I think the answer is probably "no", but I haven't managed to find anything where anyone tried to do this. Which maybe is a sign in itself).

So, I have a parent and child table than I'm generating data for in a script. The PK on parent table is an autosequence, the FK on child table into the parent table is the autosequence PK which isn't populated until the parent table's data is loaded.

When I load (much smaller datasets) as SQL in PGAdmin, I can match the records together by using another column as a lookup (eg, I have a column Reference on parent table which I fill with a unique pre-defined value for each record, effectively as a surr)

I load the parent table through COPY, and that works fine. However, in the child table, what I want to be able to do is inculde (select parent_id from parent_table where reference = predefined value) as the value for the Parent ID field.

The problem I'm trying to address is that, at the point the child table's values are created, I don't know what the FK values need to be, because the parent table's value haven't been loaded yet.

To be honest, this probably isn't even a good way to go about this - I have 2 million records in the parent table and 3-4 million in the child, so I don't really want to be making 4 million time 1 record calls into another million record table, but I can't see any obvious way around this without splitting the data generation so that the child table data is generated after the parent table has been loaded.

Better ideas gratefully received!

Answer 1

You do it like bellow:

psql -c '\copy (SELECT * FROM mytable where col1=something) to mytable.dat'

Then load dat file using COPY

psql -c '\copy mytable from mytable.dat'