postgres system catalog query columns from table

5 votes

1 answer

16928 views

postgresql information-schema system-tables catalogs

                          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?
                        

Asked by as.beaulieu (237 rep)

Jan 18, 2018, 06:58 PM
Last activity: Jan 22, 2020, 06:14 AM

postgres system catalog query columns from table

Related Questions