1.psql简单使用

进入到postgresql 命令行

[root@node1 ~]# su - postgresLast login: Sat Feb 27 03:45:04 GMT 2021 on pts/1-bash-4.2$ psqlpsql (13.2)Type "help" for help.查看有哪些数据库postgres=# \l                                  List of databases   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges-----------+----------+----------+-------------+-------------+----------------------- postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +           |          |          |             |             | postgres=CTc/postgres template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +           |          |          |             |             | postgres=CTc/postgres(3 rows)postgres=#或者-bash-4.2$ psql -l                                  List of databases   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges-----------+----------+----------+-------------+-------------+----------------------- postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +           |          |          |             |             | postgres=CTc/postgres template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +           |          |          |             |             | postgres=CTc/postgres(3 rows)

安装好后，默认会有一个叫postgres的数据库，还有2个模板数据库template0和template1。当用户在建数据库时，默认是从模板数据库template1克隆出来的，所以通常template1数据库中的内容，如往template1中添加一些表和函数，这样后续创建的数据库就会继承template1中的内容，也会拥有这些表和函数。而template0是一个最简化的模板库，创建数据库时，如果明确指定从此数据库中继承，将创建一个最简化的数据库。

2.创建数据库

创建数据库testdbpostgres=# create database testdb;CREATE DATABASE连接数据库testdbpostgres=# \c testdb;You are now connected to database "testdb" as user "postgres".testdb=#创建表testdb=# create table t(id int primary key, name varchar(30));CREATE TABLE查看表属性testdb=# \d        List of relations Schema | Name | Type  |  Owner--------+------+-------+---------- public | t    | table | postgres(1 row)

3.数据库连接

连接命令格式$ psql -h <hostname or ip> -p <端口> [数据库名称] [用户名]$ psql -h 127.0.0.1 -p 5432 testdb postgresPassword for user postgres:psql: error: FATAL:  password authentication failed for user "postgres"这里会有报错，需要进行下面的操作1.添加/var/lib/pgsql/13/data/pg_hba.conf，让其他客户端可以连接这个库host    all             all             0.0.0.0/0            trust其中， Method要改为trust重启postgresql生效#systemctl restart postgresql-132.修改postgres密码[root@node1 ~]# su - postgres-bash-4.2$ psqlpostgres=# alter role postgres with password 'postgres'3.连接测试[root@node1 ~]# su - postgresLast login: Sat Feb 27 13:10:47 GMT 2021 on pts/0-bash-4.2$ psql -h 127.0.0.1 -p 5432 testdb postgresPassword for user postgres:psql (13.2)Type "help" for help.testdb=# \d        List of relations Schema | Name | Type  |  Owner--------+------+-------+---------- public | t    | table | postgres(1 row)testdb=#通过变量设置连接数据库export PGDATABASE=testdbexport PGHOST=127.0.0.1export PGPORT=5432export PGUSER=postgres然后运行psql即可，效果与psql -h 127.0.0.1 -p 5432 testdb postgres一样。

4.psql的常用命令

4.1 \d命令

\d [pattern]

\d [pattern]+

显示每个匹配关系(表、视图、索引、序列)的信息，包括对象所有的列、它们的类型、表空间（如果不是默认的）和任何特殊属性（NOT NULL或默认值）等。

