postgres

SELECT
  column_name,
  pgd.description as column_description,
  data_type,
  CASE
    WHEN pk.constraint_name IS NOT NULL THEN 'PRIMARY KEY'
    WHEN fk.constraint_name IS NOT NULL THEN 'FOREIGN KEY'
    WHEN chk.constraint_name IS NOT NULL THEN 'CHECK'
    WHEN idx.index_name IS NOT NULL THEN 'INDEX'
    ELSE ''
  END as constraint_type
FROM 
  information_schema.columns col
  LEFT OUTER JOIN pg_catalog.pg_description pgd ON (
    col.table_catalog = pgd.objsubid AND
    col.table_name = pgd.objname AND
    col.ordinal_position = pgd.objsubid
  )
  LEFT OUTER JOIN information_schema.key_column_usage AS pk ON (
    col.table_schema = pk.table_schema AND
    col.table_name = pk.table_name AND
    col.column_name = pk.column_name AND
    pk.constraint_name LIKE 'pk_%'
  )
  LEFT OUTER JOIN information_schema.key_column_usage AS fk ON (
    col.table_schema = fk.table_schema AND
    col.table_name = fk.table_name AND
    col.column_name = fk.column_name AND
    fk.constraint_name LIKE 'fk_%'
  )
  LEFT OUTER JOIN information_schema.check_constraints AS chk ON (
    col.table_schema = chk.table_schema AND
    col.table_name = chk.table_name AND
    col.column_name = chk.column_name AND
    chk.constraint_name LIKE 'chk_%'
  )
  LEFT OUTER JOIN (
    SELECT DISTINCT idx.tablename, idx.attname, idx.indexname AS index_name
    FROM pg_indexes idx
    WHERE idx.indexname LIKE 'idx_%'
  ) AS idx ON (
    col.table_name = idx.tablename AND
    col.column_name = idx.attname
  )
WHERE 
  col.table_name = 'your_table_name';
Replace 'your_table_name' with the name of the table you want to describe.

The query joins the information_schema.columns table with various other tables to get additional information about the columns.

The pg_catalog.pg_description table contains column descriptions, so we join it on the table name, column name, and position within the table.

The information_schema.key_column_usage table contains information about primary key and foreign key constraints. We join it twice, once to find primary keys (pk.constraint_name LIKE 'pk_%') and once to find foreign keys (fk.constraint_name LIKE 'fk_%').

The information_schema.check_constraints table contains information about check constraints. We join it on the table name, column name, and constraint name (chk.constraint_name LIKE 'chk_%').

The pg_indexes table contains information about indexes. We join it to get the name of any index that includes the column (idx.indexname LIKE 'idx_%').

Finally, the query selects the column name, column description, data type, and constraint type (which is determined based on which of the four tables has a matching row).

This query should give you a comprehensive description of the columns in your table.