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!

• Vérace: I would use a staging table for functionality like this. Feed it (COPY) into the staging table and then perform your select.
• khafka: Unfortunately, I can't change anything in the deployed db structure, so no temporary tables - I agree that would be a better way to go
• Vérace: Use SQLite and then feed that into your PostgreSQL system?
• khafka: I strongly suspect I've going to have to load the parent table first, then select all the parent IDs from there and use those in the generation of the child table. PITA, but probably unavoidable
• Vérace: Those PITA's are why we get the paid the big bucks! :-) (I wish...)

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'

• khafka: That's a different problem to the one I've got - I'm not trying to select from an existing table, I have a load where every line is a distinct select statement