Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 9 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

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.