Query to return output column names and data types of a query, table or view

  • Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view?

    E.g., a solution if applied to simple SELECT query like SELECT * from person should return a list like:

    Column Name   | Column Type
    First Name    | character
    Last Name     | character
    Age           | integer
    Date of Birth | date

    I have looked up the information_schema views described in an answer below and it seems to cover tables quite well, and I suspect it covers views as well but I haven't checked that yet.

    The last is any arbitrary but valid SELECT query eg involving, JOINS, UNIONS etc, on the database. Is there a built-in procedure, or other stored procedure or script that can return the same for any valid QUERY at all?

    I am developing a program that creates data and querying forms and the information is needed for data validation and executing functions on the returned data.

    There is no single "command", obviously, but there are various ways to retrieve information from the system catalogs. Please ask a **specific question**, add an example and what you expect in return, and give us an idea about the intention behind it.

    The reason for the simplicity is that as far as clients are concerned queries ie `SELECT` queries, ie non data defining or data manipulation queries, whether on tables, views, or other queries return rows and columns of data, so PostgreSQL should be able to return a list of the column names and their data types. The `information_schema` views mentioned below in the answers appears to answer it for tables and views. Arbitrary SELECT queries are the final frontier. I will edit the answer to explain better

  • Information schema vs. system catalogs

    We have had discussions about this many times. The information schema serves certain purposes. If you know your way around the system catalogs, those serve most purposes better, IMO. The system catalogs are the actual source of all information.

    The information schema provides standardized views which help with portability, mostly across major Postgres versions, because portability across different RDBMS platforms typically is an illusion once your queries are sophisticated enough to need to look up system catalogs. And, notably, Oracle still doesn't support the information schema.

    Views in the information schema must jump through many hoops to achieve a format complying to the standard. This makes them slow, sometimes very slow. Compare plans and performance for these basic objects:

    EXPLAIN ANALYZE SELECT * from information_schema.columns;
    EXPLAIN ANALYZE SELECT * from pg_catalog.pg_attribute;

    The difference is remarkable. It really depends on what you are looking for.

    Your example

    For your example SELECT * from tbl, compare the two queries below for this simple table:

       A numeric(12,3)
     , b timestamp(0)

    Using pg_attribute:

    SELECT attname, format_type(atttypid, atttypmod) AS type
    FROM   pg_attribute
    WHERE  attrelid = 'foo'::regclass
    AND    attnum > 0
    AND    NOT attisdropped
    ORDER  BY attnum;

    format_type() returns the complete type with all modifiers:

    attname | type
    a       | numeric(12,3)
    b       | timestamp(0) without time zone

    Also note that the cast to regclass resolves the table name somewhat intelligently according to the current search_path. It also raises an exception if the name is not valid. Details:

    Using information_schema.columns:

    SELECT column_name, data_type
    FROM   information_schema.columns
    WHERE  table_name = 'foo'
    ORDER  BY ordinal_position;

    The information is standardized, but incomplete:

    column_name | data_type
    a           | numeric
    b           | timestamp without time zone

    To get full information for the data type you need to consider all of these columns additionally:


    Related answers:

    A list of pros & cons, the biggest pros (IMO) in bold:

    Information schema views

    • often simpler (depends)
    • slow
    • preprocessed, which may or may not suit your needs
    • selective (users only see objects they have privileges for)
    • conforming to an SQL standard (that's implemented by some of the major RDBMS)
    • mostly portable across major Postgres versions
    • do not require much specific knowledge about Postgres
    • identifiers are descriptive, long and sometimes awkward

    System catalogs

    • often more complex (depends), closer to the source
    • fast
    • complete (system columns like oid included)
    • not complying to an SQL standard
    • less portable across major Postgres versions (but basics aren't going to change)
    • require more specific knowledge about Postgres
    • identifiers are terse, less descriptive but conveniently short

    Arbitrary query

    To get the same list of column names and types from a query, you could use a simple trick: CREATE a temporary table from the query output, then use the same techniques as above.

    You can append LIMIT 0, since you do not need actual data:

    SELECT 1::numeric, now()
    LIMIT  0;

    To get the data type of individual columns, you can also use the function pg_typeof():

    SELECT pg_typeof(1);

    Thank you so much. I've been looking at how to get the datatype of the column in pg_attribute for awhile and just came across this post. Appreciate your post.

    This is helpful in general, but doesn't answer the original question about how to get information about data types of the columns a SELECT statement delivers. It was _not_ about the columns of views or tables, which of course reside in the system catalogs and are shown in the information schema as well.

    I appreciate this -- it worked for me! I tried several solutions elsewhere that didn't work.

  • You can use the psql command line client.

    \dt will show a list of tables

    \dv will show a list of views

    \d [object_name] will describe the schema of the table or view

    Not sure how you would describe a query though.

    More info: https://manikandanmv.wordpress.com/tag/basic-psql-commands/

  • If you have accesss to the pg_catalog and use PgAdmin3, I highly recommend a solution that I found on Valentine's Tech blog (http://tech.valgog.com/2011/02/pgadmin-iii-macros-get-table-fields.html). It is a PgAdmin3 macro that can be accessed with a shortcut to display the definition of a selected table name.

    select quote_ident(nspname) || '.' || quote_ident(relname) as table_name, 
           quote_ident(attname) as field_name, 
           format_type(atttypid,atttypmod) as field_type, 
           case when attnotnull then ' NOT NULL' else '' end as null_constraint,
           case when atthasdef then 'DEFAULT ' || 
                                    ( select pg_get_expr(adbin, attrelid) 
                                        from pg_attrdef 
                                       where adrelid = attrelid and adnum = attnum )::text else ''
           end as dafault_value,
           case when nullif(confrelid, 0) is not null
                then confrelid::regclass::text || '( ' || 
                     array_to_string( ARRAY( select quote_ident( fa.attname ) 
                                               from pg_attribute as fa 
                                              where fa.attnum = ANY ( confkey ) 
                                                and fa.attrelid = confrelid
                                              order by fa.attnum 
                                            ), ','
                                     ) || ' )'
                else '' end as references_to
      from pg_attribute 
           left outer join pg_constraint on conrelid = attrelid 
                                        and attnum = conkey[1] 
                                        and array_upper( conkey, 1 ) = 1,
     where pg_class.oid = attrelid
       and pg_namespace.oid = relnamespace
       and pg_class.oid = btrim( '$SELECTION$' )::regclass::oid
       and attnum > 0
       and not attisdropped
     order by attrelid, attnum;

    Works like a charm and extremely useful.

  • Use the information_schema views, they're SQL-standard and contain the information you want.

    You can also directly access pg_class, pg_attribute, etc, but that's unportable and often fiddlier; you may need helper functions like oidvectortypes, pg_get_function_arguments, etc for some things.

    If you want to see how psql executes something like \dt, run psql -E - it'll print the query. However, it's usually better to use the information_schema if it'll meet your needs.

  • This may be overly simple, but pgAdmin4 shows the field types in the output results. The other solutions above are probably more elegant, but when I just need a quick answer, I find pgAdmin4's query GUI works pretty well. Trying to figure out the field type of a calculated field returned by a view or function can be tricky.

    enter image description here

    It's very nice that pgAdmin4 does this, but _how_ does it to it? Can we find out _without_ scanning through all the source code of PgAdmin4?

License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM