PostgreSQL "DESCRIBE TABLE"

Try this (in the psql command-line tool):

\d+ tablename

See the manual for more info.

In addition to the PostgreSQL way (\d 'something' or \dt 'table' or \ds 'sequence' and so on)

The SQL standard way, as shown here:

select column_name, data_type, character_maximum_length, column_default, is_nullablefrom INFORMATION_SCHEMA.COLUMNS where table_name = '<name of table>';

It's supported by many db engines.

If you want to obtain it from query instead of psql, you can query the catalog schema. Here's a complex query that does that:

SELECT      f.attnum AS number,      f.attname AS name,      f.attnum,      f.attnotnull AS notnull,      pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,      CASE          WHEN p.contype = 'p' THEN 't'          ELSE 'f'      END AS primarykey,      CASE          WHEN p.contype = 'u' THEN 't'          ELSE 'f'    END AS uniquekey,    CASE        WHEN p.contype = 'f' THEN g.relname    END AS foreignkey,    CASE        WHEN p.contype = 'f' THEN p.confkey    END AS foreignkey_fieldnum,    CASE        WHEN p.contype = 'f' THEN g.relname    END AS foreignkey,    CASE        WHEN p.contype = 'f' THEN p.conkey    END AS foreignkey_connnum,    CASE        WHEN f.atthasdef = 't' THEN d.adsrc    END AS defaultFROM pg_attribute f      JOIN pg_class c ON c.oid = f.attrelid      JOIN pg_type t ON t.oid = f.atttypid      LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum      LEFT JOIN pg_namespace n ON n.oid = c.relnamespace      LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)      LEFT JOIN pg_class AS g ON p.confrelid = g.oid  WHERE c.relkind = 'r'::char      AND n.nspname = '%s'  -- Replace with Schema name      AND c.relname = '%s'  -- Replace with table name      AND f.attnum > 0 ORDER BY number;

It's pretty complex but it does show you the power and flexibility of the PostgreSQL system catalog and should get you on your way to pg_catalog mastery ;-). Be sure to change out the %s's in the query. The first is Schema and the second is the table name.