I'm looking for a precise piece of information in a database which I have no knowledge about. The database is on a separate machine, but I can log into it, and launch a psql command line, with administrator rights. It's a third-party product, and they are slow to answer questions. I know the data is inside that database, so I want to do a little bit of reverse-engineering. Given a table name, is it possible to get a list of the names of the columns in that table? For example, in SQL Server, it's possible to dump a table into a reusable CREATE statement, which textually lists all the columns the table is composed of.
Stephane Rolland asked Aug 13, 2012 at 12:44 Stephane Rolland Stephane Rolland 8,781 10 10 gold badges 33 33 silver badges 40 40 bronze badgesIn addition to the command line \d+ you already found, you could also use the Information Schema to look up the column data, using information_schema.columns :
SELECT * FROM information_schema.columns WHERE table_schema = 'your_schema' AND table_name = 'your_table' ; Note: As per the example above, make sure the values are enclosed within quotes.
10.9k 20 20 gold badges 61 61 silver badges 95 95 bronze badges answered Aug 13, 2012 at 13:27 7,315 1 1 gold badge 19 19 silver badges 11 11 bronze badges add order by ordinal_position to preserve column order Commented Nov 19, 2021 at 12:34 And table name is case sensitive.. lower case was ok. Commented Jan 24, 2022 at 23:41Just to make make copy paste to psql easier: SELECT column_name FROM information_schema.columns where table_name = 'TABLE_NAME';
Commented Sep 21, 2022 at 11:57As a supplement to the other answers, even a SELECT statement that returns no rows will expose the column names to you and to application code.
select * from table_name where false; I intend this to be executable by almost any client software for almost any DBMS. (Almost? Yes, some don't support a clause like where false . Instead, they require an expression like where 1 = 0 .)
Permissions might come into play with any of these approaches.
88.2k 30 30 gold badges 417 417 silver badges 653 653 bronze badges answered Aug 14, 2012 at 7:13 Mike Sherrill 'Cat Recall' Mike Sherrill 'Cat Recall' 10.8k 1 1 gold badge 36 36 silver badges 43 43 bronze badges Except for the hidden columns, which had to be specified to be selected (like pg_class.oid) Commented Feb 19, 2019 at 15:06Note, for some DBMSes "where (false / or false condition)" clause could be slow as it may scan all table records for condition check, depends on how smart the optimizer is. Alternatively, may consider the rows limit clause.
Commented Jul 12, 2023 at 8:09To get one line of data: COPY (SELECT * FROM table_name WHERE false) TO STDOUT WITH (FORMAT CSV, HEADER);
Commented Aug 21, 2023 at 9:37The 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: