Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2006
    Posts
    6

    Red face Unanswered: Can't "see" tables - PostgreSQL 8.1.4, Windows XP

    Greetings,

    I've installed postgreSQL 8.1.4 on my Windows XP Home Edition PC.

    I then used the pgAdmin III UI to setup a very simple database.

    However, when I try and access the tables I created via the psql command line ("\dt") I get the message:
    "No relations found."

    I am connected to the correct database, and I can see schema under which the tables were created, but I can't see the tables.

    When I check the access privileges via "\z" (or "\dp") I get:-
    Access privileges for database "<database>"
    Schema | Name | Type | Access privileges
    --------+------+------+-------------------
    (0 rows)

    I granted ALL rights to the Database, Tables, and Schema in question to both public and postgres users.

    I tried all granting access rights in the "Privileges for Schemas" window (from pgAdmin) to public and got this error:-
    "ERROR: syntax error at or near "any" at character 5486"

    Any suggestions would be very welcome?

    Thanks in advance.

  2. #2
    Join Date
    Sep 2006
    Posts
    6
    I've just tried creating a view to one of the tables in pgAdmin, and I get the message:-
    "ERROR: relation "school" does not exist"

    "school" is one of the tables I've created.

    So, it would seem that even within pgAdmin those tables have visibility issues.

  3. #3
    Join Date
    Sep 2006
    Posts
    6
    I think I may have stumbled onto something.

    The tables in question were created within a schema I created called "Draw".

    However, when I create a table within psql, it automatically assigns the new table to the "public" schema, which I have no problems seeing or creating views for.

    I'll recreate my simple database again, but this time under the "public" schema to see if that "fixes" things.

  4. #4
    Join Date
    Sep 2006
    Posts
    6

    Talking

    Right, using the "public" schema instead of my own custom-created one did the trick.

    All good now.

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    In the future, if you have questions re given database objects, try the information_schema views.

    Select * from information_schema.tables

    for instance, will return a list of all tables as well as other data.

    See the post in the PostgreSQL FAQ regarding he information schema
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by etnewbie
    Right, using the "public" schema instead of my own custom-created one did the trick.
    You can still keep the tables in your own schema if you want to. All you have to do is to set the "search path" that Postgres uses to find the tables: e.g.
    SET search_path TO my_schema, public;
    for details see:
    http://www.postgresql.org/docs/8.1/static/sql-set.html

  7. #7
    Join Date
    Sep 2006
    Posts
    6
    Thanks for the extra info guys.

    However, I've just created another custom schema (named "draw") which works fine - no visibility issues whatsoever.

    Investigating further...

  8. #8
    Join Date
    Sep 2006
    Posts
    6
    Ah - I see where I've gone wrong now.

    My initial custom schema had an uppercase character in it, so I needed to quote the schema name when referencing it:-

    select * from "Draw".entry;

    works, as where:-

    select * from Draw.entry;

    doesn't, because Draw is interpreted as draw.

    I think I'll just stick to lowercase for everything for now.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by etnewbie
    I think I'll just stick to lowercase for everything for now.
    Yes, avoid doublequotes by any means!

Posting Permissions

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