PostgreSQL how to check if table exist?

Hello all,

Can anyone advise how to check if a table exists already in PostgreSQL?

I tried some code from google searches, but none seem to work.

Thank you,
Tim

Can’t you just query the table & check for an error?

Alternatively use TableSchema to get a list of tables.

Have you tried the TableSchema method?

https://documentation.xojo.com/api/deprecated/deprecated_class_members/database.tableschema.html

Or query the structure:

SELECT 1
FROM information_schema.tables
WHERE table_type=‘BASE TABLE’
AND table_schema=‘public’
AND table_catalog=‘your_database_name’
AND table_name=‘your_table_name’

Thanks all.

I got this close, but it is inconsistent in Xojo. The same code used in pgAdmin works right everytime where it returns a 0 if it does not exist, and a 1 if it does.

Dim R As Integer = dbExecute("SELECT count(*) FROM information_schema.tables WHERE table_name = 'remotes'; " )

Dunno why it would be inconsistent!
Tim

Dim rs As Recordset = SQLSelect("SELECT count(*) FROM information_schema.tables WHERE table_name = 'remotes'; " ) Dim r As Integer = rs.idxField(1).Integervalue
maybe more consistent?

Don’t use count(), use just one literal like 1. Include the rest of the selector as I wrote or you run the risk of getting some noise from other objects coincidentally named ‘remotes’ from other origin or other database.

Check like

If rs.EOF Then // Table does not exist End

Thanks Rick and all of you!

I’m finding that the first instance of the class where this is checked, the following code works - the Record set is not nil. However subsequent instances the recordset is always Nil causing an error. There should not be an Nil instance of the recordset ever - I think.

Dim rs As RecordSet
rs= db.SQLSelect( "SELECT 1 FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='public' AND table_catalog='axcys' AND table_name='remotes' ")

If Not rs.EOF Then
// Exists

End If

The code above is performed in a method that is called during construction of the class. Rs is not used anywhere else in the class and should be limited in scope to this method. I do not understand why it is Nil on the second instance?

Any ideas?
Tim

@wayne,
Yes that is consistent. However the issue with Rs as Recordset being Nil still exists.

II must be missing something really simple!
Tim

Are you using the same connection with the. correct permissions? It is not something you’d want every joe user to do.

Rs==Nil is due to some error. You must check If DB.Error(), and watch DB.ErrorMessage to know what’s going on. Maybe a failed DB connection before querying the DB? Check the message.

What about IF TABLE EXISTS in the create SQL Cmd (if PostgreSQL knows these strings…) ?

Again.

What you are refering to has the syntax CREATE TABLE IF NOT EXISTS ... in PostgreSQL: https://www.postgresql.org/docs/current/sql-createtable.html

There is an error of 1.

Strange that it would not exist the second time around?
BTW this is due to another instance of the object being created. Not something a user could or would do.
Tim

this works perfectly fine on any of the postgresql databases I can test with :

SELECT * FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='public'

so may be you have an invisible character in “information_schema.tables” ?

It works the first time, but not when the second instance of the object is called.

If I run the command in pgAdminIII, it works everytime too. This is why I am wondering if it is something in Xojo or my code elsewhere?

"SELECT 1 FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='public' AND table_catalog='axcys' AND table_name='remotes'

Ok, but you missed the textual part. What the system says when you get the error message from DB.ErrorMessage ?

As far as I know, in Xojo, DB.ErrorCode 1 means “There’s an error, that I don’t know how to explain by codes, read the message”.

Hi Rick,

The text was “no such table: information_schema.tables”
Tim