sql: select if table exists

Question

I'm trying to select all from db.table if db.table exists. I've tried if, exists, etc. and none works. What's the best way to do this? I'm using cockroachdb and psycopg2. Didn't find an existing answer (most are quite old) that I could use. For example, this one didn't work me: SQl select all if table exists

I tried the following per Tim's suggestion, which doesn't seem to work. However, the db.table exists.

 select exists (select 1 from information_schema.tables where table_name = 'table')                                                                          ;
  exists  
+--------+
  false   
(1 row)

Time: 6.848579ms

select exists (select 1 from information_schema.tables where table_name = 'db.table')                                                                      ;
  exists  
+--------+
  false   
(1 row)

Time: 5.958707ms

What is your actual underlying SQL database (e.g. MySQL, SQL Server, Oracle, Postgres, etc.)? Every database has its own syntax for checking for this. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 15, 2019 at 6:01
Sorry, I didn't know that psycopg2 is Postgres, I'll try to keep that in mind for next time! — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 15, 2019 at 6:07
@TimBiegeleisen thanks. I tried it and it doesn't seem to work for me. Could you pls take a look at the update above? — Jobs
– Jobs, Commented Aug 15, 2019 at 6:12
Most likely your actual table name is not table. When you do SELECT * FROM information_schema.tables, do you see your table anywhere? — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 15, 2019 at 6:14

Tim Biegeleisen · Accepted Answer · 2019-08-15 06:04:48Z

2

You may handle this in two steps. First, check if the table exists in your Postgres database, and, if so, then select all records:

sql = "SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = %s"
cur.execute(sql, ('your_table',))
if cur.fetchone()[0]:
    sql_all = "SELECT * FROM your_table"
    cur = connection.cursor()    # get a new cursor
    cur.execute(sql_all)
    # process result set etc.

answered Aug 15, 2019 at 6:04

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql: select if table exists

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related