Hi all,
I've received numerous complaints about CREATE SCHEMA IF NOT EXISTS failing
when the user lacks CREATE privileges on the database - even if the schema
already exists. A typical scenario would be a multi-tenant
schema-per-tenant setup, where the schema and tenant user are created
beforehand, but then some database layer or ORM wants to ensure that the
schema is there so the above is issued.
Would it be reasonable to have the above no error if the schema already
exists? That would make it similar to the following (which I'm switching to
in the Entity Framework Core ORM):
DO $$
BEGIN
IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'foo') THEN
CREATE SCHEMA "foo";
END IF;
END $$;
The same could apply to other CREATE ... IF NOT EXISTS variations.
Shay
