Postgresql: Check If Schema Exists

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

How to check if PostgreSQL public schema exists?

The information from information_schema.schemata depends on the role you're connected with, so it's not really the right view to query to discover schemas in general.

The doc on information_schema.schemata in 9.3 says:

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

However it's not quite clear (at least to me) from just that sentence, why you can't see public .

In a mailing-list post, Tom Lane has an explanation the goes a bit further:

See http://www.postgresql.org/message-id/11650.1357782995@sss.pgh.pa.us

His conclusion:

As things stand, a non-superuser won't see "public", "pg_catalog", nor
even "information_schema" itself in this view, which seems a tad
silly.

which looks exactly like the problem in this question.

Bottom line: use pg_namespace instead of information_schema.schemata

---

This was amended in version 9.4 to conform to what users expect. The current doc says:

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

USAGE privilege on a schema is now enough to get it from this view.

How to check if a table exists in a given schema

It depends on what you want to test exactly.

Information schema?

To find "whether the table exists" (no matter who's asking), querying the information schema (information_schema.tables) is incorrect, strictly speaking, because (per documentation):

Only those tables and views are shown that the current user has access
to (by way of being the owner or having some privilege).

The query provided by @kong can return FALSE, but the table can still exist. It answers the question:

How to check whether a table (or view) exists, and the current user has access to it?

SELECT EXISTS (
   SELECT FROM information_schema.tables 
   WHERE  table_schema = 'schema_name'
   AND    table_name   = 'table_name'
   );

The information schema is mainly useful to stay portable across major versions and across different RDBMS. But the implementation is slow, because Postgres has to use sophisticated views to comply to the standard (information_schema.tables is a rather simple example). And some information (like OIDs) gets lost in translation from the system catalogs - which actually carry all information.

System catalogs

Your question was:

How to check whether a table exists?

SELECT EXISTS (
   SELECT FROM pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = 'schema_name'
   AND    c.relname = 'table_name'
   AND    c.relkind = 'r'    -- only tables
   );

Use the system catalogs pg_class and pg_namespace directly, which is also considerably faster. However, per documentation on pg_class:

The catalog pg_class catalogs tables and most everything else that has
columns or is otherwise similar to a table. This includes indexes (but
see also pg_index), sequences, views, materialized views, composite
types, and TOAST tables;

For this particular question you can also use the system view pg_tables. A bit simpler and more portable across major Postgres versions (which is hardly of concern for this basic query):

SELECT EXISTS (
   SELECT FROM pg_tables
   WHERE  schemaname = 'schema_name'
   AND    tablename  = 'table_name'
   );

Identifiers have to be unique among all objects mentioned above. If you want to ask:

How to check whether a name for a table or similar object in a given schema is taken?

SELECT EXISTS (
   SELECT FROM pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = 'schema_name'
   AND    c.relname = 'table_name'
   );

• Related answer on dba.SE discussing "Information schema vs. system catalogs"

Alternative: cast to regclass

SELECT 'schema_name.table_name'::regclass;

This raises an exception if the (optionally schema-qualified) table (or other object occupying that name) does not exist.

If you do not schema-qualify the table name, a cast to regclass defaults to the search_path and returns the OID for the first table found - or an exception if the table is in none of the listed schemas. Note that the system schemas pg_catalog and pg_temp (the schema for temporary objects of the current session) are automatically part of the search_path.

You can use that and catch a possible exception in a function. Example:

• Check if sequence exists in Postgres (plpgsql)

A query like above avoids possible exceptions and is therefore slightly faster.

Note that the each component of the name is treated as identifier here - as opposed to above queries where names are given as literal strings. Identifiers are cast to lower case unless double-quoted. If you have forced otherwise illegal identifiers with double-quotes, those need to be included. Like:

SELECT '"Dumb_SchName"."FoolishTbl"'::regclass;

See:

• Are PostgreSQL column names case-sensitive?

to_regclass(rel_name) in Postgres 9.4+

Much simpler now:

SELECT to_regclass('schema_name.table_name');

Same as the cast, but it returns ...

... null rather than throwing an error if the name is not found

Check If Domain Exists In Specific Schema

You almost have it, you just need to add in the typnamespace, as you suspected:

SELECT *
FROM pg_type WHERE typname = 'my_domain_identifier'
AND typnamespace = 'your_schema'::regnamespace;

How do I query if a database schema exists

Are you looking for sys.schemas?

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'jim')
BEGIN
EXEC('CREATE SCHEMA jim')
END

Note that the CREATE SCHEMA must be run in its own batch (per the answer below)

Postgres Function exists in a particular schema

You can adapt following query:

select routine_name, routine_schema 
from information_schema.routines 
where routine_name='myfunction' 
and routine_type='FUNCTION';

---

Related Topics