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);
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.