pg_class to check if table exists

20 April 2005   4 comments   Linux

Powered by Fusion×

I just learnt a better way to check if a PostgreSQL table exists. Before I did this to find out if table mytable is defined:

SELECT * FROM 'mytable' LIMIT 1;

But this had to be wrapped in exception catching application code because I was able to tell if the table existed if the select statement worked. That's the wrong and naive way. The correct way is to use pg_class and look at how many rows were returned:

SELECT relname FROM pg_class 
WHERE relname = 'mytable';

Why have I missed this before? No explanation?

A disadvantage with using pg_class is that it's not ISO standard, meaning that it's specific to PostgreSQL only. The first pattern will work on all relational databases.


Ian Bicking
In psql do "\set ECHO_HIDDEN t" and you can see all the queries it uses for the special backslash commands (like \d)
For functionality similar to mysql SHOW TABLES use: select * from information_schema.tables where table_schema='public' and table_type='BASE TABLE' .
btw. there's a problem with temporary tables, cause they might not be visible (even though they exist). it's better to use:
SELECT 1 FROM pg_catalog.pg_class WHERE relkind = 'r' AND relname = 'name' AND pg_catalog.pg_table_is_visible(oid) LIMIT 1
Peter Bengtsson
Thank you for posting a comment

Your email will never ever be published

Related posts

Google blogs about their Maps 19 April 2005
Serious flaw in Bose headphones 23 April 2005
Related by keywords:
Connecting with psycopg2 without a username and password 24 February 2011
Careful with your assertRaises() and inheritance of exceptions 10 April 2013
Integer division in programming languages 04 August 2004
Adding a year in PostgreSQL 04 February 2004
Fastest database for Tornado 09 October 2013
Optimization of getting random rows out of a PostgreSQL in Django 23 February 2011
To sub-select or not sub-select in PostgreSQL 31 August 2009
UPPER vs. ILIKE 19 April 2010
Date formatting in python or in PostgreSQL 20 July 2004
Speed test between django_mongokit and postgresql_psycopg2 09 March 2010
PostgreSQL, MySQL or SQLite 04 April 2004
Fastest "boolean SQL queries" possible with Django 14 January 2011