Query the schema details of a table in PostgreSQL?

Question

I need to know the column type in PostgreSQL (i.e. varchar(20)). I know that I could probably find this using \d something in psql, but I need it to be done with a select query.
Is this possible in PostgreSQL?

Winson

Answer 1

You can fully describe a table using postgres with the following query:

SELECT
  a.attname as Column,
  pg_catalog.format_type(a.atttypid, a.atttypmod) as Datatype
  FROM
  pg_catalog.pg_attribute a
  WHERE
    a.attnum > 0
  AND NOT a.attisdropped
  AND a.attrelid = (
    SELECT c.oid
    FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relname ~ '^(TABLENAME)$'
   AND pg_catalog.pg_table_is_visible(c.oid)
  )

Tith this you will retrieve column names and data type.

It is also possible to start psql client using the -E option

$ psql -E

And then a simple \d mytable will output the queries used by postgres to describe the table. It work for every psql describe commands.

Josephjosepha

Excellent answer! I ran into another problem, though: #4336965 – Winson

I don't think you need that regex match, c.relname ~ '^(TABLENAME)$' is equivalent to c.relname = 'TABLENAME'. Nice answer though. – Hahnemann

@mu You're right, but I figure he just copy-pasted this from something with regex support :) – Erato

Answer 2

There is a much simpler way in PostgreSQL to get the type of a column.

SELECT pg_typeof(col)::text FROM tbl LIMIT 1

The table must hold at least one row, of course. And you only get the base type without type modifiers (if any). Use the alternative below if you need that, too.
You can use the function for constants as well. The manual on pg_typeof().

For an empty (or any) table you can use query the system catalog pg_attribute to get the full list of columns and their respective type in order:

SELECT attnum, attname AS column, format_type(atttypid, atttypmod) AS type
FROM   pg_attribute
WHERE  attrelid = 'myschema.mytbl'::regclass   -- optionally schema-qualified
AND    NOT attisdropped
AND    attnum > 0
ORDER  BY attnum;

The manual on format_type() and on object identifier types like regclass.

Shears

Looking for "format_type(atttypid, atttypmod)" for a while, as it seems that its same as information_schema.columns.udt_name (or similar and that is perfectly ok for my case). Thank you :). – Description

Answer 3

Yes, look at the information_schema.

Roma