Take the 2-minute tour ×

I've a multi-tenant DB setup, and need to add some columns. I'm using schemas (and search_path) to partition my users, so I'm looking for a ubiquitous way to apply a DDL-schema change to all my databases. Initially, I'd thought I might be able to do it as a single query (cursor on pg_catalog), but thinking a command-line invocation of psql -f might be the preferred way.

share|improve this question
@RolandoMySQLDBA: Postgres instead of PostgreSQL is perfectly OK. –  a_horse_with_no_name Jun 11 '13 at 12:16
add comment

2 Answers

up vote 6 down vote accepted

I would prefer the latter solution. You can collect the schema names into a file (one schema per line) in psql:

\o change_schema.sql
\t on

SELECT n.nspname
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema';

-- reset the output
\t off

Then you can easily do the following:

Have a DDL changing script (for example, change_schema.sql), without reference to the including schema

SET search_path TO :schema;

ADD COLUMN last_modified timestamp;

Then you can turn every line of the schema list into a line like

psql -h dbhost -d targetdb -f change_schema.sql -v schema=<schema_name>

with a simple sed command, for example - then you just have to run these commands. Of course, you can turn it into a proper shell script if you like.

share|improve this answer
This is how I'd do it too, though I'd use the shell to loop over the schema list, rather than sed'ing it, so I had better error handling. Just for completeness, the other approach would be to write it as a PL/PgSQL procedure that used EXECUTE to run the statements as dynamic SQL. –  Craig Ringer Jun 11 '13 at 6:08
add comment

Just for completeness, another approach is to loop over all schemas and run the change with dynamic SQL in PL/PgSQL, eg:

    schemaname name;
   FOR schemaname IN SELECT nspname FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname <> 'information_schema' LOOP
       EXECUTE format('ALTER TABLE %I.my_table ADD COLUMN blah blah;', schemaname);
$$ LANGUAGE plpgsql;
share|improve this answer
add comment

Your Answer


