数据库：PostgreSQL：psql使用指南

痛定思痛。 2022-12-02 13:38 603阅读 0赞

![在这里插入图片描述][2020083013073660.jpeg_pic_center]

### 目录 ###

*  环境说明
 *  常用功能
 *   *  数据库连接
     *  版本确认
     *  查看数据库：\\l
     *  查看schema
     *  查看用户du和角色dg
     *  查看表空间：db
     *  查看权限设定：dp
     *  查看客户端编码：encoding
     *  输出信息：echo
     *  查看连接信息：conninfo
     *  查看扩展：dx
     *  设定或取消显示执行时间：timing
     *  查看当前数据库：c
     *  查看SQL命令帮助：h
     *  查看内部命令帮助：?
     *  查看用户详细信息
     *  查询所有表的信息
     *  查看具体表信息
     *  查看表结构：d 表名
     *  切换数据库 ：c 数据库名
     *  其他
 *  总结

--------------------

# 环境说明 #

本文使用的PostgreSQL为：12.4，详细介绍和环境准备可参看：

*  [https://liumiaocn.blog.csdn.net/article/details/108304605][https_liumiaocn.blog.csdn.net_article_details_108304605]

注意：上述文章中未将端口号暴露出来，使用时需要加-p选项

liumiaocn:postgres liumiao$ docker run --name postgres -p 5432:5432 -v ${PWD}/data:/var/lib/postgresql/data -e POSTGRES_PASSWORD=liumiaocn -d postgres:12.4-alpine
    9e7d7c6a19fd53ecfe2221083c27083501296c6436d0c7aec15b3612cb18374b
    liumiaocn:postgres liumiao$ docker ps
    CONTAINER ID        IMAGE                  COMMAND                  CREATED             STATUS              PORTS                    NAMES
    9e7d7c6a19fd        postgres:12.4-alpine   "docker-entrypoint.s…"   2 seconds ago       Up 1 second         0.0.0.0:5432->5432/tcp   postgres
    liumiaocn:postgres liumiao$

客户端环境的准备可参看：

*  [https://liumiaocn.blog.csdn.net/article/details/108314207][https_liumiaocn.blog.csdn.net_article_details_108314207]

--------------------

liumiaocn:postgres liumiao$ which psql
    /usr/local/Cellar/libpq/12.4/bin/psql
    liumiaocn:postgres liumiao$ psql --version
    psql (PostgreSQL) 12.4
    liumiaocn:postgres liumiao$

--------------------

# 常用功能 #

## 数据库连接 ##

psql命令进行数据库连接的使用格式如下所示

> 使用格式：psql -h <机器名或者IP地址> -p <端口号> \[数据库名称\] \[用户名称\]

注意：数据安装时，会自动创建一个和数据库同名的管理用户，比如官方镜像缺省会创建一个名为postgres的数据库，同时操作系统的postgres用户和数据库中的postgres用户都会被创建，在数据库服务所在的操作系统中进行连接，进行的是操作系统认证，所以可以不需要用户名和密码，其他客户端则需要。另外也可以通过环境变量或者pg\_hba.conf 来修改设定。

*  在数据库服务所在机器（非postgres用户）

liumiaocn:postgres liumiao$ docker exec -it postgres sh
    / # psql -U postgres
    psql (12.4)
    Type "help" for help.
    
    postgres=#

*  不指定用户则会出错（操作系统认证）

/ # psql
    psql: error: could not connect to server: FATAL:  role "root" does not exist
    / #

*  在数据库服务所在机器（postgres用户）, 不指定也没有问题（操作系统认证）

/ # su - postgres
    9e7d7c6a19fd:~$ id
    uid=70(postgres) gid=70(postgres) groups=70(postgres),70(postgres)
    9e7d7c6a19fd:~$ psql
    psql (12.4)
    Type "help" for help.
    
    postgres=# select user;
       user   
    ----------
     postgres
    (1 row)
    
    postgres=#

*  外部连接，全部需要指定，同时需要输入密码

liumiaocn:postgres liumiao$ psql -h localhost -p 5432 postgres postgres
    Password for user postgres: 
    psql (12.4)
    Type "help" for help.
    
    postgres=# select user;
       user   
    ----------
     postgres
    (1 row)
    
    postgres=#

## 版本确认 ##

*  也可以通过内置的psql确认客户端版本

/ # psql --version
    psql (PostgreSQL) 12.4
    / #

*  或者通过show命令确认(连接之后才能使用，注意提示符)

postgres=# show server_version_num;
     server_version_num 
    --------------------
     120004
    (1 row)
    
    postgres=#

*  或者使用SELECT语句

postgres=# SELECT current_setting('server_version_num');
     current_setting 
    -----------------
     120004
    (1 row)
    
    postgres=#

*  或者使用select version()语句

postgres=# select version();
                                            version                                        
    ---------------------------------------------------------------------------------------
     PostgreSQL 12.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.3.0) 9.3.0, 64-bit
    (1 row)
    
    postgres=#

--------------------

## 查看数据库：\\l ##

> 注意事项: psql中的所有命令行都以\\开头

## 查看schema ##

postgres=# \dn
      List of schemas
      Name  |  Owner   
    --------+----------
     public | postgres
    (1 row)
    
    postgres=#

## 查看用户du和角色dg ##

postgres=# \du
                                       List of roles
     Role name |                         Attributes                         | Member of 
    -----------+------------------------------------------------------------+-----------
     postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
    
    postgres=# \dg
                                       List of roles
     Role name |                         Attributes                         | Member of 
    -----------+------------------------------------------------------------+-----------
     postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
    
    postgres=#

## 查看表空间：db ##

## 查看权限设定：dp ##

postgres=# \dp
                                Access privileges
     Schema | Name | Type | Access privileges | Column privileges | Policies 
    --------+------+------+-------------------+-------------------+----------
    (0 rows)
    
    postgres=# \z
                                Access privileges
     Schema | Name | Type | Access privileges | Column privileges | Policies 
    --------+------+------+-------------------+-------------------+----------
    (0 rows)
    
    postgres=#

## 查看客户端编码：encoding ##

postgres=# \encoding
    UTF8
    postgres=#

注：设定为encoding 字符编码名称

## 输出信息：echo ##

postgres-# \echo "Hello World"
    "Hello World"
    postgres-#

也可以使用select语句

postgres=# select 'Hello ' || 'World';
      ?column?   
    -------------
     Hello World
    (1 row)
    
    postgres=#

## 查看连接信息：conninfo ##

postgres=# \conninfo
    You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
    postgres=#

## 查看扩展：dx ##

也可以直接查表

## 设定或取消显示执行时间：timing ##

postgres=# select 3*7;
     ?column? 
    ----------
           21
    (1 row)
    
    postgres=# \timing
    Timing is on.
    postgres=# select 3*7;
     ?column? 
    ----------
           21
    (1 row)
    
    Time: 1.235 ms
    postgres=#

缺省为不显示执行时间

## 查看当前数据库：c ##

postgres=# \c
    You are now connected to database "postgres" as user "postgres".
    postgres=#

或者

postgres=# select current_database();
     current_database 
    ------------------
     postgres
    (1 row)
    
    postgres=#

## 查看SQL命令帮助：h ##

postgres=# \h
    Available help:
      ABORT                            CHECKPOINT                       CREATE USER                      DROP TRIGGER
      ALTER AGGREGATE                  CLOSE                            CREATE USER MAPPING              DROP TYPE
      ALTER COLLATION                  CLUSTER                          CREATE VIEW                      DROP USER
      ALTER CONVERSION                 COMMENT                          DEALLOCATE                       DROP USER MAPPING
      ALTER DATABASE                   COMMIT                           DECLARE                          DROP VIEW
      ALTER DEFAULT PRIVILEGES         COMMIT PREPARED                  DELETE                           END
      ALTER DOMAIN                     COPY                             DISCARD                          EXECUTE
      ALTER EVENT TRIGGER              CREATE ACCESS METHOD             DO                               EXPLAIN
      ALTER EXTENSION                  CREATE AGGREGATE                 DROP ACCESS METHOD               FETCH
      ALTER FOREIGN DATA WRAPPER       CREATE CAST                      DROP AGGREGATE                   GRANT
      ALTER FOREIGN TABLE              CREATE COLLATION                 DROP CAST                        IMPORT FOREIGN SCHEMA
      ALTER FUNCTION                   CREATE CONVERSION                DROP COLLATION                   INSERT
      ALTER GROUP                      CREATE DATABASE                  DROP CONVERSION                  LISTEN
      ALTER INDEX                      CREATE DOMAIN                    DROP DATABASE                    LOAD
      ALTER LANGUAGE                   CREATE EVENT TRIGGER             DROP DOMAIN                      LOCK
      ALTER LARGE OBJECT               CREATE EXTENSION                 DROP EVENT TRIGGER               MOVE
      ALTER MATERIALIZED VIEW          CREATE FOREIGN DATA WRAPPER      DROP EXTENSION                   NOTIFY
      ALTER OPERATOR                   CREATE FOREIGN TABLE             DROP FOREIGN DATA WRAPPER        PREPARE
      ALTER OPERATOR CLASS             CREATE FUNCTION                  DROP FOREIGN TABLE               PREPARE TRANSACTION
      ALTER OPERATOR FAMILY            CREATE GROUP                     DROP FUNCTION                    REASSIGN OWNED
      ALTER POLICY                     CREATE INDEX                     DROP GROUP                       REFRESH MATERIALIZED VIEW
      ALTER PROCEDURE                  CREATE LANGUAGE                  DROP INDEX                       REINDEX
      ALTER PUBLICATION                CREATE MATERIALIZED VIEW         DROP LANGUAGE                    RELEASE SAVEPOINT
      ALTER ROLE                       CREATE OPERATOR                  DROP MATERIALIZED VIEW           RESET
      ALTER ROUTINE                    CREATE OPERATOR CLASS            DROP OPERATOR                    REVOKE
      ALTER RULE                       CREATE OPERATOR FAMILY           DROP OPERATOR CLASS              ROLLBACK
      ALTER SCHEMA                     CREATE POLICY                    DROP OPERATOR FAMILY             ROLLBACK PREPARED
      ALTER SEQUENCE                   CREATE PROCEDURE                 DROP OWNED                       ROLLBACK TO SAVEPOINT
    postgres=#

## 查看内部命令帮助：? ##

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 [FILE] or ;         execute query (and send results to file or |pipe)
      \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 [FILE]             as \g, but forces expanded output mode
      \q                     quit psql
      \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
    postgres=#

## 查看用户详细信息 ##

## 查询所有表的信息 ##

postgres=# select tablename from pg_tables;
            tablename        
    -------------------------
     pg_statistic
     pg_type
     pg_foreign_server
     pg_authid
     pg_statistic_ext_data
     pg_user_mapping
     pg_subscription
     pg_attribute
     pg_proc
     pg_class
     pg_attrdef
     pg_constraint
     pg_inherits
     pg_index
     pg_operator
     pg_opfamily
     pg_opclass
     pg_am
     pg_amop
     pg_amproc
     pg_language
     pg_largeobject_metadata
     pg_aggregate
     pg_largeobject
     pg_statistic_ext
     pg_rewrite
     pg_trigger
    postgres=#

## 查看具体表信息 ##

## 查看表结构：d 表名 ##

## 切换数据库 ：c 数据库名 ##

## 其他 ##

*  dt：列出表
 *  di：列出索引
 *  dv：列出视图
 *  dS：列出系统表

# 总结 #

这篇文章总结了PostgreSQL客户端psql的使用方式。

[2020083013073660.jpeg_pic_center]: /images/20221123/b34bc49356c142ebb894c83371e20408.png
[https_liumiaocn.blog.csdn.net_article_details_108304605]: https://liumiaocn.blog.csdn.net/article/details/108304605
[https_liumiaocn.blog.csdn.net_article_details_108314207]: https://liumiaocn.blog.csdn.net/article/details/108314207