pg_class to check if table exists

20 April 2005   4 comments   Linux

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

Mind That Age!

This blog post is 13 years old! Most likely, its content is outdated. Especially if it's technical.

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!

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 Keyword:
Best EXPLAIN ANALYZE benchmark script 19 April 2018
When Docker is too slow, use your host 11 January 2018
Select all relations in PostgreSQL 10 December 2015
Chainable catches in a JavaScript promise 05 November 2015
How do log ALL PostgreSQL SQL happening 20 July 2015
Related by Text:
jQuery and Highslide JS 08 January 2008
I'm back! Peterbe.com has been renewed 05 June 2005
Anti-McCain propaganda videos 12 August 2008
Ever wondered how much $87 Billion is? 04 November 2003
Guake, not Yakuake or Yeahconsole 23 January 2010