The following bug has been logged online:

Bug reference: 2850
Logged by: Tony Marston
Email address: tony (AT) marston-home (DOT) demon.co.uk
PostgreSQL version: 8.2
Operating system: Windows XP
Description: Cannot select from information_schema.schemat
Details:

If I am logged on as a user other than 'postgres' and I try the query

SELECT * FROM information_schema.schemata

I get no results. Yet if I try the equivalent query

SELECT * FROM pg_namespace

I can see all the available schema names.

Why is there a difference when the two queries are supposed to provide the
same results?

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

"Tony Marston" <tony (AT) marston-home (DOT) demon.co.uk> writes:
They're not "supposed to provide the same results". Per SQL99, the
schemata view is supposed to

Identify the schemata in a catalog that are owned by a given user.

and the SQL definition in the spec makes it clear that it only shows
schemas owned by CURRENT_USER or a role that CURRENT_USER is a member
of.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--On Donnerstag, Dezember 21, 2006 15:47:40 +0000 Tony Marston
<tony (AT) marston-home (DOT) demon.co.uk> wrote:

The standard doesn't specify any given user, it specifies CURRENT_USER. Only
if CURRENT_USER is the owner of a schema (or CURRENT_USER inherits
ownership by membership) you are able (and allowed) to see the schema. While
I admit that this makes it hard to identify catalog schemata by an DBA via
the information_schema, the standard is quite clear here. I don't see any
ambiguity here....

If you need to go beyond what the standard allows, you have to use the
system
catalog.

--
Thanks

Bernd

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate