\/\/ USING POSTGRES SQL<\/p>\n
SELECT table_name
\n FROM information_schema.tables
\nWHERE table_type = ‘BASE TABLE’
\n AND table_schema NOT IN
\n (‘pg_catalog’, ‘information_schema’);<\/p>\n
SELECT column_name
\n FROM information_schema.columns
\nWHERE table_name = ‘YourTablesName’;<\/p>\n
SELECT table_schema, table_name, column_name, data_type
\n FROM information_schema.columns
\nWHERE table_schema = (‘public’) and table_name in (‘agencies’)<\/p>\n
SELECT table_schema, table_name, column_name, data_type, udt_name, character_maximum_length, udt_catalog
\n FROM information_schema.columns
\nWHERE table_schema = (‘public’) and table_name not like ‘pg_%’ and table_name in (‘agencies’) and udt_catalog = ‘ats_development’<\/p>\n
USING PSQL<\/p>\n
echo ‘\\d’ | psql ats_development > tables.sql<\/p>\n
echo ‘\\d users’ | psql ats_development > users.sql<\/p>\n
for table in $(<listoftables); do
\n echo ‘\\d’ | psql
\ndone > $table.sql<\/p>\n
Information on quering information_schema:<\/p>\n
http:\/\/www.alberton.info\/postgresql_meta_info.html<\/p>\n
http:\/\/stackoverflow.com\/questions\/582657\/how-do-i-discover-the-structure-of-a-postgresql-database<\/p>\n
postgres=# \\?
\nGeneral
\n \\copyright show PostgreSQL usage and distribution terms
\n \\g [FILE] or ; execute query (and send results to file or |pipe)
\n \\h [NAME] help on syntax of SQL commands, * for all commands
\n \\q quit psql<\/p>\n
Query Buffer
\n \\e [FILE] [LINE] edit the query buffer (or file) with external editor
\n \\ef [FUNCNAME [LINE]] edit function definition with external editor
\n \\p show the contents of the query buffer
\n \\r reset (clear) the query buffer
\n \\s [FILE] display history or save it to file
\n \\w FILE write query buffer to file<\/p>\n
Input\/Output
\n \\copy … perform SQL COPY with data stream to the client host
\n \\echo [STRING] write string to standard output
\n \\i FILE execute commands from file
\n \\o [FILE] send all query results to file or |pipe
\n \\qecho [STRING] write string to query output stream (see \\o)<\/p>\n
Informational
\n (options: S = show system objects, + = additional detail)
\n \\d[S+] list tables, views, and sequences
\n \\d[S+] NAME describe table, view, sequence, or index
\n \\da[S] [PATTERN] list aggregates
\n \\db[+] [PATTERN] list tablespaces
\n \\dc[S] [PATTERN] list conversions
\n \\dC [PATTERN] list casts
\n \\dd[S] [PATTERN] show comments on objects
\n \\ddp [PATTERN] list default privileges
\n \\dD[S] [PATTERN] list domains
\n \\det[+] [PATTERN] list foreign tables
\n \\des[+] [PATTERN] list foreign servers
\n \\deu[+] [PATTERN] list user mappings
\n \\dew[+] [PATTERN] list foreign-data wrappers
\n \\df[antw][S+] [PATRN] list [only agg\/normal\/trigger\/window] functions
\n \\dF[+] [PATTERN] list text search configurations
\n \\dFd[+] [PATTERN] list text search dictionaries
\n \\dFp[+] [PATTERN] list text search parsers
\n \\dFt[+] [PATTERN] list text search templates
\n \\dg[+] [PATTERN] list roles
\n \\di[S+] [PATTERN] list indexes
\n \\dl list large objects, same as \\lo_list
\n \\dL[S+] [PATTERN] list procedural languages
\n \\dn[S+] [PATTERN] list schemas
\n \\do[S] [PATTERN] list operators
\n \\dO[S+] [PATTERN] list collations
\n \\dp [PATTERN] list table, view, and sequence access privileges
\n \\drds [PATRN1 [PATRN2]] list per-database role settings
\n \\ds[S+] [PATTERN] list sequences
\n \\dt[S+] [PATTERN] list tables
\n \\dT[S+] [PATTERN] list data types
\n \\du[+] [PATTERN] list roles
\n \\dv[S+] [PATTERN] list views
\n \\dE[S+] [PATTERN] list foreign tables
\n \\dx[+] [PATTERN] list extensions
\n \\l[+] list all databases
\n \\sf[+] FUNCNAME show a function’s definition
\n \\z [PATTERN] same as \\dp<\/p>\n
Formatting
\n \\a toggle between unaligned and aligned output mode
\n \\C [STRING] set table title, or unset if none
\n \\f [STRING] show or set field separator for unaligned query output
\n \\H toggle HTML output mode (currently off)
\n \\pset NAME [VALUE] set table output option
\n (NAME := {format|border|expanded|fieldsep|footer|null|
\n numericlocale|recordsep|tuples_only|title|tableattr|pager})
\n \\t [on|off] show only rows (currently off)
\n \\T [STRING] set HTML<\/p>\n
<<\/p>\n
table> tag attributes, or unset if none
\n \\x [on|off] toggle expanded output (currently off)<\/p>\n
Connection
\n \\c[onnect] [DBNAME|- USER|- HOST|- PORT|-]
\n connect to new database (currently “postgres”)
\n \\encoding [ENCODING] show or set client encoding
\n \\password [USERNAME] securely change the password for a user
\n \\conninfo display information about current connection<\/p>\n
Operating System
\n \\cd [DIR] change the current working directory
\n \\timing [on|off] toggle timing of commands (currently off)
\n ! [COMMAND] execute command in shell or start interactive shell<\/p>\n
Variables
\n \\prompt [TEXT] NAME prompt user to set internal variable
\n \\set [NAME [VALUE]] set internal variable, or list all if no parameters
\n \\unset NAME unset (delete) internal variable<\/p>\n
Large Objects
\n \\lo_export LOBOID FILE
\n \\lo_import FILE [COMMENT]
\n \\lo_list
\n \\lo_unlink LOBOID large object operations<\/p>\n","protected":false},"excerpt":{"rendered":"
\/\/ 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 = […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-294","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=\/wp\/v2\/posts\/294","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=294"}],"version-history":[{"count":1,"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=\/wp\/v2\/posts\/294\/revisions"}],"predecessor-version":[{"id":295,"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=\/wp\/v2\/posts\/294\/revisions\/295"}],"wp:attachment":[{"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=294"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=294"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tech.shinynewthings.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=294"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}