CODEWITHSUNDEEP
postgresqlpg-dumppg-restore
bdd
bdd

Reputation: 3434

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

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?

Upvotes: 5

Views: 7035

Answers (2)

James Doherty
James Doherty

Reputation: 1411

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

Upvotes: 3

Grzegorz Szpetkowski
Grzegorz Szpetkowski

Reputation: 37934

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

Upvotes: 5

Related Questions