########################
apple=# \?
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display result in crosstab
\errverbose show most recent error message at maximum verbosity
\g [(OPTIONS)] [FILE] execute query (and send result to file or |pipe);
\g with no arguments is equivalent to a semicolon
\gdesc describe result of query, without executing it
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store result in psql variables
\gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
\q quit psql
\watch [SEC] execute query every SEC seconds
Help
\? [commands] show help on backslash commands
\? options show help on psql command-line options
\? variables show help on special variables
\h [NAME] help on syntax of SQL commands, * for all commands
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\ev [VIEWNAME [LINE]] edit view 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 [-n] [STRING] write string to standard output (-n for no newline)
\i FILE execute commands from file
\ir FILE as \i, but relative to location of current script
\o [FILE] send all query results to file or |pipe
\qecho [-n] [STRING] write string to \o output stream (-n for no newline)
\warn [-n] [STRING] write string to standard error (-n for no newline)
Conditional
\if EXPR begin conditional block
\elif EXPR alternative within current conditional block
\else final alternative within current conditional block
\endif end conditional block
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
\dA[+] [PATTERN] list access methods
\dAc[+] [AMPTRN [TYPEPTRN]] list operator classes
\dAf[+] [AMPTRN [TYPEPTRN]] list operator families
\dAo[+] [AMPTRN [OPFPTRN]] list operators of operator families
\dAp[+] [AMPTRN [OPFPTRN]] list support functions of operator families
\db[+] [PATTERN] list tablespaces
\dc[S+] [PATTERN] list conversions
\dconfig[+] [PATTERN] list configuration parameters
\dC[+] [PATTERN] list casts
\dd[S] [PATTERN] show object descriptions not displayed elsewhere
\dD[S+] [PATTERN] list domains
\ddp [PATTERN] list default privileges
\dE[S+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\det[+] [PATTERN] list foreign tables
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[anptw][S+] [FUNCPTRN [TYPEPTRN ...]]
list [only agg/normal/procedure/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[S+] [PATTERN] list roles
\di[S+] [PATTERN] list indexes
\dl[+] list large objects, same as \lo_list
\dL[S+] [PATTERN] list procedural languages
\dm[S+] [PATTERN] list materialized views
\dn[S+] [PATTERN] list schemas
\do[S+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]
list operators
\dO[S+] [PATTERN] list collations
\dp [PATTERN] list table, view, and sequence access privileges
\dP[itn+] [PATTERN] list [only index/table] partitioned relations [n=nested]
\drds [ROLEPTRN [DBPTRN]] list per-database role settings
\dRp[+] [PATTERN] list replication publications
\dRs[+] [PATTERN] list replication subscriptions
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\du[S+] [PATTERN] list roles
\dv[S+] [PATTERN] list views
\dx[+] [PATTERN] list extensions
\dX [PATTERN] list extended statistics
\dy[+] [PATTERN] list event triggers
\l[+] [PATTERN] list databases
\sf[+] FUNCNAME show a function's definition
\sv[+] VIEWNAME show a view's definition
\z [PATTERN] same as \dp
Large Objects
\lo_export LOBOID FILE write large object to file
\lo_import FILE [COMMENT]
read large object from file
\lo_list[+] list large objects
\lo_unlink LOBOID delete a large object
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
(border|columns|csv_fieldsep|expanded|fieldsep|
fieldsep_zero|footer|format|linestyle|null|
numericlocale|pager|pager_min_lines|recordsep|
recordsep_zero|tableattr|title|tuples_only|
unicode_border_linestyle|unicode_column_linestyle|
unicode_header_linestyle)
\t [on|off] show only rows (currently off)
\T [STRING] set HTML <table> tag attributes, or unset if none
\x [on|off|auto] toggle expanded output (currently off)
Connection
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
connect to new database (currently "apple")
\conninfo display information about current connection
\encoding [ENCODING] show or set client encoding
\password [USERNAME] securely change the password for a user
Operating System
\cd [DIR] change the current working directory
\getenv PSQLVAR ENVVAR fetch environment variable
\setenv NAME [VALUE] set or unset environment variable
\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
apple=#
查看所有系统表：
- 在
psql 工具中使用 \d 或者 \d+ 列出当前当前数据库中的所有的表。 - 从
information_schema.columns 中查询表中的列。
apple=# \dt pg_*
List of relations
Schema | Name | Type | Owner
------------+--------------------------+-------+-------
pg_catalog | pg_aggregate | table | work
pg_catalog | pg_am | table | work
pg_catalog | pg_amop | table | work
pg_catalog | pg_amproc | table | work
pg_catalog | pg_attrdef | table | work
pg_catalog | pg_attribute | table | work
pg_catalog | pg_auth_members | table | work
pg_catalog | pg_authid | table | work
pg_catalog | pg_cast | table | work
pg_catalog | pg_class | table | work
pg_catalog | pg_collation | table | work
pg_catalog | pg_constraint | table | work
pg_catalog | pg_conversion | table | work
pg_catalog | pg_database | table | work
pg_catalog | pg_db_role_setting | table | work
pg_catalog | pg_default_acl | table | work
pg_catalog | pg_depend | table | work
pg_catalog | pg_description | table | work
pg_catalog | pg_enum | table | work
pg_catalog | pg_event_trigger | table | work
pg_catalog | pg_extension | table | work
pg_catalog | pg_foreign_data_wrapper | table | work
pg_catalog | pg_foreign_server | table | work
pg_catalog | pg_foreign_table | table | work
pg_catalog | pg_index | table | work
pg_catalog | pg_inherits | table | work
pg_catalog | pg_init_privs | table | work
pg_catalog | pg_language | table | work
pg_catalog | pg_largeobject | table | work
pg_catalog | pg_largeobject_metadata | table | work
pg_catalog | pg_namespace | table | work
pg_catalog | pg_opclass | table | work
pg_catalog | pg_operator | table | work
pg_catalog | pg_opfamily | table | work
pg_catalog | pg_parameter_acl | table | work
pg_catalog | pg_partitioned_table | table | work
pg_catalog | pg_policy | table | work
pg_catalog | pg_proc | table | work
pg_catalog | pg_publication | table | work
pg_catalog | pg_publication_namespace | table | work
pg_catalog | pg_publication_rel | table | work
pg_catalog | pg_range | table | work
pg_catalog | pg_replication_origin | table | work
pg_catalog | pg_rewrite | table | work
pg_catalog | pg_seclabel | table | work
pg_catalog | pg_sequence | table | work
pg_catalog | pg_shdepend | table | work
pg_catalog | pg_shdescription | table | work
pg_catalog | pg_shseclabel | table | work
pg_catalog | pg_statistic | table | work
pg_catalog | pg_statistic_ext | table | work
pg_catalog | pg_statistic_ext_data | table | work
pg_catalog | pg_subscription | table | work
pg_catalog | pg_subscription_rel | table | work
pg_catalog | pg_tablespace | table | work
pg_catalog | pg_transform | table | work
pg_catalog | pg_trigger | table | work
pg_catalog | pg_ts_config | table | work
pg_catalog | pg_ts_config_map | table | work
pg_catalog | pg_ts_dict | table | work
pg_catalog | pg_ts_parser | table | work
pg_catalog | pg_ts_template | table | work
pg_catalog | pg_type | table | work
pg_catalog | pg_user_mapping | table | work
(64 rows)
apple=#
查看所有系统视图：
apple=# \dv pg_*
List of relations
Schema | Name | Type | Owner
------------+---------------------------------+------+-------
pg_catalog | pg_available_extension_versions | view | work
pg_catalog | pg_available_extensions | view | work
pg_catalog | pg_backend_memory_contexts | view | work
pg_catalog | pg_config | view | work
pg_catalog | pg_cursors | view | work
pg_catalog | pg_file_settings | view | work
pg_catalog | pg_group | view | work
pg_catalog | pg_hba_file_rules | view | work
pg_catalog | pg_ident_file_mappings | view | work
pg_catalog | pg_indexes | view | work
pg_catalog | pg_locks | view | work
pg_catalog | pg_matviews | view | work
pg_catalog | pg_policies | view | work
pg_catalog | pg_prepared_statements | view | work
pg_catalog | pg_prepared_xacts | view | work
pg_catalog | pg_publication_tables | view | work
pg_catalog | pg_replication_origin_status | view | work
pg_catalog | pg_replication_slots | view | work
pg_catalog | pg_roles | view | work
pg_catalog | pg_rules | view | work
pg_catalog | pg_seclabels | view | work
pg_catalog | pg_sequences | view | work
pg_catalog | pg_settings | view | work
pg_catalog | pg_shadow | view | work
pg_catalog | pg_shmem_allocations | view | work
pg_catalog | pg_stat_activity | view | work
pg_catalog | pg_stat_all_indexes | view | work
pg_catalog | pg_stat_all_tables | view | work
pg_catalog | pg_stat_archiver | view | work
pg_catalog | pg_stat_bgwriter | view | work
pg_catalog | pg_stat_database | view | work
pg_catalog | pg_stat_database_conflicts | view | work
pg_catalog | pg_stat_gssapi | view | work
pg_catalog | pg_stat_progress_analyze | view | work
pg_catalog | pg_stat_progress_basebackup | view | work
pg_catalog | pg_stat_progress_cluster | view | work
pg_catalog | pg_stat_progress_copy | view | work
pg_catalog | pg_stat_progress_create_index | view | work
pg_catalog | pg_stat_progress_vacuum | view | work
pg_catalog | pg_stat_recovery_prefetch | view | work
pg_catalog | pg_stat_replication | view | work
pg_catalog | pg_stat_replication_slots | view | work
pg_catalog | pg_stat_slru | view | work
pg_catalog | pg_stat_ssl | view | work
pg_catalog | pg_stat_subscription | view | work
pg_catalog | pg_stat_subscription_stats | view | work
pg_catalog | pg_stat_sys_indexes | view | work
pg_catalog | pg_stat_sys_tables | view | work
pg_catalog | pg_stat_user_functions | view | work
pg_catalog | pg_stat_user_indexes | view | work
pg_catalog | pg_stat_user_tables | view | work
pg_catalog | pg_stat_wal | view | work
pg_catalog | pg_stat_wal_receiver | view | work
pg_catalog | pg_stat_xact_all_tables | view | work
pg_catalog | pg_stat_xact_sys_tables | view | work
pg_catalog | pg_stat_xact_user_functions | view | work
pg_catalog | pg_stat_xact_user_tables | view | work
pg_catalog | pg_statio_all_indexes | view | work
pg_catalog | pg_statio_all_sequences | view | work
pg_catalog | pg_statio_all_tables | view | work
pg_catalog | pg_statio_sys_indexes | view | work
pg_catalog | pg_statio_sys_sequences | view | work
pg_catalog | pg_statio_sys_tables | view | work
pg_catalog | pg_statio_user_indexes | view | work
pg_catalog | pg_statio_user_sequences | view | work
pg_catalog | pg_statio_user_tables | view | work
pg_catalog | pg_stats | view | work
pg_catalog | pg_stats_ext | view | work
pg_catalog | pg_stats_ext_exprs | view | work
pg_catalog | pg_tables | view | work
pg_catalog | pg_timezone_abbrevs | view | work
pg_catalog | pg_timezone_names | view | work
pg_catalog | pg_user | view | work
pg_catalog | pg_user_mappings | view | work
pg_catalog | pg_views | view | work
(75 rows)
apple=#
查看所有函数：
select routine_name from information_schema.routines;
#########################
