Add a column to a table in all schemas of a PostgreSQL database
ddl, plpgsql, postgresql, postgresql-8.4, sql
Solution
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;
Problem
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; ```