2

We have newly created few tables and schema(with tables under it). We used to fetch the table+ columns info by querying Information_schema.columns table. But the newly created tables and schema are not showing up while querying Information_schema.columns table. Any idea why?

Also I did some research and found a table - pg_tables, under which the newly created tables are listed. But couldn't get the column info here. Could it be joined with any other child table to fetch the column info?

Thanks!

Vicky
  • 115
  • 1
  • 10
  • In order to see data in in `information_schema.*` you need to query them as a user with permissions for those objects. In other words if the tables where created as one user and you are querying the `Information_schema.columns` as a different user that does not have permissions for the tables(and columns) you won't see anything. Other possibility is that the schema and table creation was done in a transaction that was not committed and so the new objects did not last past the session. – Adrian Klaver Feb 09 '21 at 16:17
  • @a_horse_with_no_name No.. the tables are not listed under `information_schema.tables` as well.. – Vicky Feb 10 '21 at 11:08
  • @AdrianKlaver The table was created by DBA as per our request..We were able to see the table and its properties in Pgadmin, but its not fetched while querying `information_schema.*` tables.. We're also unable to query the table, it says `permission denied for table`.. So, should we request for some specific access to DBA? – Vicky Feb 10 '21 at 11:15
  • @a_horse_with_no_name ohh..so is it related to some access issue of using developers id? should we request for specific access from DBA? – Vicky Feb 10 '21 at 11:18
  • If you don't have permissions for the table then you will not be able to see it's information in the information_schema. The options are: 1) get permissions on the table. 2) Use the system catalogs instead(what pgAdmin is doing). For your case [pg_attribute](https://www.postgresql.org/docs/12/catalog-pg-attribute.html) – Adrian Klaver Feb 10 '21 at 15:56
  • @a_horse_with_no_name. It is believable, the information_schema.* views have a permissions check component to them. Look at `\d+ information_schema.columns`. – Adrian Klaver Feb 10 '21 at 15:59
  • @AdrianKlaver: I understood the "newly created tables" that right after running a CREATE TABLE statement the table isn't visible - which would implicate that the current user is also the owner and thus should be able to see the columns or columns. –  Feb 10 '21 at 17:16
  • Thankyou guys for your valuable inputs! DBA informed he could access the tables by using our id, but no developers could access. So they're still working on it, will share the fix once its resolved! – Vicky Feb 11 '21 at 12:06

0 Answers0