1.列出当前数据库中的所有表testdb=# \d        List of relations Schema | Name | Type  |  Owner--------+------+-------+---------- public | t    | table | postgres public | t1   | table | postgres public | t2   | table | postgres public | x1   | table | postgres public | x2   | table | postgres(5 rows)2. \d 表名1，显示这个表的结构定义testdb=# \d t                        Table "public.t" Column |         Type          | Collation | Nullable | Default--------+-----------------------+-----------+----------+--------- id     | integer               |           | not null | name   | character varying(30) |           |          |Indexes:    "t_pkey" PRIMARY KEY, btree (id)    3. 显示表的索引信息testdb=# \d t_pkey         t_ 为表名t        Index "public.t_pkey" Column |  Type   | Key? | Definition--------+---------+------+------------ id     | integer | yes  | idprimary key, btree, for table "public.t"testdb=# \d x3_pkey        Index "public.x3_pkey" Column |  Type   | Key? | Definition--------+---------+------+------------ id     | integer | yes  | idprimary key, btree, for table "public.x3"4.显示通配符表 * ?* 匹配多个字符，表和索引? 匹配1个字符，表testdb=# \d t*                        Table "public.t" Column |         Type          | Collation | Nullable | Default--------+-----------------------+-----------+----------+--------- id     | integer               |           | not null | name   | character varying(30) |           |          |Indexes:    "t_pkey" PRIMARY KEY, btree (id)                 Table "public.t1" Column |  Type   | Collation | Nullable | Default--------+---------+-----------+----------+--------- c1     | integer |           |          |                 Table "public.t2" Column |  Type   | Collation | Nullable | Default--------+---------+-----------+----------+--------- c1     | integer |           |          |        Index "public.t_pkey" Column |  Type   | Key? | Definition--------+---------+------+------------ id     | integer | yes  | idprimary key, btree, for table "public.t"testdb=# \d t?                 Table "public.t1" Column |  Type   | Collation | Nullable | Default--------+---------+-----------+----------+--------- c1     | integer |           |          |                 Table "public.t2" Column |  Type   | Collation | Nullable | Default--------+---------+-----------+----------+--------- c1     | integer |           |          |5. \d+ 显示更加详细的信息testdb=# \d+                           List of relations Schema | Name | Type  |  Owner   | Persistence |  Size   | Description--------+------+-------+----------+-------------+---------+------------- public | t    | table | postgres | permanent   | 0 bytes | public | t1   | table | postgres | permanent   | 0 bytes | public | t2   | table | postgres | permanent   | 0 bytes | public | x1   | table | postgres | permanent   | 0 bytes | public | x2   | table | postgres | permanent   | 0 bytes | public | x3   | table | postgres | permanent   | 0 bytes |(6 rows)6. 匹配不同类型的\d命令只显示匹配的表， \dt命令只显示匹配的索引，\di命令只显示序列，\ds命令只显示视图，\dv命令只显示函数， \df命令7.显示SQL已执行的时间， 可以用\timingtestdb=# \timing onTiming is on.testdb=# select * from t; id | name----+------(0 rows)Time: 0.787 ms8.列出所有的schema命令 \dntestdb=# \dn  List of schemas  Name  |  Owner--------+---------- public | postgres(1 row)9.显示所有的表空间 \dbtestdb=# \db       List of tablespaces    Name    |  Owner   | Location------------+----------+---------- pg_default | postgres | pg_global  | postgres |(2 rows)10. 列出数据库中的所有角色或用户， \du 或 \dgtestdb=# \dg                                   List of roles Role name |                         Attributes                         | Member of-----------+------------------------------------------------------------+----------- postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}testdb=# \du                                   List of roles Role name |                         Attributes                         | Member of-----------+------------------------------------------------------------+----------- postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}  11. 显示表的权限分配情况 testdb=# \dp t                            Access privileges Schema | Name | Type  | Access privileges | Column privileges | Policies--------+------+-------+-------------------+-------------------+---------- public | t    | table |                   |                   |(1 row)

4.2 指定字符集编译的命令

当客户端的字符编码与服务器的不一样时，可能会显示乱码，可以使用\encoding命令指定客户端的字符编码

设置客户端的字符编码为gbk

testdb=# \encoding gbk

设置客户端的字符编码为utf8

testdb=# \encoding utf8

4.3 \pset命令

\pset命令用于设置输出的格式，具体如下

• \pset border 0: 表示输出内容无边框
• \pset border 1: 表示边框只在内部
• \pset border 2: 表示内外都有边框

testdb=# create table e(id int,name varchar(20));CREATE TABLEtestdb=# insert into e values(1,'jason');testdb=# insert into e values(2,'darcy');testdb=# insert into e values(3,'apple');testdb=# select * from e;id name-- ----- 1 jason 2 darcy 3 apple(3 rows)testdb=# \pset border 1;Border style is 1.testdb=# select * from e; id | name----+-------  1 | jason  2 | darcy  3 | apple(3 rows)testdb=# \pset border 2;Border style is 2.testdb=# select * from e;+----+-------+| id | name  |+----+-------+|  1 | jason ||  2 | darcy ||  3 | apple |+----+-------+(3 rows)

4.4 \x 命令

表中每一行的每列数据都拆分为单行展示

testdb=# \xExpanded display is on.testdb=# select * from pg_stat_activity;+-[ RECORD 1 ]-----+---------------------------------+| datid            |                                 || datname          |                                 || pid              | 13938                           || leader_pid       |                                 || usesysid         | 10                              || usename          | postgres                        || application_name |                                 || client_addr      |                                 || client_hostname  |                                 || client_port      |                                 || backend_start    | 2021-02-27 13:16:32.92436+00    || xact_start       |                                 || query_start      |                                 || state_change     |                                 || wait_event_type  | Activity                        || wait_event       | LogicalLauncherMain             || state            |                                 || backend_xid      |                                 || backend_xmin     |                                 || query            |                                 || backend_type     | logical replication launcher    |+-[ RECORD 2 ]-----+---------------------------------+| datid            |                                 || datname          |                                 || pid              | 13936                           || leader_pid       |                                 || usesysid         |                                 || usename          |                                 || application_name |                                 || client_addr      |                                 || client_hostname  |                                 || client_port      |                                 || backend_start    | 2021-02-27 13:16:32.924837+00   || xact_start       |                                 || query_start      |                                 || state_change     |                                 || wait_event_type  | Activity                        || wait_event       | AutoVacuumMain                  || state            |                                 || backend_xid      |                                 || backend_xmin     |                                 || query            |                                 || backend_type     | autovacuum launcher   

如果一行数据有太多的拆行，显示不下，就可以使用这里介绍的\x命令。这与MySQL中命令后\G功能类似

再执行\x ,即可关闭。

4.5 执行存储在外部文件中的SQL命令

