pg_dump vs COPY (SELECT * FROM my_table)

Question

I need to copy the contents of a table from one database to another database with an identical table which is currently empty.

I plan to dump the table data from the old table and then simply import it to the empty table in the new database. However, I ran into some behavior I don't understand using pg_dump.

I try to dump the table data to a file with the command:

pg_dump -a -t '"my_table"' my_database > /tmp/my_table.sql

This works, but I only get 8 records and there are over 1000 records in the table if I view the table like so:

SELECT * FROM my_table;

So, I tried to use the COPY command to generate a .csv file and I see similar behavior:

COPY my_table TO '/tmp/my_table.csv' WITH CSV HEADER;

I get the same 8 records as pg_dump. But, with:

COPY (SELECT * FROM my_table) TO '/tmp/my_table.csv' WITH CSV HEADER;

I get all 1266 records.

I would assume these commands should all return the same data, but obviously, I'm wrong. What is the difference?

Answer 1

Is it possible that my_table is part of an inheritance hierarchy? I ask because http://www.postgresql.org/docs/9.0/interactive/sql-copy.html#AEN58984 has this:

COPY only deals with the specific table named; it does not copy data to or from child tables. Thus for example COPY table TO shows the same data as SELECT * FROM ONLY table. But COPY (SELECT * FROM table) TO ... can be used to dump all of the data in an inheritance hierarchy.

You should be able to check by running:

SELECT * FROM ONLY my_table;

If that returns just the 8 records then we're on the right track, and we just need to find the child tables (for which How to find child tables that inherit from another table in PSQL will be helpful).

If not then I'm not sure - I wondered if maybe Rules or Triggers were getting involved, but I can't see how at the moment. Still, maybe it gives someone else an idea...?