CODEWITHSUNDEEP
databasepostgresqlshellalter-table
Sanyifejű
Sanyifejű

Reputation: 2740

alter table in multiple database postgres

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?

Upvotes: 0

Views: 1100

Answers (2)

Daniel Vérité
Daniel Vérité

Reputation: 61506

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

Upvotes: 2

Borys
Borys

Reputation: 3034

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;

Upvotes: 0

Related Questions