By Stephane Rolland


2012-08-13 12:44:44 8 Comments

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.

5 comments

@oᴉɹǝɥɔ 2019-09-04 19:26:14

PostgreSQL only

This is somewhat hokey but could be a contender if you are looking for the shortest possible SQL:

SELECT json_object_keys(to_json(json_populate_record(NULL::schema_name.table_name, '{}'::JSON)))

or even shorter (assuming there is at least one row present in the table)

SELECT json_object_keys(to_json((SELECT t FROM schema_name.table_name t LIMIT 1)))

The listing preserves the order. In case you don't care about the order and have hstore extension installed you can do even shorter

SELECT skeys(hstore(NULL::schema_name.table_name))

@bhamby 2012-08-13 13:27:53

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

Note: As per the example above, make sure the values are enclosed within quotes.

@anishpatel 2017-09-07 22:46:06

In psql, use \x on or \pset expanded on to make the query results linear (instead of tabular) and thus easier to read stackoverflow.com/a/9605093/513397

@Thalis K. 2017-10-23 18:30:47

In current versions (tried it in 9.6) you can do in psql \d+ public.* to get the description (schema + indeces/fkeys/triggers) of all your tables and views in the public schema. We use it in our pre-commit hook script to keep track in git of changes in the DB made by each commit.

@Andrew 2018-02-22 16:56:14

SELECT column_name to get only column name

@Evan Carroll 2018-07-06 19:08:40

psql on PostgreSQL 11+

If you're looking for the column types on a query, you can use psql's \gdesc

SELECT
    NULL AS zero,
    1 AS one,
    2.0 AS two,
    'three' AS three,
    $1 AS four,
    sin($2) as five,
    'foo'::varchar(4) as six,
    CURRENT_DATE AS now
\gdesc
 Column |         Type         
--------+----------------------
 zero   | text
 one    | integer
 two    | numeric
 three  | text
 four   | text
 five   | double precision
 six    | character varying(4)
 now    | date
(8 rows)

@Erwin Brandstetter 2012-08-13 23:05:19

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 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 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:

@Evan Carroll 2016-12-02 20:31:40

Really like that you show you -E and show people how to get the sql of psql.

@Mike Sherrill 'Cat Recall' 2012-08-14 07:13:19

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.

@JohnMudd 2017-03-09 19:37:28

I presume you mean to pass this SQL to the psql command. I suggest using the --no-psqlrc option in that case to avoid surprises in the output.

@okutane 2019-02-19 15:06:45

Except for the hidden columns, which had to be specified to be selected (like pg_class.oid)

@timkofu 2019-10-11 09:49:46

Nice and simple. Thank you.

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] postgres system catalog query columns from table

2 Answered Questions

[SOLVED] PostgreSQL: How to list all stored functions that access specific table

3 Answered Questions

[SOLVED] How can I list all tables without a primary key?

1 Answered Questions

2 Answered Questions

[SOLVED] How can I list all user-owned functions?

2 Answered Questions

[SOLVED] How to list all table columns in sorted order?

  • 2014-01-20 12:45:15
  • Oxfist
  • 9519 View
  • 7 Score
  • 2 Answer
  • Tags:   postgresql table

1 Answered Questions

Sponsored Content