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.
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!
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.
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?
@Emile S What about
IF TABLE EXISTSin the create SQL Cmd (if PostgreSQL knows these strings…) ?
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.
no such table: information_schema.tables
error code = 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.
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'
@Tim S There is an error of 1.
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".