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?

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.

  • 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. – mlissner Sep 16 '13 at 21:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.