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

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.

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.