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,
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.
information_schema vs. system catalogs
We have discussed this many times. The information schema serves certain purposes. System catalogs are the actual source of all information.
The information schema provides standardized views which help with portability – mostly across major Postgres versions as portability across different RDBMS platforms typically is an illusion once your queries are sophisticated enough to look up system catalogs. 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.
For your example
SELECT * from tbl compare the two queries below for this simple table:
CREATE TEMP TABLE foo( a numeric(12,3) , b timestamp(0) );
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 according to the current
search_path. It raises an exception if the name is not valid. See:
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:
character_maximum_length character_octet_length numeric_precision numeric_precision_radix numeric_scale datetime_precision interval_type interval_precision
List of pros & cons
The biggest pros (IMO) in bold.
Information schema views
- often simpler (depends)
- 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
- often more complex (depends), closer to the source
- complete (system columns like
- 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
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:
CREATE TEMP TABLE tmp123 AS SELECT 1::numeric, now() LIMIT 0;
To get the data type of individual columns, you can also use the function
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 and array_upper( conkey, 1 ) = 1, pg_class, pg_namespace 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.
information_schema views, they’re SQL-standard and contain the information you want.
You can also directly access
pg_attribute, etc, but that’s unportable and often fiddlier; you may need helper functions like
pg_get_function_arguments, etc for some things.
If you want to see how
psql executes something like
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.