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?

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.