Postgresql dump/restore column

Question

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.

Dmitry MiksIr · Accepted Answer · 2016-11-08 22:54:31Z

4

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;

answered Nov 8, 2016 at 22:54

Dmitry MiksIr

4,4851 gold badge20 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Loïc Faure-Lacroix Over a year ago

It works great thanks. I ended up using the temp table instead of a dump to file. But the copy from/to file is pretty much what I was looking for.

Collectives™ on Stack Overflow

Postgresql dump/restore column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related