How do I list all columns for a specified table

Question

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.

Answer 1

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:

SELECT *
FROM information_schema.columns
WHERE table_schema = your_schema
  AND table_name   = your_table

Answer 2

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. Queries on it are very slow, because relations are implemented as huge views.

Of course, psql takes the fast lane and queries the source directly. If you start psql with the parameter -E, the SQL behind backslash commands like \d is displayed. Starting from there you can come up with an answer to question such as yours:

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;

Answer 3

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.