Nikki Nicholas Romero Nikki Nicholas Romero - 3 months ago 23
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

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.