Export/Import Subset of data in postgres

Question

Using the following query, I'm able to export two tables, selecting 500 items randomly from the first:

copy (select * from (
 select 1 + floor(random() * 1040000)::integer as "documentUUID"
 from generate_series(1, 600) g
 group by 1) r
 join "Document" using ("documentUUID")
 inner join "Citation" on ("Document"."citation_id" = "Citation"."citationUUID")
 limit 500) 
to '/tmp/data.sql';

I want to import this data into my testing database, but COPY FROM doesn't seem to be able to handle multiple table imports.

How can I make this work?

Answer 1

Your problem is that the output (row structure) of your query does not match either of your tables.

A possible solution would be creating a temporary table to put in your randomly generated IDs and copying data from one table at a time, using the necessary joins.

BEGIN;
CREATE TEMPORARY TABLE doc_ids ON COMMIT DROP AS 
SELECT 1 + floor(random() * 1040000)::integer AS documentUUID
 FROM generate_series(1, 500) g;
COPY (
 SELECT d.*
 FROM Document d
 JOIN doc_ids ON i d.documentUUID = i.documentUUID
 )
 TO /tmp/document_data.sql;
COPY (
 SELECT c.*
 FROM Citation c
 JOIN Document d ON d.citation_id = c.citationUUID
 JOIN doc_ids ON i d.documentUUID = i.documentUUID
 )
 TO /tmp/citation_data.sql;
COMMIT;

Note that I removed the double quotes as they don't make any difference for the given table and column names. Also I don't see what is the merit of grouping when generating the random document IDs. Furthermore, I won't call the IDs ...UUID as UUID is a specific type used very often in very diverse applications.

• mlissner: Wow, yeah, this basically worked. The quotes are because I created my tables by migrating from MySQL...and the UUIDs are an old misunderstanding, never to die. Anyway, yes, this works, thanks so much.