// USING POSTGRES SQL

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’;

SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = (‘public’) and table_name in (‘agencies’)

SELECT table_schema, table_name, column_name, data_type, udt_name, character_maximum_length, udt_catalog
FROM information_schema.columns
WHERE table_schema = (‘public’) and table_name not like ‘pg_%’ and table_name in (‘agencies’) and udt_catalog = ‘ats_development’

USING PSQL

echo ‘\d’ | psql ats_development > tables.sql

echo ‘\d users’ | psql ats_development > users.sql

for table in $(<listoftables); do
echo ‘\d’ | psql
done > $table.sql

Information on quering information_schema:

http://www.alberton.info/postgresql_meta_info.html

http://stackoverflow.com/questions/582657/how-do-i-discover-the-structure-of-a-postgresql-database

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