数据库：PostgreSQL：psql使用指南

环境说明

本文使用的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的使用方式。