Postgresql: Check if Schema Exists?

Question

I need to create, manage and drop schemas on the fly. If I go to create a schema that already exists, I want to (conditionally, via external means) drop and recreate it as specified. How can I check for the existence of said schema on my Postgres 9 server?

Currently, I'm doing this:

select exists (select * from pg_catalog.pg_namespace where nspname = 'schemaname');

but I feel like there's probably another way... is this the "proper" way to query Postgres for the existence of a particular schema?

Answer 1

NONE of those will work if you have objects (tables,sprocs,views) within a particular schema - IT WILL FAIL during DROP...

CREATE & MANAGE is the easy part.. It's the drop that will get you.. Anyways, I couldn't find a suitable answer, so I posted here for others..

SEE LINK HERE: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/4753d1b8-f547-44c6-b205-aa2dc22606ba/#6eb8238a-305e-40d5-858e-0fbd70454810

Answer 2

If you want to create a schema if it doesn't exist you can just execute:

CREATE SCHEMA IF NOT EXISTS foo

Source: https://www.postgresql.org/docs/current/sql-createschema.html

Answer 3

This can be one of the approaches. Drop the schema first and then create it.

IF EXISTS:
Do not throw an error if the schema does not exist. A notice is issued in this case.

So,

DROP SCHEMA IF EXISTS schema_Name
Create SCHEMA schema_Name

Answer 4

Somewhat related and perhaps of interest to others looking for conditional schema creation. I found myself using code like this in some of my creation scripts:

DO $$
BEGIN

    IF NOT EXISTS(
        SELECT schema_name
          FROM information_schema.schemata
          WHERE schema_name = 'pgcrypto'
      )
    THEN
      EXECUTE 'CREATE SCHEMA pgcrypto';
    END IF;

END
$$;

Answer 5

If you are a total purist or you want to gain some milisecs. I recommend you to make use of postgres native system catalog. One can avoid then nested loop which is caused by calling pg_catalog anyway...

SELECT EXISTS(SELECT 1 FROM information_schema.schemata 
              WHERE schema_name = 'name');

If you querying pg_namespace directly:

SELECT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'name');

Planer's work is much simpler:

So your own solution was the best.

Answer 6

The following query will tell you whether a schema exists.

SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'name';