How do I discover the structure of a PostgreSQL database?

SELECT table_name 
    FROM information_schema.tables 
WHERE table_type = 'BASE TABLE' 
    AND table_schema NOT IN 
        ('pg_catalog', 'information_schema'); 

SELECT column_name 
    FROM information_schema.columns 
WHERE table_name = 'YourTablesName'; 

This page has some great information on retrieving information from information_schema: http://www.alberton.info/postgresql_meta_info.html

use the ANSI information_schema views

select * from information_schema.tables

select * from information_schema.columns

If you have access to psql, you can use \d and \d table. In terms of SQL, first is equivalent to

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'

second

SELECT column_name FROM information_schema.columns WHERE table_name ='table'

psql has the -E, --echo-hidden option, which will print out all queries made by psql commands.

For example:

psql -h localhost -U postgres -p 5432 postgres -E

postgres=# \d
******** QUERY *********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

Don't write it yourself. Use phpPgAdmin - it will be much easier, faster and less error prone.