PostgreSQL how to check if table exist?

  1. last year

    Tim S

    13 May 2019 Testers, Xojo Pro Phoenix Arizona USA

    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

    @Tim S Hi Rick,

    The text was "no such table: information_schema.tables"
    Tim

    Are you SURE you are running all your queries using a PostgreSQLDatabase object? Aren't you trying it in a wrong Database object like a SQLiteDatabase?

  2. Wayne G

    13 May 2019 Testers, Xojo Pro, MVP Auckland, New Zealand

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

  3. Wayne G

    13 May 2019 Testers, Xojo Pro, MVP Auckland, New Zealand
    Edited last year

    Alternatively use TableSchema to get a list of tables.

  4. James D

    13 May 2019 Testers, Xojo Pro Europe (Switzerland)

    Have you tried the TableSchema method?

    https://docs.xojo.com/Database.TableSchema

  5. Rick A

    13 May 2019 Testers, Xojo Pro (Brazil. UTC-3:00)

    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'

  6. Tim S

    13 May 2019 Testers, Xojo Pro Phoenix Arizona USA

    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

  7. Wayne G

    13 May 2019 Testers, Xojo Pro, MVP Auckland, New Zealand
    Edited last year
    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?

  8. Rick A

    13 May 2019 Testers, Xojo Pro (Brazil. UTC-3:00)
    Edited last year

    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
  9. Tim S

    13 May 2019 Testers, Xojo Pro Phoenix Arizona USA

    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

  10. Tim S

    13 May 2019 Testers, Xojo Pro Phoenix Arizona USA

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

    II must be missing something really simple!
    Tim

  11. James D

    14 May 2019 Testers, Xojo Pro Europe (Switzerland)

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

  12. Rick A

    14 May 2019 Testers, Xojo Pro (Brazil. UTC-3:00)
    Edited last year

    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.

  13. Emile S

    14 May 2019 Europe (France, Strasbourg)

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

  14. Rick A

    14 May 2019 Testers, Xojo Pro (Brazil. UTC-3:00)

    @Rick A 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.

    Again.

  15. Tobias B

    14 May 2019 Testers, Xojo Pro Bern, Switzerland

    @Emile S What about IF TABLE EXISTS in 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

  16. Tim S

    14 May 2019 Testers, Xojo Pro Phoenix Arizona USA

    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.
    Tim

  17. Jean-Yves P

    14 May 2019 Testers, Xojo Pro Europe (France, Besançon)

    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" ?

  18. Tim S

    14 May 2019 Testers, Xojo Pro Phoenix Arizona USA

    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'
  19. Rick A

    14 May 2019 Testers, Xojo Pro (Brazil. UTC-3:00)
    Edited last year

    @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".

  20. Newer ›

or Sign Up to reply!