Postgres : pg_restore/pg_dump everything EXCEPT the table id's for a table

Question

Currently I'm doing something like:

pg_dump -a -O -t my_table my_db > my_data_to_import.sql

What I really want is to be able to import/export just the data without causing conflicts with my autoid field or overwriting existing data.

Maybe I'm thinking about the whole process wrong?

Answer 1

You can use COPY with column list to dump and restore just data from one table. For example:

COPY my_table (column1, column2, ...) TO 'yourdumpfilepath';
COPY my_table (column1, column2, ...) FROM 'yourdumpfilepath';

OID is one of the system columns. For example it is not included in SELECT * FROM my_table (you need to use SELECT oid,* FROM my_table). OID is not the same as ordinary id column created along with other columns in CREATE TABLE. Not every table has OID column. Check default_with_oids option. If it's set to off, then probalby you don't have OID column in your table, but even if so, then you can still create table with OID using WITH OIDS option. It's recommended not to use OID as table's column (that's why default_with_oids is set to off prior to PostgreSQL 8.1).