alter table in multiple database postgres

Question

I have multiple database, each has table "authentication". In each table I want to drop a constraint and replace it with a new one. That would be great if I had not to do that manually.

ALTER TABLE authentication DROP CONSTRAINT  uk_authentication_01;
ALTER TABLE authentication ADD CONSTRAINT uk_authentication_01 UNIQUE (authenticator, method);

is there a way to do a bulk alter with a script?

You can do that with dblink and database list from pg_database. — Ihor Romanchenko
– Ihor Romanchenko, Commented Jan 25, 2013 at 10:01
hi, I have the list of databases, however the whole thing is not really clear to me. postgresql.org/docs/9.2/static/contrib-dblink-function.html — Sanyifejű
– Sanyifejű, Commented Jan 25, 2013 at 10:27

Daniel Vérité · Accepted Answer · 2013-01-25 17:09:30Z

2

You could iterate over your databases in a shell script:

for db in dbname1 dbname2 dbname3...
do
 psql -d $db -U username << EOF
ALTER TABLE authentication DROP CONSTRAINT  uk_authentication_01;
ALTER TABLE authentication ADD CONSTRAINT uk_authentication_01 UNIQUE (authenticator, method);
EOF
done

answered Jan 25, 2013 at 17:09

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

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

Comments

Borys · Accepted Answer · 2013-01-25 12:41:03Z

0

I assume that all databases are on the same server? If that is true you can simply do the SELECT:

SELECT 'SELECT * FROM  dblink_exec(''dbname=' || datname 
         || '''::text, ''ALTER TABLE authentication 
         DROP CONSTRAINT  uk_authentication_01;
         ALTER TABLE authentication ADD CONSTRAINT uk_authentication_01 
         UNIQUE (authenticator, method);''::text);'
FROM pg_database WHERE datistemplate = false;

Then copy the result of this query and run it.

And you'll need for that dblink extension:

CREATE EXTENSION dblink;

answered Jan 25, 2013 at 12:41

Borys

3,0443 gold badges28 silver badges38 bronze badges

6 Comments

Sanyifejű Over a year ago

yes, they are on the same servlet. Let me try it out, if works I'll be back to accept as the right answer.

Sanyifejű Over a year ago

Sadly, it does not seem to be working. Can we stick to the bare essentials? Let's say I just want to drop the first constraint and I want to perform it on only two databases baml1 and baml2. Those are on the same server.

Borys Over a year ago

Have you installed the extension first?

Borys Over a year ago

When you run just - SELECT datname FROM pg_database WHERE datistemplate = false; Do you get the list of dbs?

Sanyifejű Over a year ago

CREATE EXTENSION dblink; ERROR: could not open extension control file "/usr/pkg/share/postgresql/extension/dblink.control": No such file or directory

|

Collectives™ on Stack Overflow

alter table in multiple database postgres

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related