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

Question

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

Peter Hansen · Accepted Answer · 2013-09-23T13:35:41.497

99

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

edited Sep 23 '13 at 13:35

answered Dec 09 '09 at 14:28

Peter Hansen

21,046
5
50
72

2

Close but better to use `exists()`. :) – jathanism Dec 09 '09 at 14:36
2

I added that, but why is it "better"? – Peter Hansen Dec 09 '09 at 14:41
2

@Peter It is better because it only needs to find the first row matching the `where` condition while `rowcount` will have to retrieve all rows. – Clodoaldo Neto May 11 '13 at 16:39
I like the `exists()` solution, but you should use `cur.fetcone()[0]` instead of `bool(cur.rowcount)` (this request always returns a value where the first column is the exists() result, True or False) – Nonow Dev Jan 26 '17 at 12:14
after executing 1 time I keep getting this error: `psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block` – Ciasto piekarz Feb 20 '18 at 06:15
@Ciastopiekarz This answer is from over 8 years ago... very likely different versions of things are acting differently. – Peter Hansen Mar 07 '18 at 21:37
It seems to me that all these answers are returning that the table exists, even if it does not exist in the database I am connected to, but it does exist in another database. – roschach Dec 24 '20 at 14:57

score 25 · Answer 2 · answered Dec 09 '09 at 14:09

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.

For default table_catalog use ?? – Peter Krauss Jan 04 '14 at 10:27 — Peter Krauss, Jan 04 '14 at 10:27

ChristopheD · Answer 3 · 2009-12-09T14:37:09.137

5

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

edited Dec 09 '09 at 14:37

answered Dec 09 '09 at 14:08

ChristopheD

112,638
29
165
179

score 5 · Answer 4 · edited Mar 05 '18 at 17:52

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

score 2 · Answer 5 · answered Jun 18 '15 at 03:50

#!/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()

score 2 · Answer 6 · answered Jul 20 '20 at 04:03

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.

score 0 · Answer 7 · answered Mar 08 '18 at 09:54

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])

ouonomos · Answer 8 · 2019-04-26T19:56:52.443

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')

Y Y · Answer 9 · 2022-01-30T16:14:04.063

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.

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

9 Answers9

Linked