Add a column to a table in all schemas of a PostgreSQL database

Question

I have a Postgres 8.4 schema like below:

My_Database
 |-> Schemas
       |-> AccountA
       |-> AccountB
       |-> AccountC
       |-> AccountD
       |-> AccountE
      ...
       |-> AccountZ

All schemas have a table called product and I would like to add a boolean column to them at once. Is it possible to do this?

The only way I found until now is to run the below SQL account by account.

ALTER TABLE product ADD COLUMN show_price boolean NOT NULL DEFAULT TRUE;

Erwin Brandstetter · Accepted Answer · 2021-10-07T03:36:04.733

DO
$do$
DECLARE
  _schema text;
  _sp
BEGIN
   FOR _schema IN
      SELECT quote_ident(nspname)  -- prevent SQL injection
      FROM   pg_namespace n
      WHERE  nspname !~~ 'pg_%'
      AND    nspname <>  'information_schema'
   LOOP
      EXECUTE 'SET LOCAL search_path = ' || _schema;
      ALTER TABLE product ADD COLUMN show_price boolean NOT NULL DEFAULT TRUE;
   END LOOP;
END
$do$

You can loop through the entries in system catalog tables with a DO statement. Requires Postgres 9.0 or later.
You can also create a function. The DO statement uses the procedural language plpgsql by default.

The only system catalog you need is pg_namespace, holding the schemas of a database. Loop through all schemas except known system schemas.

Make sure you are connected to the right database!

To add a column to a table with a NOT NULL constraint, you must also provide a default value to fill the new column. Logically impossible otherwise. I added DEFAULT TRUE, adjust to your needs.

Avoid SQL injection by quoting identifiers retrieved from system catalog tables properly. quote_ident() in this case. [There are more options. See:

SQL injection in Postgres functions vs prepared queries

You need dynamic SQL. The primary "trick" is to just set the search_path dynamically, so the same statement can be run over and over. The effect of SET LOCAL lasts till the end of the transaction. You can use RESET search_path or save the prior state and reset it if you need to do more in the same transaction with it (unlikely):

SHOW search_path INTO _text_var;
...
EXECUTE 'SET search_path = ' || _text_var;

score 1 · Answer 2 · answered Nov 28 '13 at 19:29

You'll need to do that in any event.

What you can do is automate it, by querying the catalog, and then wrap the code in a do block (in Postgres 9.0 or later) or using a migration script. To find the needed query if you're unfamiliar with querying the catalog, the simplest is to use psql -E. It'll show hidden queries that get run when you enter \dn and \dt.

Add a column to a table in all schemas of a PostgreSQL database

2 Answers2

Linked