Help with PostgreSQL information_schema.tables queries

Databases
1

Hello all.

I am using the following code to check if a table exists in the PostgreSQL schema. In some cases the code works. In others it returns a zero (not existing) even though when I use pgAdmin, the table definitely does exist.

Good Code

SELECT 1 FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='public' AND table_catalog='axcys' AND table_name='accounts'

NG Code

SELECT 1 FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='public' AND table_catalog='axcys' AND table_name='elevators'

The table names are different in the two samples above, but again both exist in pgAdmin, but the elevators are returned as not existing. What am I doing wrong?

Thanks,
Tim

2

Most likely a permissioning issue.

3

Hi James,

Is permissioning done on a table by table basis in PostgreSQL?

4

This is almost certainly an issue with your capitalization. PGAdmin might have created your elevators table as “Elevators” and thus you can only find it via:

SELECT 1 FROM information_schema.tables WHERE table_type=‘BASE TABLE’ AND table_schema=‘public’ AND table_catalog=‘axcys’ AND table_name=‘Elevators’

5

which version of postgresql do you use? the schema can change from version to version. therefore the way you set up the query could change

6

I found the problem - working too late last night!
pgAdmin did NOT show the table - I was looking at a table that holds all of the names of the tables not the actual schema itself. So it was actually reporting correctly.

Thank you for all of your responses and help
Tim