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?
Best Answer
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.
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.