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.
Best Answer
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:
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:Using
pg_attribute
:format_type()
returns the complete type with all modifiers:Also note that the cast to
regclass
resolves the table name somewhat intelligently according to the currentsearch_path
. It also raises an exception if the name is not valid. Details:Using
information_schema.columns
:The information is standardized, but incomplete:
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
System catalogs
oid
included)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:To get the data type of individual columns, you can also use the function
pg_typeof()
: