psql的简单使用

psql的简单使用

1.使用“psql -l”命令可以查看数据库

psql -l

也可以进入psql的命令交互输入模式使用“\l”命令查看有哪些数据库

• 上面的查询结果中有一个叫“postgres”的数据库，这是默认postgreSQL安装完成后就有的一个数据库，
• 还有两个模板数据库：template0和template1。当用户创建数据库时，默认是从模板数据库“template1”克隆来的，所以通常我们可以定制template1数据库中的内容，如向template1中添加一些表后函数，这样后续创建的数据库就会继承template1中的内容，也会拥有这些表和函数。
• 而template0是一个最简化的模板库，如果创建数据库时明确指定从此数据库克隆，将创建出一个最简化的数据库。

2.查看表

\d

3.创建和连接数据库

创建数据库：
CREATE DATABASE testdb;
createdb testdb;

然后使用“\c testdb”命令连接到testdb数据库上：
\c testdb;

下面介绍psql连接数据库的常用的方法，命令格式如下：
psql -h <hostname or ip> -p <端口> [数据库名称] [用户名称]
psql -h 192.168.56.11 -p 5432 testdb

这些连接参数也可以通过环境变量指定，示例如下：
export PGDATABASE=testdb
export PGHOST=192.168.56.11
export PGPORT=5432
export PGUSER=postgres
然后运行psql，其运行结果与“psql -h 192.168.56.11 -p 5432
testdb postgres”的运行结果相同。

4.查看帮助

使用psql工具需要记住的第一个命令是“\h”，该命令用于查询SQL语句的语法.
如我们不知道如何用SQL语句创建用户，就可以执行“\h create user”命令来查询：
查看SQL语句创建用户命令

\h create user

使用“\h”命令可以查看各种SQL语句的语法，非常方便。

5.“\d”命令查看对象信息

该命令将显示每个匹配“pattern”（表、视图、索引、序列）的信息，包括对象中所有的列、各列的数据类型、表空间（如果不是默认的）和所有特殊属性（诸如“NOT NULL”或默认值等）等。

如果“\d”命令后什么都不带，将列出当前数据库中的所有表

\d

“\d”命令后面跟一个表名，表示显示这个表的结构定义，示例如下

\d t

“\d”命令也可以用于显示索引信息，示例如下：

\d t_pkey

“\d”命令后面的表名或索引名中也可以使用通配符，如“*”或“?”等，示例如下：

\d x?
\d t*

使用“\d+”命令可以显示比“\d”命令的执行结果更详细的信息，除了前面介绍的信息，还会显示所有与表的列关联的注释，以及表中出现的OID。示例如下：

\d+ t

匹配不同对象类型的“\d”命令如下：

• ·如果只想显示匹配的表，可以使用“\dt”命令。
• ·如果只想显示索引，可以使用“\di”命令。
• ·如果只想显示序列，可以使用“\ds”命令。
• ·如果只想显示视图，可以使用“\dv”命令。
• ·如果想显示函数，可以使用“\df”命令。

6.显示执行SQL语句的时间

可以用“\timing”命令，示例如下：

\timing on

7.列出所有schema

\dn

8.显示所有的表空间

\db

实际上，PostgreSQL中的表空间对应一个目录，放在这个表空间中的表，就是把表的数据文件放到该表空间下。

9.列出数据库中的所有角色或用户

使用“\du”或“\dg”命令

datawarehouse_test=# \du
                                   List of roles
      Role name       |                         Attributes                         
----------------------+------------------------------------------------------------
 app_user_read        | 
 app_user_write       | 
 monitor_system_stats | Cannot login
 postgres             | Superuser, Create role, Create DB, Replication, Bypass RLS

datawarehouse_test=# 
datawarehouse_test=# 
datawarehouse_test=# 
datawarehouse_test=# \dg
                                   List of roles
      Role name       |                         Attributes                         
----------------------+------------------------------------------------------------
 app_user_read        | 
 app_user_write       | 
 monitor_system_stats | Cannot login
 postgres             | Superuser, Create role, Create DB, Replication, Bypass RLS

datawarehouse_test=# 

“\du”和“\dg”命令等价。原因是，在PostgreSQL数据库中，用户和角色是不分的。

10.显示表的权限分配情况

\dp t

指定客户端字符集的命令

当客户端的字符编码与服务器不一致时，可能会出现乱码，可以使用“\encoding”命令指定客户端的字符编码，如使用“\encodinggbk;”命令设置客户端的字符编码为“gbk”；使用“\encodingutf8;”命令设置客户端的字符编码为“utf8”。

格式化输出的\pset命令

“\pset”命令的语法如下：

\pset [option [value] ]

带有内外边框的表格内容

根据命令后面“option”和“value”的不同可以设置很多种不同的输出格式，这里只介绍一些常用的用法。
默认情况下，psql中执行SQL语句后输出的内容是只有内边框的表格：
如果要像MySQL中一样输出带有内外边框的表格内容，可以用命令“\pset boder 2”来实现，示例如下：

testdb=# \pset border 2
Border style is 2.
testdb=# 
testdb=# select * from cities 
;
+--------+------------+-----------+
|  name  | population | elevation |
+--------+------------+-----------+
| 成都市 |       5000 |       800 |
| 成都市 |       5000 |       800 |
+--------+------------+-----------+

输出不带任何边框的内容

当然也可以用“\pset boder 0”命令输出不带任何边框的内容，
示例如下：

testdb=# \pset border 0
Border style is 0.
testdb=# select * from cities ;
 name  population elevation 
------ ---------- ---------
成都市       5000       800
成都市       5000       800
(2 rows)

综上所述，“\pset”命令设置边框的用法如下。

·\pset border 0：表示输出内容无边框。
·\pset border 1：表示输出内容只有内边框。
·\pset border 2：表示输出内容内外都有边框。

psql中默认的输出格式是“\pset border 1”。

输出逗号分隔或以Tab分隔的文本文件

testdb=# \pset format unaligned
Output format is unaligned.
testdb=# 
testdb=# 
testdb=# select * from cities 
;
name|population|elevation
成都市|5000|800
成都市|5000|800

设置分隔符

默认分隔符是“|”，我们可以用命令“\pset fieldsep”来设置分隔符，如改成Tab分隔符的方法如下：

testdb=# \pset fieldsep '\t'
Field separator is "	".
testdb=# select * from cities ;
name	population	elevation
成都市	5000	800
成都市	5000	800

将查询内容输出到文件中

实际使用时，我们需要把SQL命令输出到一个文件中，而不是屏幕上，这时可以用“\o”命令指定一个文件，然后再执行上面的SQL命令，执行结果就会输出到这个文件中，示例如下：

testdb=# \pset fieldsep ';'
Field separator is ";".
testdb=# \o out.txt
testdb=# select * from cities ;

而且很多时候
我们也不需要列头数据如“name|population|elevation”，这时就可以用“\t”命
令来删除这些信息：

postgres=# \pset fieldsep ';'
Field separator is ";".
postgres=# \o out.txt
postgres=# \t
Tuples only is on.
postgres=# select * from cities ;

将结果按行展示

使用“\x”命令可以把按行展示的数据变成按列展示，实现和mysql的\G效果，示例如下：

testdb=# \x
Expanded display is on.
testdb=# select * from cities ;
-[ RECORD 1 ]------
name       | 成都市
population | 5000
elevation  | 800
-[ RECORD 2 ]------
name       | 成都市
population | 5000
elevation  | 800

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

命令“\i <文件名>”用于执行存储在外部文件中的SQL语句或命令。示例如下：

postgres=# \c testdb 
You are now connected to database "testdb" as user "postgres".
testdb=# 
testdb=# 
testdb=# \i test.sql
Border style is 2.
+------------+--------+-------+------------------+
| product_no |  name  | price | discounted_price |
+------------+--------+-------+------------------+
|       1001 | oliver |   100 |               80 |
+------------+--------+-------+------------------+

[postgres@sit-mid ~]$ cat test.sql 
\pset border 2;
select * from products;

当然也可以在psql命令行中加上“-f ”来执行SQL脚本文件中的命令，示例如下：

psql -x -f getrunsql
其中命令行参数“-x”的作用相当于在psql交互模式下运行“\x”命令

编辑命令

编辑命令“\e”可以用于编辑文件，也可用于编辑系统中已存在的函数或视图定义，下面来举例说明此命令的使用方法。

输入“\e”命令后会调用一个编辑器，在Linux下通常是Vi，当“\e”命令不带任何参数时则是生成一个临时文件，前面执行的最后一条命令会出现在临时文件中，当编辑完成后退出编辑器并回到psql中时会立即执行该命令：

osdba=# \e ←-这里输入“\e”后，会进入Vi编辑器，退出Vi编辑器后就会
执行Vi中编辑的内容，然后下面就显示出执行的内容

在上面的操作中，我们在Vi中输入的内容为“select * from class where no=1;”，当退出Vi编辑器后，就会执行SQL语句“select * from class where no=1;”，这条SQL语句的内容在psql中是看不到的。

“\e”后面也可以指定一个文件名，但要求这个文件必须存在，否则会报错：

testdb=# \e test.sql
+------------+--------+-------+------------------+
| product_no |  name  | price | discounted_price |
+------------+--------+-------+------------------+
|       1001 | oliver |   100 |               80 |
+------------+--------+-------+------------------+

可以用“\ef”命令编辑一个函数的定义，如果“\ef”后面不跟任何参数，则会出现一个编辑函数的模板：

CREATE FUNCTION ( )
RETURNS
LANGUAGE
-- common options: IMMUTABLE STABLE STRICT SECURITY
DEFINER
AS $function$
$function$

如果“\ef”后面跟一个函数名，则函数定义的内容会出现在Vi编辑器中，当编辑完成后按“wq:”保存并退出，再输入“;”就会执行所创建函数的SQL语句。

同样输入“\ev”且后面不跟任何参数时，在Vi中会出现一个创建视图的模板：

CREATE VIEW AS
SELECT
-- something...

然后用户就可以在Vi中编辑这个创建视图的SQL语句，编辑完成后，保存并退出，再输入分号“;”，就会执行所创建视图的SQL语句。
也可以编辑已存在的视图的定义，只需在“\ev”命令后面跟视图的名称即可。

“\ef”和“\ev”命令可以用于查看函数或视图的定义，当然用户需要注意，退出Vi后，要在psql中输入“\reset”来清除psql的命令缓冲区，防止误执行创建函数和视图的SQL语句，示例如下：

postgres=# \ev vm_class ←-在这里进入Vi后，在Vi中用":q"退出
No changes
postgres-# \reset ←-在这里不要忘了输入"\reset"命令清除psql缓冲
区
Query buffer reset (cleared).

输出信息的“\echo”命令

“\echo”命令用于输出一行信息，示例如下：

osdba=# \echo hello word
hello word

此命令通常用于在使用.sql脚本的文件中输出提示信息。
比如，某文件“a.sql”有如下内容

\echo =========================================
select * from x1;
\echo =========================================

运行a.sql脚本：

osdba=# \i a.sql
=========================================
id | name
----+--------
1 | aaaaaa
2 | bbbbbb
3 | cccccc
(3 rows)
=========================================
osdba=#

上面使用“\echo”命令输出由“=”组成的分隔线。

其他命令

更多其他的命令可以用“?”命令来显示，示例如下：

psql的使用技巧

本节将介绍psql最常用的使用技巧，如历史命令和补全技巧、关闭自动提交功能、获得快捷命令实际的SQL，以便学习数据库的系统表等。

历史命令与补全功能

可以使用上下方向键把以前使用过的命令或SQL语句调出来，连续单击两次Tab键表示把命令补全或给出输入提示：

自动提交技巧

需要特别注意的是，在psql中事务是自动提交的，比如，执行完一条DELETE或UPDATE语句后，事务就会自动提交，如果不想让事务自动提交，方法有两种。
方法一：运行“begin;”命令，然后执行DML语句，最后再执行
commit或rollback语句，示例如下

osdba=# begin;
BEGIN
osdba=# update x1 set name='xxxxx' where id=1;
UPDATE 1
osdba=# select * from x1;
id | name
----+--------
2 | bbbbbb
3 | cccccc
1 | xxxxx
(3 rows)
osdba=# rollback;
ROLLBACK
osdba=# select * from x1;
id | name
----+--------
1 | aaaaaa
2 | bbbbbb
3 | cccccc
(3 rows)

方法二：直接使用psql中的命令关闭自动提交功能。

\set AUTOCOMMIT off

注意：这个命令中的“AUTOCOMMIT”是大写的，不能使用小写，如果使用小写，虽不会报错，但会导致关闭自动提交的操作无效。

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

在启动psql的命令行中加上“-E”参数，就可以把psql中各种以“\”开头的命令执行的实际SQL语句打印出来，示例如下：

[postgres@sit-mid ~]$ psql -E testdb
psql (16.6)
Type "help" for help.

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 't' THEN 'TOAST table' 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.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | capitals | table | postgres
 public | cities   | table | postgres
 public | products | table | postgres
(3 rows)

如果在已运行的psql中显示了某个命令实际执行的SQL语句后又想关闭此功能，该怎么办？这时可以使用“\set ECHO_HIDDEN on|off”命令，示例如下：

通过分析这个方法输出的SQL语句，可以让我们快速学习PostgreSQL的系统表原理。