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.