0

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?

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
1
  • 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. Commented Sep 16, 2013 at 21:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.