If you need something similar to the DESCRIBE command available in MySQL or Oracle in order to get information about the columns of a table, then you can do something like:

SELECT
  -- Field
  pg_attribute.attname AS "Field",
  -- Type
  CASE pg_type.typname
    WHEN 'int2' THEN 'smallint'
    WHEN 'int4' THEN 'int'
    WHEN 'int8' THEN 'bigint'
    WHEN 'varchar' THEN 'varchar(' || pg_attribute.atttypmod-4 || ')'
    ELSE pg_type.typname
  END AS "Type",
  -- Null
  CASE WHEN pg_attribute.attnotnull THEN ''
    ELSE 'YES'
  END AS "Null",
  -- Default
  CASE pg_type.typname
    WHEN 'varchar' THEN substring(pg_attrdef.adsrc from '^\'(.*)\'.*$')
    ELSE pg_attrdef.adsrc
  END AS "Default"
FROM pg_class
  INNER JOIN pg_attribute
    ON (pg_class.oid=pg_attribute.attrelid)
  INNER JOIN pg_type
    ON (pg_attribute.atttypid=pg_type.oid)
  LEFT JOIN pg_attrdef
    ON (pg_class.oid=pg_attrdef.adrelid AND pg_attribute.attnum=pg_attrdef.adnum)
WHERE pg_class.relname='table_name' AND pg_attribute.attnum>=1 AND NOT pg_attribute.attisdropped
ORDER BY pg_attribute.attnum;

where table_name is the name of the table (relation) whose columns you want to get information about.
However be aware that, unlike the MySQL DESCRIBE command, if there is no table named table_name, then the above SELECT command will simply display 0 row (no error).

Ooops... sorry! The above query gives:
ERROR:  syntax error at or near "(" at character 386

Replace line 18
WHEN 'varchar' THEN substring(pg_attrdef.adsrc from '^'(.*)'.*$')
by
WHEN 'varchar' THEN substring(pg_attrdef.adsrc from '^\'(.*)\'.*$')
and it should work fine.