psql命令：
1.拷贝数据到excel;
postgres=# \copy (select * from user_test) to h:\user_test.csv with (format csv);
copy 5
time: 0.996 ms
testdb2=# \copy (select * from t_user) to /home/postgres/test.csv with (format csv);
copy 3
time: 0.312 ms
testdb2=#
2.查看有哪些;
postgres-# \l
list of databases
name | owner | encoding | collate | ctype | access privileges
———–+———-+———-+——————————–+——————————–+———————–
postgres | postgres | utf8 | chinese (simplified)_china.936 | chinese (simplified)_china.936 |
template0 | postgres | utf8 | chinese (simplified)_china.936 | chinese (simplified)_china.936 | =c/postgres +
| | | | | postgres=ctc/postgres
template1 | postgres | utf8 | chinese (simplified)_china.936 | chinese (simplified)_china.936 | =c/postgres +
| | | | | postgres=ctc/postgres
(3 rows)
3.创建数据库
postgres-# create database testdb;
error: syntax error at or near “psql”
line 1: psql -l
^
postgres=# create database testdb;
error: source database “template1” is being accessed by other users
detail: there are 2 other sessions using the database.
postgres=#
解决问题：
通过以上分析，其实要解决这几个问题有以下几种方案：
1.linux中重启postgresql服务器进程(/etc/rc.d/init.d/postgresql restart)后，再在psql中用“create database $数据库名称;”创建数据库。注：不推荐使用这一方案。
2.关闭windows xp下的navicat premium客户端关于连接到postgresql服务器的连接，然后再在psql中用“create database $数据库名称;”创建数据库。
3.在linux服务器中用“kill -9 $进程号”杀死postgresql服务进程(本例中的进程号为27122)，然后再在psql中用“create database $数据库名称;”创建数据库。
4.在创建数据库时指定模板数据库为template0，指定方式在psql客户端中用“create database $数据库名称 with template=template0;”(中的with可有 可无)，在shell命令行(请先切换到postgres用户)中用“createdb $数据库名称 -t template0”或“createdb $数据库名称 –template=template0”均可，“-t或–template”
postgres=# create database testdb2;
create database
postgres=#
4.连接数据库
postgres=# \c testdb2;
you are now connected to database “testdb2” as user “postgres”.
testdb2=#
5.psql连接数据库的命令格式
psql -h -p <端口> [数据库名称] [用户名称]
psql -h 192.168.229.132 -p 5432 testdb postgres
可以将连接参数配进环境变量
export pgdatabase=testdb
export pghost=192.168.229.132
export pgport=5432
export pguser=postgres
然后运行 psql 即可
6.查看表的示例
\d后什么也不带，显示数据库中所有的表
postgres-# \d
list of relations
schema | name | type | owner
——–+———–+——-+———-
public | user_test | table | postgres
(1 row)
\d后跟一个表名，显示表的结构
testdb2-# \d test
table “public.test”
column | type | modifiers
——–+——————-+———–
id | integer |
name | character varying |
mark | character varying |
testdb2-#
\d后跟着索引，显示索引的信息
testdb2=# create index idx_id_idx on test (id);
create index
testdb2=# \d test;
table “public.test”
column | type | modifiers
——–+——————-+———–
id | integer |
name | character varying |
mark | character varying |
indexes:
“idx_id_idx” btree (id)
testdb2=# \d idx_id_idx;
index “public.idx_id_idx”
column | type | definition
——–+———+————
id | integer | id
btree, for table “public.test”
testdb2=#
\d后也可跟通配符，如\d t*
7.\d+显示更详细的信息
testdb2=# \d+ test
table “public.test”
column | type | modifiers | storage | stats target | description
——–+——————-+———–+———-+————–+————-
id | integer | | plain | |
name | character varying | | extended | |
mark | character varying | | extended | |
indexes:
“idx_id_idx” btree (id)
testdb2=#
8.区分不同对象类型的\d命令
匹配的表 \dt
显示索引 \di
显示序列 \ds
显示视图 \dv
显示函数 \df
9.显示sql执行时间 \timing
postgres=# \timing on
timing is on.
postgres=# select count(*) from test_user;
count
——-
1
(1 row)
time: 0.209 ms
postgres=#
10.列出所有schema
testdb2=# \dn
list of schemas
name | owner
——–+———-
public | postgres
(1 row)
11.列出所有表空间
testdb2=# \db
list of tablespaces
name | owner | location
————+———-+———-
pg_default | postgres |
pg_global | postgres |
12.列出所有角色或用户，可用\du 后 \dg 两个命令等价
testdb2=# \du
list of roles
role name | attributes | member of
———–+————————————————————+———–
luxuefeng | | {}
postgres | superuser, create role, create db, replication, bypass rls | {}
testdb2=# \dg
list of roles
role name | attributes | member of
———–+————————————————————+———–
luxuefeng | | {}
postgres | superuser, create role, create db, replication, bypass rls | {}
13.\dp 或 \z 显示表的分配权限
testdb2=# \dp
access privileges
schema | name | type | access privileges | column privileges | policies
——–+——–+——-+——————-+——————-+———-
public | t_user | table | | |
public | test | table | | |
(2 rows)
testdb2=# \z
access privileges
schema | name | type | access privileges | column privileges | policies
——–+——–+——-+——————-+——————-+———-
public | t_user | table | | |
public | test | table | | |
(2 rows)
14.指定字符集编译命令
\encoding指定客户端的字符集，如：\encoding gbk; \encoding utf8;
15.\pset 设置输出的格式
\pset border 0: 输出内容无边框;
\pset border 1:边框只在内部;
\pset border 2:内外都有边框;
15. \x，把表中每一行的每列数据都拆分为单行展示;
testdb2=# \x
expanded display is on.
testdb2=# select * from t_user;
-[ record 1 ]——-
id | 3
name | 瑶瑶
mark | 哈哈一样f哈哈
-[ record 2 ]——-
id | 2
name | feng
mark | feng
-[ record 3 ]——-
id | 1
name | 雪
mark | snow big snow
time: 0.220 ms
17.更多的命令
testdb2=# \?
general
\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
\? [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 [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
(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
\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|pa
ger|
unicode_border_linestyle|unicode_column_linestyle|unicode_header_line
style})
\t [on|off] show only rows (currently off)
\t [string] set html tag attributes, or unset if none
\x [on|off|auto] toggle expanded output (currently on)
connection
\c[onnect] {[dbname|- user|- host|- port|-] | conninfo}
connect to new database (currently “testdb2”)
\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 on)
\! [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
testdb2=#
18.自动提交的技巧
psql中的事务是自动提交的。比如，执行一条 delete 或 update 语句后，事务就会自动提交，如不想自动提交，方法有两种。
方法1：运行begin命令，然后执行dml语句，最后执行commit或rollback语句。
testdb2=# begin;
begin
time: 0.127 ms
testdb2=# update t_user set name = ‘凤’ where id =2;
update 1
time: 0.589 ms
testdb2=# select * from t_user;
-[ record 1 ]——-
id | 3
name | 瑶瑶
mark | 哈哈一样f哈哈
-[ record 2 ]——-
id | 1
name | 雪
mark | snow big snow
-[ record 3 ]——-
id | 2
name | 凤
mark | feng
time: 0.155 ms
testdb2=# rollback;
rollback
time: 0.847 ms
testdb2=# select * from t_user;
-[ record 1 ]——-
id | 3
name | 瑶瑶
mark | 哈哈一样f哈哈
-[ record 2 ]——-
id | 2
name | feng
mark | feng
-[ record 3 ]——-
id | 1
name | 雪
mark | snow big snow
time: 0.218 ms
testdb2=#
testdb2=# \x off;
expanded display is off.
testdb2=# select * from u_user;
error: relation “u_user” does not exist
line 1: select * from u_user;
^
time: 0.209 ms
testdb2=# select * from t_user;
id | name | mark
—-+——+—————
3 | 瑶瑶 | 哈哈一样f哈哈
2 | feng | feng
1 | 雪 | snow big snow
(3 rows)
time: 0.191 ms
testdb2=#
方法2：直接使用psql命令关闭自动提交的功能。autocommit必须大写。
\set autocommit off
19.在启动psql的命令行中加 “-e”参数，就可以把psql中各种以“\”开头的命令执行的实际sql打印出来。
如果在已运行的psql中显示某一个命令实际执行的sql，但显示完又想关闭这个功能，可以使用 “\set echo_hidden on|off”