PSQL序

切换到postgres用户进入数据库控制台

hightlight:true
1
sudo -u postgres psql

创建新数据库

hightlight:true
1
create database newdbname [owner username]

赋予用户数据库所有权限

hightlight:true
1
grant all privileges on database dbname to username

更改数据库owner

hightlight:true
1
alter database dbname owner to username

查看所有数据库列表

hightlight:true
1
\l

连接数据库

hightlight:true
1
\c dbname 

显示所有的schema

hightlight:true
1
\dn

显示所有的用户

hightlight:true
1
\du

显示表的权限分配情况

hightlight:true
1
\dp

显示当前的模式

hightlight:true
1
show search_path

更改模式

hightlight:true
1
set search_path to myschema

已列的形式展示或取消

hightlight:true
1
\x 

查看所有表

hightlight:true
1
\dt

显示执行时间

hightlight:true
1
\timing on     

关闭显示执行时间

hightlight:true
1
\timing off

禁用全表扫描

hightlight:true
1
2
alter role rolename set enable_seqscan = off;
set enable_seqscan = off;

PSQL COMMANDS

General

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
\copyright             
# show PostgreSQL usage and distribution terms

\errverbose
# show most recent error message at maximum verbosity

\g [FILE] or ;
# execute query (and send results to file or |pipe)

\gexec
# execute query, then execute each value in its result

\gset [PREFIX]
# execute query and store results in psql variables

\q
# quit psql

\crosstabview [COLUMNS]
# execute query and display results in crosstab

\watch [SEC]
# execute query every SEC seconds

Help

1
2
3
4
5
6
7
8
9
10
11
\? [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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
\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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
\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 location of current script

\o [FILE]
# send all query results to file or |pipe

\qecho [STRING]
# write string to query output stream (see \o)

Informational

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
(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

\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[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] [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[S+] [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

\sv[+] VIEWNAME
# show a view's definition

\z [PATTERN]
# same as \dp

Formatting

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
\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 := {border|columns|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

1
2
3
4
5
6
7
8
9
10
11
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
# connect to new database (currently "crm_online_test")

\encoding [ENCODING]
# show or set client encoding

\password [USERNAME]
# securely change the password for a user

\conninfo
# display information about current connection

Operating System

1
2
3
4
5
6
7
8
9
10
11
\cd [DIR]              
# change the current working directory

\setenv NAME [VALUE]
# set or unset environment variable

\timing [on|off]
# toggle timing of commands (currently on)

\! [COMMAND]
# execute command in shell or start interactive shell

Variables

1
2
3
4
5
6
7
8
\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

1
2
3
4
5
6
7
8
\lo_export LOBOID FILE

\lo_import FILE [COMMENT]

\lo_list

\lo_unlink LOBOID
# large object operations