我的书签
添加书签
移除书签背景
psql 可谓是最强大的数据库命令行客户端：
支持丰富的LIST对象信息的功能（包括数据库、权限、类型、聚合、域、转换、外部表、外部server、大对象、全文检索、逻辑订阅、插件、事件触发器….等数据库所有对象），
支持编程
支持query buffer管理
支持IO操作
其他，支持格式、变量、操作系统命令等。
支持行列变换。
支持大对象操作。
简化的psql周期图
https://postgresweekly.com/issues/308
psql 详情
1. psql \?
3. postgres=# \?
General
1. \copyright show PostgreSQL usage and distribution terms
2. \crosstabview [COLUMNS] execute query and display results in crosstab
3. \errverbose show most recent error message at maximum verbosity
4. \g [FILE] or ; execute query (and send results to file or |pipe)
5. \gdesc describe result of query, without executing it
6. \gexec execute query, then execute each value in its result
7. \gset [PREFIX] execute query and store results in psql variables
8. \gx [FILE] as \g, but forces expanded output mode
9. \q quit psql
10. \watch [SEC] execute query every SEC seconds
Help
1. \? [commands] show help on backslash commands
2. \? options show help on psql command-line options
3. \? variables show help on special variables
4. \h [NAME] help on syntax of SQL commands, * for all commands
Query Buffer
1. \e [FILE] [LINE] edit the query buffer (or file) with external editor
2. \ef [FUNCNAME [LINE]] edit function definition with external editor
3. \ev [VIEWNAME [LINE]] edit view definition with external editor
4. \p show the contents of the query buffer
5. \r reset (clear) the query buffer
6. \s [FILE] display history or save it to file
7. \w FILE write query buffer to file
Input/Output
1. \copy ... perform SQL COPY with data stream to the client host
2. \echo [STRING] write string to standard output
3. \i FILE execute commands from file
4. \ir FILE as \i, but relative to location of current script
5. \o [FILE] send all query results to file or |pipe
6. \qecho [STRING] write string to query output stream (see \o)
Conditional
1. \if EXPR begin conditional block
2. \elif EXPR alternative within current conditional block
3. \else final alternative within current conditional block
4. \endif end conditional block
Informational
1. (options: S = show system objects, + = additional detail)
2. \d[S+] list tables, views, and sequences
3. \d[S+] NAME describe table, view, sequence, or index
4. \da[S] [PATTERN] list aggregates
5. \dA[+] [PATTERN] list access methods
6. \db[+] [PATTERN] list tablespaces
7. \dc[S+] [PATTERN] list conversions
8. \dC[+] [PATTERN] list casts
9. \dd[S] [PATTERN] show object descriptions not displayed elsewhere
10. \dD[S+] [PATTERN] list domains
11. \ddp [PATTERN] list default privileges
12. \dE[S+] [PATTERN] list foreign tables
13. \det[+] [PATTERN] list foreign tables
14. \des[+] [PATTERN] list foreign servers
15. \deu[+] [PATTERN] list user mappings
16. \dew[+] [PATTERN] list foreign-data wrappers
17. \df[anptw][S+] [PATRN] list [only agg/normal/procedures/trigger/window] functions
18. \dF[+] [PATTERN] list text search configurations
19. \dFd[+] [PATTERN] list text search dictionaries
20. \dFp[+] [PATTERN] list text search parsers
21. \dFt[+] [PATTERN] list text search templates
22. \dg[S+] [PATTERN] list roles
23. \di[S+] [PATTERN] list indexes
24. \dl list large objects, same as \lo_list
25. \dL[S+] [PATTERN] list procedural languages
26. \dm[S+] [PATTERN] list materialized views
27. \dn[S+] [PATTERN] list schemas
28. \do[S] [PATTERN] list operators
29. \dO[S+] [PATTERN] list collations
30. \dp [PATTERN] list table, view, and sequence access privileges
31. \dP[tin+] [PATTERN] list [only table/index] partitioned relations
32. \drds [PATRN1 [PATRN2]] list per-database role settings
33. \dRp[+] [PATTERN] list replication publications
34. \dRs[+] [PATTERN] list replication subscriptions
35. \ds[S+] [PATTERN] list sequences
36. \dt[S+] [PATTERN] list tables
37. \dT[S+] [PATTERN] list data types
38. \du[S+] [PATTERN] list roles
39. \dv[S+] [PATTERN] list views
40. \dx[+] [PATTERN] list extensions
41. \dy [PATTERN] list event triggers
42. \l[+] [PATTERN] list databases
43. \sf[+] FUNCNAME show a function's definition
44. \sv[+] VIEWNAME show a view's definition
45. \z [PATTERN] same as \dp
Formatting
1. \a toggle between unaligned and aligned output mode
2. \C [STRING] set table title, or unset if none
3. \f [STRING] show or set field separator for unaligned query output
4. \H toggle HTML output mode (currently off)
5. \pset [NAME [VALUE]] set table output option
6. (border|columns|csv_fieldsep|expanded|fieldsep|
7. fieldsep_zero|footer|format|linestyle|null|
8. numericlocale|pager|pager_min_lines|recordsep|
9. recordsep_zero|tableattr|title|tuples_only|
10. unicode_border_linestyle|unicode_column_linestyle|
11. unicode_header_linestyle)
12. \t [on|off] show only rows (currently off)
13. \T [STRING] set HTML <table> tag attributes, or unset if none
14. \x [on|off|auto] toggle expanded output (currently off)
Connection
1. \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
2. connect to new database (currently "postgres")
3. \conninfo display information about current connection
4. \encoding [ENCODING] show or set client encoding
5. \password [USERNAME] securely change the password for a user
Operating System
1. \cd [DIR] change the current working directory
2. \setenv NAME [VALUE] set or unset environment variable
3. \timing [on|off] toggle timing of commands (currently off)
4. \! [COMMAND] execute command in shell or start interactive shell
Variables
1. \prompt [TEXT] NAME prompt user to set internal variable
2. \set [NAME [VALUE]] set internal variable, or list all if no parameters
3. \unset NAME unset (delete) internal variable
Large Objects
1. \lo_export LOBOID FILE
2. \lo_import FILE [COMMENT]
3. \lo_list
4. \lo_unlink LOBOID large object operations
例子
1、列出psql 帮助
1. \? [commands] show help on backslash commands
2. \? options show help on psql command-line options
3. \? variables show help on special variables
2、列出SQL COMMAND语法帮助
1. \h [NAME] help on syntax of SQL commands, * for all commands
1. postgres=# \h listen
2. Command: LISTEN
3. Description: listen for a notification
4. Syntax:
5. LISTEN channel
7. URL: https://www.postgresql.org/docs/12/sql-listen.html
3、对查询结果进行行列变换
1. \crosstabview [ colV [ colH [ colD [ sortcolH ] ] ] ]
3. Executes the current query buffer (like \g) and shows the results in a crosstab grid.
1. create table abc (uid int, class text, score float);
2. postgres=# insert into abc select random()*100, (array['语文','数学','英语','物理','化学'])[ceil(random()*5)::int], round((random()*100)::numeric,0) from generate_series(1,500);
3. INSERT 0 500
4. postgres=# select * from abc limit 10;
5. uid | class | score
6. -----+-------+-------
7. 87 | 数学 | 29
8. 54 | 语文 | 95
9. 1 | 语文 | 57
10. 43 | 化学 | 4
11. 51 | 数学 | 37
12. 88 | 数学 | 14
13. 77 | 物理 | 54
14. 44 | 数学 | 5
15. 36 | 语文 | 96
16. 5 | 语文 | 73
17. (10 rows)
20. postgres=# select class, level, count(*) from (select *,width_bucket(score,0,101,5) as level from abc) t group by 1,2 order by 1,2;
21. class | level | count
22. -------+-------+-------
23. 化学 | 1 | 25
24. 化学 | 2 | 30
25. 化学 | 3 | 22
26. 化学 | 4 | 19
27. 化学 | 5 | 19
28. 数学 | 1 | 15
29. 数学 | 2 | 20
30. 数学 | 3 | 12
31. 数学 | 4 | 20
32. 数学 | 5 | 17
33. 物理 | 1 | 17
34. 物理 | 2 | 16
35. 物理 | 3 | 22
36. 物理 | 4 | 26
37. 物理 | 5 | 16
38. 英语 | 1 | 15
39. 英语 | 2 | 16
40. 英语 | 3 | 21
41. 英语 | 4 | 22
42. 英语 | 5 | 15
43. 语文 | 1 | 24
44. 语文 | 2 | 24
45. 语文 | 3 | 23
46. 语文 | 4 | 18
47. 语文 | 5 | 26
48. (25 rows)
指定X轴、Y周进行变换，执行前一条SQL(当前query buffer的SQL)。
1. postgres=# \crosstabview level class
2. level | 化学 | 数学 | 物理 | 英语 | 语文
3. -------+------+------+------+------+------
4. 1 | 25 | 15 | 17 | 15 | 24
5. 2 | 30 | 20 | 16 | 16 | 24
6. 3 | 22 | 12 | 22 | 21 | 23
7. 4 | 19 | 20 | 26 | 22 | 18
8. 5 | 19 | 17 | 16 | 15 | 26
9. (5 rows)
4、打印当前query buffer
1. \p
3. \w FILE write query buffer to file
1. postgres=# select 1;
2. ?column?
3. ----------
4. 1
5. (1 row)
7. postgres=# \p
8. select 1;
将QUERY BUFFER写入文件
1. postgres=# \w '/tmp/abc'
执行操作系统命令
1. \!
1. postgres=# \! cat '/tmp/abc'
2. select 1;
5、打印所有query buffer(实际上是.psql_history )
1. \s [FILE] display history or save it to file
1. postgres=# \s '/tmp/abc'
2. Wrote history to file "/tmp/abc".
1. postgres=# \! head -n 10 '/tmp/abc'
2. create table h (id int, info text0;
3. ;
4. );
5. create table h (id int, info text);
6. insert into h select random()*1000 , 'test' from generate_series(1,1000000);
7. select * from pg_stat_all_indexes where index;
8. create index idx_h_1 on h(id);
9. select * from pg_stat_all_indexes where indexrelname='idx_h_1';
10. explain (analyze,verbose,timing,costs,buffers) select * from h where id=2;
11. select * from pg_stat_all_indexes where indexrelname='idx_h_1';
6、执行SQL
1. \g [FILE] or ; execute query (and send results to file or |pipe)
2. \gdesc describe result of query, without executing it
3. \gexec execute query, then execute each value in its result
4. \gset [PREFIX] execute query and store results in psql variables
5. \gx [FILE] as \g, but forces expanded output mode
直接执行
1. select 1
2. \g
4. 或
6. select 1
7. ;
将执行结果存储到PSQL所在的系统文件中
1. select 1
2. \g '/tmp/abc'
列出query buffer中的SQL的返回结果的结构
1. postgres=# \p
2. select 1;
4. postgres=# \gdesc
5. Column | Type
6. ----------+---------
7. ?column? | integer
8. (1 row)
11. postgres=# select 'abc',1,2;
12. ?column? | ?column? | ?column?
13. ----------+----------+----------
14. abc | 1 | 2
15. (1 row)
17. postgres=# \p
18. select 'abc',1,2;
19. postgres=# \gdesc
20. Column | Type
21. ----------+---------
22. ?column? | text
23. ?column? | integer
24. ?column? | integer
25. (3 rows)
执行QUERY，并执行QUERY的结果。通常用于批量操作。
1. postgres=# select 'create table t_'||i||'( id int, info text, crt_time timestamp)' from generate_series(1,10) i
2. postgres-# \gexec
3. CREATE TABLE
4. CREATE TABLE
5. CREATE TABLE
6. CREATE TABLE
7. CREATE TABLE
8. CREATE TABLE
9. CREATE TABLE
10. CREATE TABLE
11. CREATE TABLE
12. CREATE TABLE
14. postgres=# \dt t_*
15. List of relations
16. Schema | Name | Type | Owner
17. --------+------+-------+----------
18. public | t_1 | table | postgres
19. public | t_10 | table | postgres
20. public | t_2 | table | postgres
21. public | t_3 | table | postgres
22. public | t_4 | table | postgres
23. public | t_5 | table | postgres
24. public | t_6 | table | postgres
25. public | t_7 | table | postgres
26. public | t_8 | table | postgres
27. public | t_9 | table | postgres
28. (10 rows)
将结果设置到COLUMN ALIAS的变量中
1. postgres=# select 'abcd' as col1, 1 as col2
2. postgres-# \gset
3. postgres=# \echo :col1
4. abcd
5. postgres=# \echo :col2
6. 1
列出已设置的PSQL变量
1. postgres=# \set
2. AUTOCOMMIT = 'on'
3. COMP_KEYWORD_CASE = 'preserve-upper'
4. DBNAME = 'postgres'
5. ECHO = 'none'
6. ECHO_HIDDEN = 'off'
7. ENCODING = 'UTF8'
8. ERROR = 'false'
9. FETCH_COUNT = '0'
10. HIDE_TABLEAM = 'off'
11. HISTCONTROL = 'none'
12. HISTSIZE = '500'
13. HOST = '/data01/pg12/pg_root12000'
14. IGNOREEOF = '0'
15. LASTOID = '0'
16. LAST_ERROR_MESSAGE = ''
17. LAST_ERROR_SQLSTATE = '00000'
18. ON_ERROR_ROLLBACK = 'off'
19. ON_ERROR_STOP = 'off'
20. PORT = '12000'
21. PROMPT1 = '%/%R%# '
22. PROMPT2 = '%/%R%# '
23. PROMPT3 = '>> '
24. QUIET = 'off'
25. ROW_COUNT = '1'
26. SERVER_VERSION_NAME = '12beta1'
27. SERVER_VERSION_NUM = '120000'
28. SHOW_CONTEXT = 'errors'
29. SINGLELINE = 'off'
30. SINGLESTEP = 'off'
31. SQLSTATE = '00000'
32. USER = 'postgres'
33. VERBOSITY = 'default'
34. VERSION = 'PostgreSQL 12beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit'
35. VERSION_NAME = '12beta1'
36. VERSION_NUM = '120000'
37. col1 = 'abcd'
38. col2 = '1'
扩展方式返回结果
1. \x
2. query;
4. 或
6. \gx
1. postgres=# \p
2. select 'abcd' as col1, 1 as col2
3. postgres=# \g
4. col1 | col2
5. ------+------
6. abcd | 1
7. (1 row)
9. postgres=# \gx
10. -[ RECORD 1 ]
11. col1 | abcd
12. col2 | 1
7、每隔N秒执行一次query buffer中的SQL
1. postgres=# \p
2. select 'abcd' as col1, 1 as col2
3. postgres=# \watch 1
4. Sat 08 Jun 2019 10:33:55 AM CST (every 1s)
6. col1 | col2
7. ------+------
8. abcd | 1
9. (1 row)
11. Sat 08 Jun 2019 10:33:56 AM CST (every 1s)
13. col1 | col2
14. ------+------
15. abcd | 1
16. (1 row)
8、条件判定逻辑
gset执行并设置变量到column alias
1. -- check for the existence of two separate records in the database and store
2. -- the results in separate psql variables
3. SELECT
4. EXISTS(SELECT 1 FROM customer WHERE customer_id = 123) as is_customer,
5. EXISTS(SELECT 1 FROM employee WHERE employee_id = 456) as is_employee
6. \gset
7. \if :is_customer
8. SELECT * FROM customer WHERE customer_id = 123;
9. \elif :is_employee
10. \echo 'is not a customer but is an employee'
11. SELECT * FROM employee WHERE employee_id = 456;
12. \else
13. \if yes
14. \echo 'not a customer or employee'
15. \else
16. \echo 'this will never print'
17. \endif
18. \endif
9、大对象操作
1. postgres=# \lo_import '/tmp/abcd'
2. could not open file "/tmp/abcd": No such file or directory
3. postgres=# \lo_import '/tmp/abc'
4. lo_import 16516
5. postgres=# \lo_list
6. Large objects
7. ID | Owner | Description
8. -------+----------+-------------
9. 16516 | postgres |
10. (1 row)
12. postgres=# \lo_export 16516 '/tmp/copy_abc'
13. lo_export
14. postgres=# \! diff '/tmp/copy_abc' '/tmp/abc'
15. postgres=# \! head -n 2 '/tmp/copy_abc'
16. create table h (id int, info text0;
17. ;
18. postgres=# \! head -n 2 '/tmp/abc'
19. create table h (id int, info text0;
20. ;
22. postgres=# select * from pg_largeobject_metadata;
23. oid | lomowner | lomacl
24. -------+----------+--------
25. 16516 | 10 |
26. (1 row)
清理大对象
1. postgres=# \lo_unlink 16516
2. lo_unlink 16516
3. postgres=# \lo_list
4. Large objects
5. ID | Owner | Description
6. ----+-------+-------------
7. (0 rows)
9. postgres=# select * from pg_largeobject_metadata ;
10. oid | lomowner | lomacl
11. -----+----------+--------
12. (0 rows)
14. postgres=# select * from pg_largeobject;
15. loid | pageno | data
16. ------+--------+------
17. (0 rows)
10、COPY PSQL的客户端服务器上的文件到数据库，或COPY数据库数据（表或者SQL）的结果到执行PSQL的客户端。
《PostgreSQL 服务端COPY和客户端COPY - 暨PG有哪些服务端操作接口》
《PostgreSQL copy (quote,DELIMITER,…) single byte char 的输入》
11、连接其他数据库
2. postgres=# create role abc login;
3. CREATE ROLE
4. postgres=# \c 'hostaddr=127.0.0.1 dbname=postgres user=abc port=12000'
5. You are now connected to database "postgres" as user "abc".
7. 连接当前实例的其他DB或USER，可以简写
9. \c dbname username
10. 例如
11. postgres=> \c postgres postgres
12. You are now connected to database "postgres" as user "postgres".
12、列出当前连接信息
1. postgres=# \conninfo
2. You are connected to database "postgres" as user "postgres" via socket in "/data01/pg12/pg_root12000" at port "12000".
13、列出对象信息、权限
S包括系统对象
1. Informational
2. (options: S = show system objects, + = additional detail)
3. \d[S+] list tables, views, and sequences
4. \d[S+] NAME describe table, view, sequence, or index
5. \da[S] [PATTERN] list aggregates
6. \dA[+] [PATTERN] list access methods
7. \db[+] [PATTERN] list tablespaces
8. \dc[S+] [PATTERN] list conversions
9. \dC[+] [PATTERN] list casts
10. \dd[S] [PATTERN] show object descriptions not displayed elsewhere
11. \dD[S+] [PATTERN] list domains
12. \ddp [PATTERN] list default privileges
13. \dE[S+] [PATTERN] list foreign tables
14. \det[+] [PATTERN] list foreign tables
15. \des[+] [PATTERN] list foreign servers
16. \deu[+] [PATTERN] list user mappings
17. \dew[+] [PATTERN] list foreign-data wrappers
18. \df[anptw][S+] [PATRN] list [only agg/normal/procedures/trigger/window] functions
19. \dF[+] [PATTERN] list text search configurations
20. \dFd[+] [PATTERN] list text search dictionaries
21. \dFp[+] [PATTERN] list text search parsers
22. \dFt[+] [PATTERN] list text search templates
23. \dg[S+] [PATTERN] list roles
24. \di[S+] [PATTERN] list indexes
25. \dl list large objects, same as \lo_list
26. \dL[S+] [PATTERN] list procedural languages
27. \dm[S+] [PATTERN] list materialized views
28. \dn[S+] [PATTERN] list schemas
29. \do[S] [PATTERN] list operators
30. \dO[S+] [PATTERN] list collations
31. \dp [PATTERN] list table, view, and sequence access privileges
32. \dP[tin+] [PATTERN] list [only table/index] partitioned relations
33. \drds [PATRN1 [PATRN2]] list per-database role settings
34. \dRp[+] [PATTERN] list replication publications
35. \dRs[+] [PATTERN] list replication subscriptions
36. \ds[S+] [PATTERN] list sequences
37. \dt[S+] [PATTERN] list tables
38. \dT[S+] [PATTERN] list data types
39. \du[S+] [PATTERN] list roles
40. \dv[S+] [PATTERN] list views
41. \dx[+] [PATTERN] list extensions
42. \dy [PATTERN] list event triggers
43. \l[+] [PATTERN] list databases
44. \sf[+] FUNCNAME show a function's definition
45. \sv[+] VIEWNAME show a view's definition
46. \z [PATTERN] same as \dp
14、执行psql服务器上的文件
1. \i FILE execute commands from file
2. \ir FILE as \i, but relative to location of current script
1. postgres=# \a
2. Output format is unaligned.
3. postgres=# \t on
6. postgres=# select format('create table abc_%s (id int);', i) from generate_series(1,10) i
7. postgres-# \g '/tmp/abcd'
8. postgres=# \! cat '/tmp/abcd'
9. create table abc_1 (id int);
10. create table abc_2 (id int);
11. create table abc_3 (id int);
12. create table abc_4 (id int);
13. create table abc_5 (id int);
14. create table abc_6 (id int);
15. create table abc_7 (id int);
16. create table abc_8 (id int);
17. create table abc_9 (id int);
18. create table abc_10 (id int);
1. postgres=# \i '/tmp/abcd'
2. CREATE TABLE
3. CREATE TABLE
4. CREATE TABLE
5. CREATE TABLE
6. CREATE TABLE
7. CREATE TABLE
8. CREATE TABLE
9. CREATE TABLE
10. CREATE TABLE
11. CREATE TABLE
12. postgres=# \dt
13. public|abc_1|table|postgres
14. public|abc_10|table|postgres
15. public|abc_2|table|postgres
16. public|abc_3|table|postgres
17. public|abc_4|table|postgres
18. public|abc_5|table|postgres
19. public|abc_6|table|postgres
20. public|abc_7|table|postgres
21. public|abc_8|table|postgres
22. public|abc_9|table|postgres
小结
psql是非常强大的PostgreSQL客户端，可以极大提高数据库管理人员的管理便捷性。
参考
man psql