命令 \i <文件名> 执行存储在外部文件中的sql语句或命令

-bash-4.2$ cat sele_x.sqlselect * from e;select * from t;-bash-4.2$ psqlpsql (13.2)Type "help" for help.postgres=# \c testdb;You are now connected to database "testdb" as user "postgres".testdb=# \i sele_x.sql id | name----+-------  1 | jason  2 | darcy  3 | apple(3 rows) id | name----+------(0 rows)testdb=# \q

也可以psql 命令行加 "-f <filename>" 来执行SQL脚本文件中的命令

-bash-4.2$ psql -h 127.0.0.1 -p 5432 testdb postgres -x -f sele_x.sqlPassword for user postgres:-[ RECORD 1 ]id   | 1name | jason-[ RECORD 2 ]id   | 2name | darcy-[ RECORD 3 ]id   | 3name | apple(0 rows)

4.6 显示信息的命令

\echo 命令用于输出一行信息

testdb=# \echo hello worldhello world

通常在脚本中使用， 1.sql

$vi 1.sql\echo ===============select * from e;\echo ===============-bash-4.2$ -bash-4.2$ psqlpsql (13.2)postgres=# \c testdb;You are now connected to database "testdb" as user "postgres".testdb=# \i 1.sql;=============== id | name----+-------  1 | jason  2 | darcy  3 | apple(3 rows)===============

4.7更多的命令

postgres=# \?General  \copyright             show PostgreSQL usage and distribution terms  \crosstabview [COLUMNS] execute query and display results in crosstab  \errverbose            show most recent error message at maximum verbosity  \g [(OPTIONS)] [FILE]  execute query (and send results to file or |pipe);                         \g with no arguments is equivalent to a semicolon  \gdesc                 describe result of query, without executing it  \gexec                 execute query, then execute each value in its result  \gset [PREFIX]         execute query and store results in psql variables  \gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode  \q                     quit psql  \watch [SEC]           execute query every SEC secondsHelp  \? [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 commandsQuery 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 fileInput/Output  \copy ...              perform SQL COPY with data stream to the client host  \echo [-n] [STRING]    write string to standard output (-n for no newline)  \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 [-n] [STRING]   write string to \o output stream (-n for no newline)  \warn [-n] [STRING]    write string to standard error (-n for no newline)

5.psql的使用技巧与注意事项

5.1历史命令与补全的功能

可以使用上下键把以前使用过的命令或SQL语句调出来，连续按两个tab键表示把命令补全或给出提示输入:

postgres=# \c testdbYou are now connected to database "testdb" as user "postgres".testdb=# \d    <-- 连续按两个tab键e                    pg_toast.            t1                   x1                   x3_pkeyinformation_schema.  public.              t2                   x2pg_catalog.          t                    t_pkey               x3testdb=# \d t  <-- 连续按两个tab键t       t1      t2      t_pkeytestdb=# \d t

5.2自动提交方面的技巧

在psql中事务是自动提交的。比如，执行完delete或update语句后，事务就自动提交了，如果不想自动提交，有两种方法。

1. 执行begin命令，然后执行dml语句，最后再执行commit或rollback语句
2. 直接使用\set AUTOCOMMIT off , AUTOCOMMIT必须大写，不能小写

5.3如何得到psql中命令实际执行的SQL

如果在启动psql的命令中加入"-E"参数，就可以把psql中各种以""开头的命令执行的实际SQL打印出来

-bash-4.2$ psql -E postgrespsql (13.2)Type "help" for help.postgres=# \c testdb;You are now connected to database "testdb" as user "postgres".testdb=# \d********* QUERY **********SELECT n.nspname as "Schema",  c.relname as "Name",  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"FROM pg_catalog.pg_class c     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespaceWHERE c.relkind IN ('r','p','v','m','S','f','')      AND n.nspname <> 'pg_catalog'      AND n.nspname <> 'information_schema'      AND n.nspname !~ '^pg_toast'  AND pg_catalog.pg_table_is_visible(c.oid)ORDER BY 1,2;**************************        List of relations Schema | Name | Type  |  Owner--------+------+-------+---------- public | e    | table | postgres public | t    | table | postgres public | t1   | table | postgres public | t2   | table | postgres public | x1   | table | postgres public | x2   | table | postgres public | x3   | table | postgres(7 rows)

如果想在已运行psql中显示某一个命令实际执行的SQL，但显示完后又想关闭这个功能，使用 \set ECHO_HIDDEN on|off

-bash-4.2$ psql postgrespsql (13.2)Type "help" for help.postgres=# \dn  List of schemas  Name  |  Owner--------+---------- public | postgres(1 row)postgres=# \set ECHO_HIDDEN onpostgres=# \dn********* QUERY **********SELECT n.nspname AS "Name",  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"FROM pg_catalog.pg_namespace nWHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'ORDER BY 1;**************************  List of schemas  Name  |  Owner--------+---------- public | postgres(1 row)postgres=# \set ECHO_HIDDEN offpostgres=# \dn  List of schemas  Name  |  Owner--------+---------- public | postgres(1 row)