Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Unanswered: No Tables Visible

    For some reason I am unable to view my database tables for my Wiki & I can't understand what I am doing wrong or what I'm missing. I know for sure there's table data on my 'wiki' database but for some reason I don't know how to list it.

    Code:
    psql (8.4.5)
    Type "help" for help.
    
    wiki=# \dt
    No relations found.
    wiki=# \d
    No relations found.
    Am I doing something wrong as I know there's data in there. I believe I'm just not submitting the proper commands to the server. Thanks for any assistance.

  2. #2
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by CarlosinFL View Post
    I know for sure there's table data on my 'wiki' database
    How exactly do you know this?

    But assuming you're correct, any combination of two options are possible:

    1) The application created its tables in a different database than the one you're looking in.
    2) The application created its tables in a different schema than the one you're looking in.

    You can check #1 by looking at the application's database connection parameters.

    After you connect to the correct database, you can check #2 by issuing the "\dn" command, which will list all the schemas in the database.

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    I connected to the database again as my 'super-user' & ran your suggested command:

    Code:
    wiki=# \dn
            List of schemas
            Name        |   Owner   
    --------------------+-----------
     information_schema | postgres
     mediawiki          | mediawiki
     pg_catalog         | postgres
     pg_toast           | postgres
     pg_toast_temp_1    | postgres
     public             | postgres
    (6 rows)
    Now I'm not sure I understand what I'm looking at. I can see my schema called 'mediawiki' but the other listed schema's are not related to the wiki database, correct?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Do you get the same list of schemas when connected with your "normal" user?
    If yes, you should be able to list all tables using the command:

    Code:
    wiki=# \dt mediawiki.*

  5. #5
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Nope - I do get results with your last suggestion...

    Code:
    wiki=# \dt mediawiki.*
                     List of relations
      Schema   |       Name        | Type  |   Owner   
    -----------+-------------------+-------+-----------
     mediawiki | archive           | table | mediawiki
     mediawiki | category          | table | mediawiki
     mediawiki | categorylinks     | table | mediawiki
     mediawiki | change_tag        | table | mediawiki
     mediawiki | external_user     | table | mediawiki
    So I'm trying to understand what exactly is happening. Is the schema name 'mediawiki.*' and the database name 'wiki.public'?

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    So I'm trying to understand what exactly is happening. Is the schema name 'mediawiki.*' and the database name 'wiki.public'?
    The schema is named "mediawiki" and the database is named "wiki"

  7. #7
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by shammat View Post
    The schema is named "mediawiki" and the database is named "wiki"
    I understand the database name is 'wiki' & the table name is 'mediawiki' but on MySQL it's very clear and easy to understand:

    Code:
    mysql> use wiki
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables;
    +----------------------+
    | Tables_in_wiki       |
    +----------------------+
    | dp_archive           | 
    | dp_category          | 
    | dp_categorylinks     | 
    | dp_change_tag        | 
    | dp_externallinks     | 
    | dp_filearchive       |
    As you can see I connect to the database called 'wiki' & then run 'SHOW TABLES' SQL command and I can see the tables clearly. This is not the case when I type '\dt' in PostgreSQL. Why do I get the "No relations available" error when I try to view the tables for the 'wiki' database? So what if they're called 'mediawiki'? Why doesn't it list them when I enter the proper command?
    Last edited by CacheDrive; 12-13-10 at 16:47.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    I understand the database name is 'wiki' & the table name is 'mediawiki'
    The schema name is wiki, not not the table name.
    but on MySQL it's very clear and easy to understand
    Well PostgreSQL is not MySQL.

    Why do I get the "No relations available" error when I try to view the tables for the 'wiki' database? So what if they're called 'mediawiki'? Why doesn't it list them when I enter the proper command?
    Because your application chose to create the tables in the schema mediawiki during installation (it actively created that schema, and then created the tables in that schema). If it had not specified a schema, things would work just as you expect.

    To find out why the application created the tables that way, you need to ask the manufacturer of your application.

    What you can do, to make life easier is to make the mediawiki schema your default schema by changing the user you use to connect to the database:
    Code:
    ALTER USER your_user_name_goes_here SET search_path= 'mediawiki,public';
    COMMIT;
    (I think you need to log out and log in to make that change have an effect).


    I highly recommend you read the PostgreSQL manual regarding the concept of schemas.
    This is something that MySQL does not have (like many other things PostgreSQL offers).
    PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: Schemas

    Don't try to do things the "MySQL way" you won't get far if you assume PostgreSQL works the same as MySQL (or DB2 or Oracle or SQL Server or Informix).

    You have to accept the fact that each DBMS is different and changing from MySQL to PostgreSQL is a big step because MySQL fails to comply with so many standards (wrong literal quoting, wrong object quoting, wrong concatenation operator, ....)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •