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 <hostname or ip> -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 <table> 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”本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
