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?

Raynaraynah

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).

Spavin

And to import/export that data without the objects id can I use the OID switch? – Raynaraynah

@Paperino: Yes, there is OIDS boolean option for COPY. – Spavin

Is OID the same thing as what I'm trying to exclude? I.d. for a table Books, my id field is 'id'? Do all columns have to be defined manually? – Raynaraynah

Answer 2

pg_dump --insert -t TABLENAME DBNAME > fc.sql
       cat fc.sql | sed -e 's/VALUES [(][0-9][0-9],/VALUES (/g'|sed -e 's/[(]id,/(/g' > fce.sql
       psql -f fce.sql DBNAME

This dumps the table with columns into fc.sql then uses sed to remove the id, and the value associated with it

Wotton