postgres system catalog query columns from table

Question

I am building an integration test against an app that provisions a database for a user. The User created for it is not a super user, and does not have access to schema_information.tables because when I try the following script:

SELECT table_name
FROM information_schema.tables
WHERE table_schema='{schema}'

I get a return of 0, as I should since this user does not have permission.

I am trying to query the database to verify the tables and the columns created. I can get the list of table names through the system catalog with the following script:

SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = '{schema}'

And this outputs the table names the way I want it:

business, location, person, etc...

I can't find the script with system catalog to then find the column names (And as a bonus, the data type) of each table. So far I've tried the following:

SELECT attname, format_type(atttypid, atttypmod) AS type
FROM pg_attribute
WHERE attrelid = 'business'

and here is the error:

ERROR: invalid input syntax for type oid: "business"
LINE 1: ...od) AS type FROM pg_attribute WHERE attrelid = 'business'
 ^```

Also tried:

SELECT
 a.attname as "Column",
 pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
FROM
 pg_catalog.pg_attribute a
WHERE
 a.attnum > 0
 AND NOT a.attisdropped
 AND a.attrelid = (
 SELECT c.oid
 FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relname ~ '**Dont know what to put here, Schema? Database?**'
 --AND pg_catalog.pg_table_is_visible(c.oid)
 );

and this returns 0 with a schema or database. I'm not sure what to put there for c.relname. Am I also seeing 0 with this because a basic user just cannot see deeper than tables in a schema, period?

• András Váczi: You want to use 'business'::regclass, I believe.
• as.beaulieu: I also tried using ::regclass, and it delivered the same error as above.

Answer 1

Just select from information_schema.columns instead.

SELECT table_catalog, table_schema, table_name, data_type
FROM information_schema.tables
WHERE table_schema='{schema}';

FOR GLORY

For whatever reason, if you can't query information_schema.columns (which would otherwise indicate something is funky to me). We can reverse engineer the catalogs efficiently with psql -E with a user that can. Then run the appropriate client (\) command in psql that shows you what you want, like \d schema. And copy out the parts of the exported query you need..

For me the final column in that is something like

SELECT a.attname,
 pg_catalog.format_type(a.atttypid, a.atttypmod),
 (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
 FROM pg_catalog.pg_attrdef d
 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
 a.attnotnull, a.attnum,
 (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
 WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
 NULL AS indexdef,
 NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '1024334' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

Now I just need to get all the attrelid's for the schema. Running a quick \d asdofkodskf I see,

SELECT c.oid,
 n.nspname,
 c.relname
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(asdofkodskf)$'
 AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

We actually want n.nspname not c.relname

So given my version of psql, the official method of querying the catalog directly would be...

SELECT c.oid,
 n.nspname,
 c.relname, t.*
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (
 SELECT a.attname,
 pg_catalog.format_type(a.atttypid, a.atttypmod),
 (
 SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
 FROM pg_catalog.pg_attrdef d
 WHERE d.adrelid = a.attrelid
 AND d.adnum = a.attnum
 AND a.atthasdef
 ),
 a.attnotnull, a.attnum,
 (
 SELECT c.collname
 FROM
 pg_catalog.pg_collation c,
 pg_catalog.pg_type t
 WHERE c.oid = a.attcollation
 AND t.oid = a.atttypid
 AND a.attcollation <> t.typcollation
 ) AS attcollation
 FROM pg_catalog.pg_attribute a
 WHERE a.attrelid = c.oid
 AND a.attnum > 0
 AND NOT a.attisdropped
) AS t
WHERE n.nspname ~ '^(public)$' -- YOUR SCHEMA HERE
AND pg_catalog.pg_table_is_visible(c.oid);

Excerpt (without all the columns or all the rows) (relname is table, attname is column)

 oid | nspname | relname | attname | format_type 
llation 
---------+---------+-----------------------------+----------------------+-------------------------
--------
 1024242 | public | spatial_ref_sys_pkey | srid | integer 
 1045853 | public | product_discount_qty_excl | qty | int4range 
 1024334 | public | valid_detail | valid | boolean 
 1024334 | public | valid_detail | reason | character varying 
 1024334 | public | valid_detail | location | geometry 
 1045847 | public | product_discount | pid | integer 
 1045847 | public | product_discount | qty | int4range 
 1045847 | public | product_discount | percent_modifier | real 
 1024569 | public | geography_columns | f_table_catalog | name