Can't see tables in PostgreSQL

Question

I've created a schema in my PostgreSQL database that Hibernate uses, creating tables, persisting entities and so on. After I updated my Ubuntu 14.04 to 16.04, I can't see the tables anymore. The applications that manipulate data in the schema and its tables can still do it, though.

Here is the output of some commands after I login using sudo -u postgres psql:

postgres=# \dt
No relations found.
postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 jcat   | postgres
 public | postgres
(2 rows)

postgres=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 jcat   | postgres | postgres=UC/postgres+| 
        |          | =UC/postgres         | 
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
(2 rows)

I've tried the following, from a few questions I aswers I found:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA jcat TO public;
grant ALL on SCHEMA jcat to public;

but I still can't see my tables. Any hints?

Edit: here are the information requested.

postgres-# \d jcat.*
Did not find any relation named "jcat.*".
postgres-# show search_path
postgres-#

What does show search_path give you? What does \d jcat.* give you? — user330315
– user330315, Commented May 31, 2016 at 10:25
You need to end SQL statements with a ;. Your show search_path did not actually run. But if \d jcat.* results in "did not find any relation" it means there are not tables in that schema. What do you get if you run select table_name from information_schema.tables where table_schema = 'jcat'; — user330315
– user330315, Commented May 31, 2016 at 10:48
show search_path; shows 1 row: "$user", public. The select statement you just requested returns 0 rows. — Douglas De Rizzo Meneghetti
– Douglas De Rizzo Meneghetti, Commented May 31, 2016 at 10:53

adavea · Accepted Answer · 2019-12-24 16:50:58Z

1

I suspect you are manually connecting to a different database than your ORM or code is connecting to (or at least I was running into the same problem as you and it turns out that's what I was doing).

It can help clear up a lot of confusion if you use a good GUI tool to look around in your database to help you figure out where your tables are actually ending up. PGAdmin4 was my tool of choice, might be worth a shot: https://www.pgadmin.org/download/pgadmin-4-windows/

You can also try setting your search path from the command line when connecting via psql. On Ubuntu that looks like this:

PGOPTIONS='-c search_path=jcat' psql -h your.host -U youruser

edited Dec 24, 2019 at 16:50

answered Dec 24, 2019 at 16:45

adavea

1,6221 gold badge20 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zhb.hodas · Accepted Answer · 2022-04-20 12:13:53Z

1

I had a similar problem. I was running Postgresql 12 in a docker container and using volume with data. When accessing the database to show a list of tables, an error occurred:

"ERROR: column c.relhasoids does not exist at character 190".

Data for DB was from Postgresql 11. When I rebuilt the dockerfile with image Postgresql 11 everything was OK

answered Apr 20, 2022 at 12:13

zhb.hodas

212 bronze badges

Collectives™ on Stack Overflow

Can't see tables in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related