Test if PostgreSQL table does NOT exist (with psycopg2)

Question

Using Psycopg2, I need to test whether a postgresql table exists or not.

In a similar question, it is recommended to use the following test:

cursor.execute("select exists(select * from myDb.mytable_%s)" % complementName)
tableExists = cursor.fetchone()[0]
print(tableExists)

This works great if the table already exists, and returns True, but it does not work if the table does not exist. Instead of returning False like I would need, I get an error

ProgrammingError: relation "myDb.mytable_001" does not exist

What am I doing wrong? What should I do in order to get a False statement if the table doesn't exist? Thanks for any help!

EDIT

Following advice in comments, I tried also:

tableExists = cursor.execute("SELECT 1 AS result FROM pg_database WHERE datname='mytable_001'")

and

tableExists = cursor.execute("SELECT EXISTS (SELECT 1 AS result FROM pg_tables WHERE schemaname = 'mySchema' AND tablename = 'mytable_001)')")

But both simply return None, whether the table exists or not. However, I'm not sure of the syntax, maybe you can point out some novice mistake I may be making? Thanks!

EDIT 2 Finally the solution consisted in a combination of the latter query above, and fetching the boolean result as follows:

cursor.execute("SELECT EXISTS (SELECT 1 AS result FROM pg_tables WHERE schemaname = 'mySchema' AND tablename = 'mytable_001');")
tableExists = cursor.fetchone()[0]

Check the System table: http://stackoverflow.com/questions/20582500/how-to-check-if-a-table-exists-in-a-given-schema — Ben H, Mar 09 '17 at 08:54
Another option is to check the `pg_database`: https://www.postgresql.org/docs/9.4/static/catalog-pg-database.html. The query is `SELECT 1 AS result FROM pg_database WHERE datname='FOO'`. If the query returns zero rows the database does not exist. — Kirill, Mar 09 '17 at 08:57
Thanks @BenH , I tried a solution based on your link's advice, but it still doesn't work (see edit above). Any ideas what's wrong? — sc28, Mar 09 '17 at 10:03
Thanks @Kirill, I tried the last query you suggest, assuming `datname` should refer to the table name, but it still doesn't work... — sc28, Mar 09 '17 at 10:04
`tableExists = cursor.execute("SELECT EXISTS (SELECT 1 AS result FROM pg_tables WHERE tablename = 'mytable_001'))")` — Ben H, Mar 09 '17 at 10:35
Thank you! This query correctly returns True/False if the table exists/doesn't exist. However, this kept returning `None` simply because `cursor.execute` doesn't return the actual result, which should be fetched with e.g. `fetchone()` (cf. [here](http://stackoverflow.com/questions/22767603/python-pymysql-select-query-returning-boolean-not-desired-value). I'll post my final working solution above, which was strongly based on your advice. Cheers! — sc28, Mar 09 '17 at 10:58

score 0 · Answer 1 · answered Mar 09 '17 at 09:16

0

You can get info from information schema like:

SELECT table_schema,table_name
FROM information_schema.tables
WHERE table_name like 'pg_database';

answered Mar 09 '17 at 09:16

Alexander Bannikov

1
1

Thanks, but this simply returns `None`, independently of whether the table exists or not. This is the query I tried: `tableExists = cursor.execute("SELECT 'mySchema','mytable_complement' FROM information_schema.tables WHERE 'mytable_complement' like 'pg_database';")`. Any ideas why? – sc28 Mar 09 '17 at 10:13

score 0 · Answer 2 · edited Oct 11 '18 at 09:46

Your query does not seem to right. You are supposed to provide table name for which you are verifying whether it is exist or not...... There are many more ways to verify whether table exist or not why to make it so complex.....

SELECT table_name FROM information_schema.tables where table_schema = 'your schema name' & store output in any variable then verify whether it is empty or not...you should be fine....

or use

query = SELECT EXISTS (SELECT relname FROM pg_class WHERE relname = 'table 
name');
resp = cur.execute(query)
rows = cur.fetchone()
print(rows[0])

this will return True if table exist otherwise False....

Test if PostgreSQL table does NOT exist (with psycopg2)

2 Answers2