56

I have created a database in postgres. It has 3 empty tables. The table has user tom as its Superuser along with root. I am logged in as tom and connected to mydb database. But still the commands \d or \dt - gives no relations found.

Is there any alternate to SHOW TABLE in postgresql?

Could not find solution here

I get results from - \dt *.*.

Community
  • 1
  • 1
Sp1
  • 699
  • 2
  • 10
  • 17
  • 19
    If `\dt *.*` works but `\dt` doesn't, then the tables' schema is probably not in your [`search_path`](https://www.postgresql.org/docs/9.1/static/runtime-config-client.html#GUC-SEARCH-PATH) – Nick Barnes Nov 29 '16 at 12:56
  • http://stackoverflow.com/questions/2951875/postgresql-how-do-i-set-the-search-path-at-the-user-level – Sp1 Nov 29 '16 at 14:17

7 Answers7

37

It is not a problem with your search_path, it could be an issue with your schema permissions as described in the answer here. Check with \dn+ that the public schema indicates permissions for the postgres role, and if not, grant them with: GRANT ALL ON SCHEMA public TO public;

Pav K.
  • 2,548
  • 2
  • 19
  • 29
HorsePunchKid
  • 848
  • 12
  • 17
17

I solved my problem by using double quote e.g \d "Table_name". Because my table name is capitalized like Foo, Bar. Hope that could help someones.

BrianLe
  • 212
  • 3
  • 8
12

You might not be connected to the right database.

The first command \c DATABASE_NAME, coming from the following comment on Reddit, did the trick for me.

Make sure you're connected to the correct database with \c . Using \l will list all databases regardless of which database you're connected to, but most commands are specific to the connected database.

Use \dt .* to list all tables with name matching in all schemas.

Assuming the table exists, it will tell you what schema it's in. Then you can query with SELECT * FROM .;.

If you want to avoid having to explicitly specify the schema name, make sure the schema that contains the table is in your path: SHOW search_path and SET search_path .

s3c
  • 1,481
  • 19
  • 28
2

I had the exact same issue. None of the answers above helped. I was able to see my table when I ran \dt .*. Then I realized I had to call explicitly state its schema when running SELECT statements.

So, if you run SELECT * FROM public.<your_tablename_goes_here>; it should work. I hope this helps!

Hazar Panc
  • 35
  • 7
0

If you have different instances of postgres running on server on different port it helps when you connect to database with specific port psql -p 5432 databasename( or psql -p 5433 databasename etc.)

Bahtiyar
  • 1
0

you must have not added a semicolon at the end that's what always happens to me I always forget my semicolon -> create table nameoftable()

Triangle
  • 11
  • 1
0

This happened for me when I removed the default privileges for PUBLIC on the public schema (specifically the USAGE privilege).

Mike Conigliaro
  • 1,144
  • 1
  • 13
  • 28