Checking if a postgresql table exists under python (and probably Psycopg2)

How can I determine if a table exists using the Psycopg2 Python library? I want a true or false boolean.

How about:

>>> import psycopg2
>>> conn = psycopg2.connect("dbname='mydb' user='username' host='localhost' password='foobar'")
>>> cur = conn.cursor()
>>> cur.execute("select * from information_schema.tables where table_name=%s", ('mytable',))
>>> bool(cur.rowcount)
True

An alternative using EXISTS is better in that it doesn't require that all rows be retrieved, but merely that at least one such row exists:

>>> cur.execute("select exists(select * from information_schema.tables where table_name=%s)", ('mytable',))
>>> cur.fetchone()[0]
True

I don't know the psycopg2 lib specifically, but the following query can be used to check for existence of a table:

SELECT EXISTS(SELECT 1 FROM information_schema.tables 
              WHERE table_catalog='DB_NAME' AND 
                    table_schema='public' AND 
                    table_name='TABLE_NAME');

The advantage of using information_schema over selecting directly from the pg_* tables is some degree of portability of the query.

select exists(select relname from pg_class 
where relname = 'mytablename' and relkind='r');

The first answer did not work for me. I found success checking for the relation in pg_class:

def table_exists(con, table_str):
    exists = False
    try:
        cur = con.cursor()
        cur.execute("select exists(select relname from pg_class where relname='" + table_str + "')")
        exists = cur.fetchone()[0]
        print exists
        cur.close()
    except psycopg2.Error as e:
        print e
    return exists

#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


con = None

try:
     
    con = psycopg2.connect(database='testdb', user='janbodnar') 
    cur = con.cursor()
    cur.execute('SELECT 1 from mytable')          
    ver = cur.fetchone()
    print ver    //здесь наш код при успехе
    

except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)
    
    
finally:
    
    if con:
        con.close()

I know you asked for psycopg2 answers, but I thought I'd add a utility function based on pandas (which uses psycopg2 under the hood), just because pd.read_sql_query() makes things so convenient, e.g. avoiding creating/closing cursors.

import pandas as pd

def db_table_exists(conn, tablename):
    # thanks to Peter Hansen's answer for this sql
    sql = f"select * from information_schema.tables where table_name='{tablename}'" 
    
    # return results of sql query from conn as a pandas dataframe
    results_df = pd.read_sql_query(sql, conn)

    # True if we got any results back, False if we didn't
    return bool(len(results_df))

I still use psycopg2 to create the db-connection object conn similarly to the other answers here.

The following solution is handling the schema too:

import psycopg2

with psycopg2.connect("dbname='dbname' user='user' host='host' port='port' password='password'") as conn:
    cur = conn.cursor()
    query = "select to_regclass(%s)"
    cur.execute(query, ['{}.{}'.format('schema', 'table')])

exists = bool(cur.fetchone()[0])

Expanding on the above use of EXISTS, I needed something to test table existence generally. I found that testing for results using fetch on a select statement yielded the result "None" on an empty existing table -- not ideal.

Here's what I came up with:

import psycopg2

def exist_test(tabletotest):

	schema=tabletotest.split('.')[0]
	table=tabletotest.split('.')[1]
	existtest="SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = '"+schema+"' AND table_name = '"+table+"' );"

	print('existtest',existtest)

	cur.execute(existtest) # assumes youve already got your connection and cursor established

	# print('exists',cur.fetchall()[0])
	return ur.fetchall()[0] # returns true/false depending on whether table exists
	 

exist_test('someschema.sometable')

You can look into pg_class catalog:

> The catalog pg_class catalogs tables and most everything else that has > columns or is otherwise similar to a table. This includes indexes (but > see also pg_index), sequences (but see also pg_sequence), views, > materialized views, composite types, and TOAST tables; see relkind. > Below, when we mean all of these kinds of objects we speak of > “relations”. Not all columns are meaningful for all relation types.

Assuming an open connection with cur as cursor,

# python 3.6+
table = 'mytable'
cur.execute(f"SELECT EXISTS(SELECT relname FROM pg_class WHERE relname = {table});")

if cur.fetchone()[0]:
    # if table exists, do something here
    return True

cur.fetchone() will resolve to either True or False because of the EXISTS() function.