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?

For future visitors: The upcoming Postgres 9.3 will have a create schema if not exists — user330315
– user330315, Commented Aug 2, 2013 at 9:26

Peter Eisentraut · Accepted Answer · 2011-08-11 06:44:06Z

138

The following query will tell you whether a schema exists.

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

answered Aug 11, 2011 at 6:44

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Chris Cashwell Over a year ago

Thanks, this was an excellent answer. Here's how I extended it to work perfectly for my needs: SELECT exists(select schema_name FROM information_schema.schemata WHERE schema_name = 'foo');

Shesha Over a year ago

How to check this in c# ? If I use "ExecuteReader" it shows 1 for all queries.

Adam Bruss Over a year ago

Use ExecuteScalar to get true or false back.

RoflWaffle17 Over a year ago

So - even though this is an older posting, I would like to throw in my two cents (as I was utilizing this very same query, but ran into issues). The specific table mentioned above (schemata) only houses schemas in which the user running the query has been granted ownership to the schema in question. Here is the link for documentation. That being said a better solution might be the following: select distinct table_schema from information_schema.tables where table_schema = 'name'

Francisco Gomes Over a year ago

Using single quotes is important! Double quotes didn't work for me. Might be obvious for others, but it wasn't for me :)

|

Borys · Accepted Answer · 2016-08-12 11:57:42Z

73

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

querying information_schema

If you querying pg_namespace directly:

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

Planer's work is much simpler:

enter image description here

So your own solution was the best.

edited Aug 12, 2016 at 11:57

answered Dec 14, 2012 at 11:36

Borys

3,0443 gold badges28 silver badges38 bronze badges

2 Comments

JCKödel Over a year ago

They have literally the same execution time (0.034s). "Some millisecs" is really a stretch here...

Andrzej Martyna Over a year ago

even the time is almost the same IMHO it's worthwhile to get much simpler execution tree (especially with so simple approach)

Glenn · Accepted Answer · 2012-12-01 02:02:07Z

38

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
$$;

answered Dec 1, 2012 at 2:02

Glenn

9,2102 gold badges44 silver badges56 bronze badges

1 Comment

nagylzs Over a year ago

This it the best solution ever! :-)

Vinay · Accepted Answer · 2011-08-10 19:22:24Z

11

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

answered Aug 10, 2011 at 19:22

Vinay

1,0648 silver badges22 bronze badges

3 Comments

Chris Cashwell Over a year ago

Yeah, but say I wanted to have a step between? i.e. Does SchemaXYZ exist? Yes: does it contain X? No: drop and create; Yes: jump out of function

Vinay Over a year ago

During that time we will have to do the select exists(...).I found a couple of links with the same requirement. They also tell the same. :) postgresql.1045698.n5.nabble.com/… -- PostgreSQL social.msdn.microsoft.com/forums/en-US/transactsql/thread/… --- SQL Server

Guy Park Over a year ago

Seeing if something throws an error to check a simple condition, is a terrible approach, when there are so many ways to check for a schema, as others have noted.

gyosifov · Accepted Answer · 2020-04-08 14:13:07Z

9

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

answered Apr 8, 2020 at 14:13

gyosifov

3,2534 gold badges28 silver badges42 bronze badges

Comments

niekas · Accepted Answer · 2023-12-07 08:07:42Z

3

This is valid for the PostgreSQL that will check if the schema exists and if not then will create it:

CREATE SCHEMA IF NOT EXISTS tenant;

edited Dec 7, 2023 at 8:07

niekas

9,2577 gold badges46 silver badges61 bronze badges

answered Jan 15, 2021 at 16:14

Arefe

12.6k25 gold badges135 silver badges197 bronze badges

Comments

Tay Ray Chuan · Accepted Answer · 2012-07-31 09:50:05Z

1

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.

answered Jul 31, 2012 at 9:50

Tay Ray Chuan

535 bronze badges

Comments

m0meni · Accepted Answer · 2016-06-27 19:26:52Z

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.

jian · Accepted Answer · 2023-05-14 01:31:31Z

1

another way to check Schema Exists?

to_regnamespace ( text ) → regnamespace

Translates a textual schema name to its OID. A similar result is obtained by casting the string to type regnamespace (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found.

so you can use:

select to_regnamespace('public') is not null;

answered May 14, 2023 at 1:31

jian

5,0231 gold badge29 silver badges46 bronze badges

Comments

Broken_Window · Accepted Answer · 2014-03-11 16:17:51Z

0

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')

answered Mar 11, 2014 at 16:17

Broken_Window

2,1013 gold badges23 silver badges49 bronze badges

Comments

Junior Chester · Accepted Answer · 2012-01-30 07:25:10Z

-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

answered Jan 30, 2012 at 7:25

Junior Chester

1

2 Comments

Chris Cashwell Over a year ago

This is not accurate for Postgres. Your answer is referring to constraints of TransactSQL, not PostgreSQL.

neno Over a year ago

You can drop a non-empty schema if you add CASCADE: DROP SCHEMA myschema CASCADE;

Collectives™ on Stack Overflow

Postgresql: Check if Schema Exists?

11 Answers 11

6 Comments

2 Comments

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

6 Comments

2 Comments

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related