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
I know this is 5 years after the thread was started, but I'd like to add a slight variation to the solutions proposed so far, in case it helps someone else (ultimately, this is what I had to come up with).
The potential problem with the above, is that if implemented naively against a database with hundreds of tables and thousands of fields, a developer might first query the set of tables, and then inside a loop, query all fields for each table. That hammers the database server. I know nobody specifically suggested using a loop, but nobody warned against it either. And frankly, it's sort of implicit in the way the answers are structured, in that they effectively say "first query all tables, next query all fields". Translated to code, that process can't really be anything other than a loop.
A better way (imo) to accomplish the original question is to run a query like:
SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE table_schema in ('a', 'b', 'c', 'd')
where a,b,c,d, ... are the schemas with tables that you care to articulate.
This provides you a dataset that isn't normalized, but that doesn't really matter since you're consuming it with an app anyway - it's trivial to parse the result at the app layer the way you need, and you've only hit the database server with one super lightweight query, instead of possibly hundreds inside a loop.
Anyway, hope that helps someone!
The database query tool psql, part of the PostgreSQL distribution, provides table description functionality.
# psql postgres postgres
psql (9.1.0)
Type "help" for help.
postgres=# -- list all tables:
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | my_table | table | postgres
public | my_table2 | table | postgres
(2 rows)
postgres=# -- describe table:
postgres=# \d my_table
Table "public.my_table"
Column | Type | Modifiers
--------+---------+-----------
col1 | integer |
col2 | text |
The rest of the psql commands you can get with \?:
postgres=# \?
General
\copyright show PostgreSQL usage and distribution terms
\g [FILE] or ; execute query (and send results to file or |pipe)
\h [NAME] help on syntax of SQL commands, * for all commands
\q quit psql
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
Input/Output
\copy ... perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard output
\i FILE execute commands from file
\o [FILE] send all query results to file or |pipe
\qecho [STRING] write string to query output stream (see \o)
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\db[+] [PATTERN] list tablespaces
\dc[S] [PATTERN] list conversions
\dC [PATTERN] list casts
\dd[S] [PATTERN] show comments on objects
\ddp [PATTERN] list default privileges
\dD[S] [PATTERN] list domains
\det[+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions
\dF[+] [PATTERN] list text search configurations
\dFd[+] [PATTERN] list text search dictionaries
\dFp[+] [PATTERN] list text search parsers
\dFt[+] [PATTERN] list text search templates
\dg[+] [PATTERN] list roles
\di[S+] [PATTERN] list indexes
\dl list large objects, same as \lo_list
\dL[S+] [PATTERN] list procedural languages
\dn[S+] [PATTERN] list schemas
\do[S] [PATTERN] list operators
\dO[S+] [PATTERN] list collations
\dp [PATTERN] list table, view, and sequence access privileges
\drds [PATRN1 [PATRN2]] list per-database role settings
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\du[+] [PATTERN] list roles
\dv[S+] [PATTERN] list views
\dE[S+] [PATTERN] list foreign tables
\dx[+] [PATTERN] list extensions
\l[+] list all databases
\sf[+] FUNCNAME show a function's definition
\z [PATTERN] same as \dp
Formatting
\a toggle between unaligned and aligned output mode
\C [STRING] set table title, or unset if none
\f [STRING] show or set field separator for unaligned query output
\H toggle HTML output mode (currently off)
\pset NAME [VALUE] set table output option
(NAME := {format|border|expanded|fieldsep|footer|null|
numericlocale|recordsep|tuples_only|title|tableattr|pager})
\t [on|off] show only rows (currently off)
\T [STRING] set HTML <table> tag attributes, or unset if none
\x [on|off] toggle expanded output (currently off)
Connection
\c[onnect] [DBNAME|- USER|- HOST|- PORT|-]
connect to new database (currently "postgres")
\encoding [ENCODING] show or set client encoding
\password [USERNAME] securely change the password for a user
\conninfo display information about current connection
Operating System
\cd [DIR] change the current working directory
\timing [on|off] toggle timing of commands (currently off)
\! [COMMAND] execute command in shell or start interactive shell
Variables
\prompt [TEXT] NAME prompt user to set internal variable
\set [NAME [VALUE]] set internal variable, or list all if no parameters
\unset NAME unset (delete) internal variable
Large Objects
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID large object operations