pg_class to check if table exists

20 April 2005   4 comments   Linux

http://www.vitavoom.com/postgresql-docs/catalog-pg-class.html

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.

Comments

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)
dd
For functionality similar to mysql SHOW TABLES use: select * from information_schema.tables where table_schema='public' and table_type='BASE TABLE' .
toruvinn
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
Thanks!
Thank you for posting a comment

Your email will never ever be published


Related posts

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