数据库：PostgreSQL：psql使用指南

程序员文章站 2022-03-15 16:34:08

...

环境说明

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

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

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中的所有命令行都以\开头

liumiaocn:postgres liumiao$ psql -h localhost -p 5432 postgres postgres
Password for user postgres: 
psql (12.4)
Type "help" for help.

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

postgres=#

查看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

postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)

postgres=#

查看权限设定：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

postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

postgres=#

也可以直接查表

postgres=# select * from pg_extension;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 13441 | plpgsql |       10 |           11 | f              | 1.0        |           | 
(1 row)

postgres=#

设定或取消显示执行时间：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 * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |          | 
(1 row)

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=#

查看具体表信息

postgres=# select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |          | 
(1 row)

postgres=#

查看表结构：d 表名

postgres=# \d pg_user;
                        View "pg_catalog.pg_user"
    Column    |           Type           | Collation | Nullable | Default 
--------------+--------------------------+-----------+----------+---------
 usename      | name                     |           |          | 
 usesysid     | oid                      |           |          | 
 usecreatedb  | boolean                  |           |          | 
 usesuper     | boolean                  |           |          | 
 userepl      | boolean                  |           |          | 
 usebypassrls | boolean                  |           |          | 
 passwd       | text                     |           |          | 
 valuntil     | timestamp with time zone |           |          | 
 useconfig    | text[]                   | C         |          | 

postgres=#

切换数据库：c 数据库名

postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

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

其他

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

postgres=# \dt
Did not find any relations.
postgres=# \di
Did not find any relations.
postgres=# \dv
Did not find any relations.
postgres=# \dS
                        List of relations
   Schema   |              Name               | Type  |  Owner   
------------+---------------------------------+-------+----------
 pg_catalog | pg_aggregate                    | table | postgres
 pg_catalog | pg_am                           | table | postgres
 pg_catalog | pg_amop                         | table | postgres
 pg_catalog | pg_amproc                       | table | postgres
 pg_catalog | pg_attrdef                      | table | postgres
 pg_catalog | pg_attribute                    | table | postgres
 pg_catalog | pg_auth_members                 | table | postgres
 pg_catalog | pg_authid                       | table | postgres
 pg_catalog | pg_available_extension_versions | view  | postgres
 pg_catalog | pg_available_extensions         | view  | postgres
 pg_catalog | pg_cast                         | table | postgres
 pg_catalog | pg_class                        | table | postgres
 pg_catalog | pg_collation                    | table | postgres
 pg_catalog | pg_config                       | view  | postgres
 pg_catalog | pg_constraint                   | table | postgres
 pg_catalog | pg_conversion                   | table | postgres
 pg_catalog | pg_cursors                      | view  | postgres
 pg_catalog | pg_database                     | table | postgres
 pg_catalog | pg_db_role_setting              | table | postgres
 pg_catalog | pg_default_acl                  | table | postgres
 pg_catalog | pg_depend                       | table | postgres
 pg_catalog | pg_description                  | table | postgres
 pg_catalog | pg_enum                         | table | postgres
 pg_catalog | pg_event_trigger                | table | postgres
 pg_catalog | pg_extension                    | table | postgres
 pg_catalog | pg_file_settings                | view  | postgres
postgres=#

总结

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

上一篇： win7出现错误1068，无法上网怎么办

下一篇： linux内存用光

数据库：PostgreSQL：psql使用指南

目录

环境说明

常用功能

数据库连接

版本确认

查看数据库：\l

查看schema

查看用户du和角色dg

查看表空间：db

查看权限设定：dp

查看客户端编码：encoding

输出信息：echo

查看连接信息：conninfo

查看扩展：dx

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

查看当前数据库：c

查看SQL命令帮助：h

查看内部命令帮助：?

查看用户详细信息

查询所有表的信息

查看具体表信息

查看表结构：d 表名

切换数据库：c 数据库名

其他

总结

C++连接Postgresql数据库进行数据预筛选（代码教程）

Navicat怎么连接PostgreSQL数据库?Navicat Premium新建连接PostgreSQL数据库教程

MySQL数据库与PostgreSQL数据库比较哪个数据库更好些?

PHP实现从PostgreSQL数据库检索数据分页显示及根据条件查找数据示例

基于Python3的接口自动化总结（六）——PostgreSQL数据库

使用python将mdb数据库文件导入postgresql数据库示例

Python使用PyGreSQL操作PostgreSQL数据库教程

PostgreSQL 数据库跨版本升级常用方案解析

PostgreSQL数据库所有的等待事件讲解

postgresql数据库部署

数据库：PostgreSQL：psql使用指南

目录

环境说明

常用功能

数据库连接

版本确认

查看数据库：\l

查看schema

查看用户du和角色dg

查看表空间：db

查看权限设定：dp

查看客户端编码：encoding

输出信息：echo

查看连接信息：conninfo

查看扩展：dx

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

查看当前数据库：c

查看SQL命令帮助：h

查看内部命令帮助：?

查看用户详细信息

查询所有表的信息

查看具体表信息

查看表结构：d 表名

切换数据库 ：c 数据库名

其他

总结

C++连接Postgresql数据库进行数据预筛选（代码教程）

Navicat怎么连接PostgreSQL数据库?Navicat Premium新建连接PostgreSQL数据库教程

MySQL数据库与PostgreSQL数据库比较 哪个数据库更好些?

PHP实现从PostgreSQL数据库检索数据分页显示及根据条件查找数据示例

基于Python3的接口自动化总结（六）——PostgreSQL数据库

使用python将mdb数据库文件导入postgresql数据库示例

Python使用PyGreSQL操作PostgreSQL数据库教程

PostgreSQL 数据库跨版本升级常用方案解析

PostgreSQL数据库所有的等待事件讲解

postgresql数据库部署

切换数据库：c 数据库名

MySQL数据库与PostgreSQL数据库比较哪个数据库更好些?