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.