PG dump with update

Posted on pią 09 marzec 2018 in postgres

Many times, I need to edit some value in particular row in table. The easiest way is to use pgAdminIII - however I don't use it, I'm addicted to psql.

Consider such table:

maho=# CREATE TABLE example(id SERIAL, vals VARCHAR);
CREATE TABLE
maho=# INSERT INTO example(vals) VALUES('some string value1');
INSERT 0 1
maho=# INSERT INTO example(vals) VALUES('some
maho'# string
maho'# value
maho'# with
maho'# newlines');
INSERT 0 1

Now when I need to edit second value, I need to: * select value from table

maho=# SELECT * FROM example WHERE id=2;
 id |   vals
----+----------
  2 | some    +
    | string  +
    | value   +
    | with    +
    | newlines
(1 wiersz)
  • copy it, somehow strip + chars, prepare appropriate update.

Complicated, annoying.

So I made simple script, which works like pg_dump, but generates UPDATEs not INSERTs.

In this case it would be used like that:

maho@dlaptop:~/workspace/abo3$ ~/utils/public/pg_updump.py -d postgres:///maho -t example id=2 >/tmp/ee.sql

edit /tmp/ee.sql

UPDATE example SET vals='some
string
value
with
newlinesss' WHERE id=2

and include it to psql. Voila'

It be of course very convenient if it's pluggable into psql, but I found no way (so far) to pass connection strings from psql to this script.