This blog post is 16 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.
-
Previous:
- Google blogs about their Maps 19 April 2005
- Next:
- Serious flaw in Bose headphones 23 April 2005
- Related by category:
- How to create-react-app with Docker 17 November 2017 Linux
- Be very careful with your add_header in Nginx! You might make your site insecure 11 February 2018 Linux
- Linux tip: du --max-depth=1 27 September 2007 Linux
- set -ex - The most useful bash trick of the year 31 August 2014 Linux
- Run something forever in bash until you want to stop it 13 February 2018 Linux
- Related by keyword:
- Chainable catches in a JavaScript promise 05 November 2015
- When Docker is too slow, use your host 11 January 2018
- Select all relations in PostgreSQL 10 December 2015
- Careful with your assertRaises() and inheritance of exceptions 10 April 2013
-
Connecting with psycopg2 without a username and password 24 February 2011
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
Thanks!