Marcio Mazzucato Marcio Mazzucato - 1 year ago 92
SQL Question

How to check if PostgreSQL public schema exists?

Running the following query:

SELECT exists(
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name = 'public'
) as schema_exists;

I am getting always
, even if the public schema exists.

How should i check if this schema exists?


I am using PostgreSQL version 8.4

Answer Source

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:

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download