The information schema is the slow and sure way: it is standardized and largely portable to other databases that support it. And it will keep working across major versions.
However, views in the information schema often join many tables from the system catalogs to meet a strictly standardized format - many of which are just dead freight most of the time. This makes them slow.
The Postgres developers aren't making promises, but basics (like what is needed here) aren't going to change across major versions.
More detailed assessment:
psql
(the native command-line interface) takes the fast lane, of course, and queries the source directly. If you start psql
with the parameter -E
, the SQL behind backslash commands like \d
is displayed. Or \set ECHO_HIDDEN on
from the psql command line. Starting from there you can build an answer to your question.
Given one table, is it possible to have a list of the names of the columns for this table.
SELECT attrelid::regclass AS tbl
, attname AS col
, atttypid::regtype AS datatype
-- more attributes?
FROM pg_attribute
WHERE attrelid = 'myschema.mytable'::regclass -- table name optionally schema-qualified
AND attnum > 0
AND NOT attisdropped
ORDER BY attnum;
Faster than querying information_schema.columns
. Try EXPLAIN ANALYZE
to see for yourself. Still hardly matters for a one-time look-up. But might make a difference if used in a query / function that's repeated many times.
There are also subtle differences in visibility. Detailed comparison: