Postgresql: Check if Schema Exists?

Postgresql: Check if Schema Exists?

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 by Vinay for Postgresql: Check if Schema Exists?

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 by Peter Eisentraut for Postgresql: Check if Schema Exists?

The following query will tell you whether a schema exists.

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

Answer by Junior Chester for Postgresql: Check if Schema Exists?

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 by Tay Ray Chuan for Postgresql: Check if Schema Exists?

From http://www.postgresql.org/docs/9.1/static/infoschema-schemata.html (emphasis my own):

The view schemata contains all schemas in the current database that are owned by a currently enabled role.

So your original solution/query is more reliable than Peter's, albeit non-standard.

Answer by Glenn for Postgresql: Check if Schema Exists?

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 by Borys for Postgresql: Check if Schema Exists?

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 by Broken_Window for Postgresql: Check if Schema Exists?

This one worked for me (Postgres 9.3):

Select exists (SELECT 1 FROM information_schema.schemata where catalog_name = 'My_BD_with_UpperCase_characters_in_its_Name')  

Answer by AR7 for Postgresql: Check if Schema Exists?

Use

SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_namespace WHERE nspowner <> 1 AND nspname = 'schemaname');  

If you check https://www.postgresql.org/docs/current/static/infoschema-schemata.html, you see

The view schemata contains all schemas in the current database that the current user has access to (by way of being the owner or having some privilege).

This means the query in accepted answer using information_schema.schemata doesn't show schemas that the current user isn't the owner of or doesn't have the USAGE privilege on.

SELECT 1  FROM pg_catalog.pg_namespace  WHERE nspowner <> 1 -- ignore tables made by postgres itself  AND nspname = 'schemaname';  

is more complete and will show all existing schemas that postgres didn't make itself regardless of whether or not you have access to the schema.

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72