CODEWITHSUNDEEP
postgresqlrestoredump
Loïc Faure-Lacroix
Loïc Faure-Lacroix

Reputation: 13600

Postgresql dump/restore column

What I'd like to do is this

psql -d xxx -c "select user_id from res_partner;" > backup.txt

Then

psql -d xxx -c "update res_partner set user_id = null";

The big problem remain in inserting the data back into the table... Is there a way to dump a particular column of a particular table in postgresql and then to insert everything back?

To add a bit more context, the real problem is that I'm upgrading modules from a webservice called Odoo. Each module might insert/update constraints... In my case, I changed a constraint on the same column to reference an other column. It works good, but when we upgrade the server, it tries to insert back the old foreign key then when my module gets loaded it would add my foreign key... yet since it points to a different column, the old foreign key failed with a constraint error... I'd like to either supress the constraint check during the upgrade or backup restore the datas. Having null value shouldn't raise a constraint error.

Upvotes: 3

Views: 2405

Answers (1)

Dmitry MiksIr
Dmitry MiksIr

Reputation: 4445

Sorry, didn't understand you context but can tell you how to dump/restore your data :). My solution is very simple.

1st step, save data to file
COPY res_partner(id, user_id) TO '/tmp/filename.txt';
2nd step - restore data to temporary table
CREATE TABLE res_partner_tmp(id int, user_id int);
COPY res_partner_tmp(id, user_id) FROM '/tmp/filename.txt';

For you case, may be no need to dump to file, just create copy of table
CREATE TABLE res_partner_tmp AS SELECT id,user_id FROM res_partner;

And last step - restore your data
UPDATE res_partner o SET user_id=c.user_id FROM res_partner_tmp c WHERE o.id=c.id;

Upvotes: 3

Related Questions