How to check if PostgreSQL public schema exists?
postgresql, schema, sql
Solution
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.
Problem
Running the following query: ``` SELECT exists ( SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'public' ) AS schema_exists; ``` I am getting always `FALSE`, even if the public schema exists. How should i check if this schema exists? EDIT I am using PostgreSQL version 8.4