Tips for using psql tools

Psql powerful, skills how to use only follow-up slowly supplemented, see a tool first see how to use Help
$ psql #连接到数据库服务器, in which you can enter the appropriate SQL statement or PSQL command, psql command starts with \
$ \l #列出所有的数据库
$ \d #查看表的结构
$ \d Table name #查看指定表的具体结构
$ CREATE DATABASE name #创建新的数据库
$ \c The database name already exists #切换当前数据库到指定的数据库
$ psql-h Host name/IP address-P port number database name User name #连接到指定的数据库
$ \d #显示所有的表
$ \d Table name #显示指定的表的结构
$ \d Index name #显示指定索引的结构
$ \d followed by a wildcard character or wait #? Represents a matchingRepresents a match for multiple, such as \d s* matches a table or index with any character beginning with s, and displays it, \d s? matches a table or index followed by a character starting with s and displaying it
$ \d+ #该命令会比 The \d command displays more detailed information, in addition to those described earlier, it also shows any comments associated with the table, and the oid,\d+ that appear in the table can replace any previous command that starts with \d
$ \DT #只显示表
$ \di #只显示所有的索引
$ \ds #只显示序列
$ \DV #只显示视图
$ \DF #只显示函数
$ \timing on #显示SQL的执行时间
$ \timing off #关闭显示SQL的执行时间
$ \DN #显示所有的schema
$ \db #显示所有的表空间
$ \DU or \DG #显示数据库中的所有角色和用户
$ \DP or \z table name #显示表的权限分配情况
$ \encoding UTF8 #当客户端的字符编码和服务器的不一样时, may be garbled, you can use the \encoding command to specify the client's character encoding, such as using \encoding UTF8 to specify that the client is encoded as UTF8
The \pset command is used to specify the format of the output, as follows:
\pset Border 0: Indicates the output content border
\pset Border 1: Indicates that the border is internal only, by default this is the command
\pset Border 2: Indicates that there are borders inside and outside
History command and complement function
Use the up and down keys to view history commands
Two times Tab key to complete command completion or to give input hints
In Psql, transactions are automatically committed, for example, after a CREATE TABLE statement is executed, the transaction is automatically committed, and if you do not want to commit automatically, there are two methods:
Scenario One: Run the BEGIN command, execute the appropriate SQL statement, and finally execute a commit or ROLLBACK statement
Scenario Two: Use the Psql command to turn off autocommit functionality, \set autocommit off, which sets autocommit off
In fact, MySQL and other databases are also the case, when we do not use the displayed transaction statements, each SQL statement is treated as a transaction, or the database inside each SQL statement is encapsulated as a transaction, in PostgreSQL is still the same, usually used to pay attention to.
Get the SQL statements that are actually executed in Psql
aoldbs=# Help
You is using Psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution Terms
\h for help with SQL commands
\? For help with Psql commands
\g or terminate with semicolon to execute query
\q to quit
aoldbs=#

eg: View SQL commands
aoldbs=# \h drop table
Command:drop TABLE
Description:remove a table
Syntax:
DROP TABLE [IF EXISTS] name [, ...] [CASCADE | RESTRICT]

aoldbs=#

View Psql Command

General
\copyright Show PostgreSQL usage and distribution terms
\g [FILE] or; Execute query (and send results to file or |pipe)
\gset [PREFIX] Execute query and store results in Psql variables
\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 in 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
\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
\ir FILE as \i, but relative to the location of the current script
\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 object descriptions not displayed elsewhere
\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
\dm[s+] [PATTERN] list materialized views
\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
\dy [PATTERN] list event triggers
\l[+] [PATTERN] list 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|fieldsep_zero|footer|null|
numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager|
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 "Aoldbs")
\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
\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

Large Objects
\lo_export loboid FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink loboid Large Object operations

Tips for using psql tools