PostgreSQL how to check if table exist?

  1. 4 months ago

    Tim S

    May 13 Pre-Release 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

    May 13 Pre-Release Testers, Xojo Pro New Zealand axisdirect.nz

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

  3. Wayne G

    May 13 Pre-Release Testers, Xojo Pro New Zealand axisdirect.nz
    Edited 4 months ago

    Alternatively use TableSchema to get a list of tables.

  4. James D

    May 13 Pre-Release Testers, Xojo Pro Europe (Switzerland)

    Have you tried the TableSchema method?

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

  5. Rick A

    May 13 Pre-Release Testers (Brazil. GMT-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

    May 13 Pre-Release 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

    May 13 Pre-Release Testers, Xojo Pro New Zealand axisdirect.nz
    Edited 4 months ago
    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

    May 13 Pre-Release Testers (Brazil. GMT-3:00)
    Edited 4 months ago

    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

    May 13 Pre-Release 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

    May 13 Pre-Release 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

    May 14 Pre-Release 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

    May 14 Pre-Release Testers (Brazil. GMT-3:00)
    Edited 4 months ago

    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

    May 14 Europe (France, Strasbourg)

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

  14. Rick A

    May 14 Pre-Release Testers (Brazil. GMT-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

    May 14 Pre-Release 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

    May 14 Pre-Release 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

    May 14 Pre-Release 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

    May 14 Pre-Release 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

    May 14 Pre-Release Testers (Brazil. GMT-3:00)
    Edited 4 months ago

    @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!