How do I discover the structure of a PostgreSQL database?

Question

I need to write a PHP script that will output data from a PostgreSQL database that I do not know the structure of. What query will return the names of all tables in a database? And what query will list the names of all columns in a table?

Answer 1

use the ANSI information_schema views

select * from information_schema.tables

select * from information_schema.columns

Answer 2

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'

Answer 3

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

Answer 4

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