psql常见命令

########################

 

 

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;

 

 

 

#########################

 

posted @ 2023-02-20 10:06  igoodful  阅读(316)  评论(1)  编辑  收藏  举报