Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm looking for a precise piece of information in a database which I have no knowledge about.

It's a 3rd party product, they are slow on answering some questions, and I know the data is lying inside that db, so I want to do a little of retro engineering.

Given one table, is it possible to have a list of the names of the columns for this table ?

For example in SqlServer, it's possible to dump a table into a reusable CREATE statements, that textually lists all the columns the table is composed of.

share|improve this question
What sort of access do you have to the DB? – dezso Aug 13 '12 at 12:49
@dezso, it is on a seperate machine, but I can log into it, and launch psql command line, with administrator rights – Stephane Rolland Aug 13 '12 at 12:54
If I understand you correctly, you are after \dt[+] table_name in psql. – dezso Aug 13 '12 at 13:03
nope. \dt+ doesn't seem to explicitely display the columns name. it only adds a "Description" field. – Stephane Rolland Aug 13 '12 at 13:16
but \d+ table name works ! – Stephane Rolland Aug 13 '12 at 13:18
up vote 103 down vote accepted

In addition to the command line \d+ <table_name> you already found, you could also use the Information Schema to look up the column data, using information_schema.columns:

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.

share|improve this answer

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 in 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 very slow.
The Postgres developers aren't making promises, but basics (like what is needed here) aren't going to change across major versions.

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, attnum, attname
FROM   pg_attribute
WHERE  attrelid = 'myschema.mytable'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

Much faster than querying information_schema.columns. Try EXPLAIN ANALYZE to see for yourself.
There are also subtle differences in visibility. Detailed comparison:

share|improve this answer

As 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;

Permissions might come into play with any of these approaches.

share|improve this answer

protected by Paul White Feb 9 at 5:50

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.