psql常用数据库命令大全

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”