Nikki Nicholas Romero Nikki Nicholas Romero - 1 year ago 92
SQL Question

How to apply upgrade scripts to multiple PostgreSQL schema at once?

I'm trying to create a multiple-tenant SaaS application using one-database/multiple-schema on PostgreSQL. I was wondering if there is a way to upgrade multiple schema using a single command in PostgreSQL.

I'm looking for something like this :

CREATE TABLE tenants_*.users (id VARCHAR(255) PRIMARY KEY NOT NULL);


Note : I'm also using Liquibase for my schema migration but found no way of doing the multiple-schema-upgrade thing using Liquibase.

Any help would be highly appreciated. Thanks.

Answer Source

There's no built-in way to do this in plain SQL, but you could use plpgsql:

DO
$$
DECLARE
    schemaname name;
BEGIN
   FOR schemaname IN SELECT nspname FROM pg_namespace WHERE nspname LIKE 'tenants_%' LOOP
       EXECUTE format('CREATE TABLE %I.users (id text PRIMARY KEY);', schemaname);
   END LOOP;
END;
$$ LANGUAGE plpgsql;

As a side note you probably don't want a varchar column with character limit, as it has no practical benefit in PostgreSQL. Just use text, unless you really want your values truncated to 255 chars for some reason.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download