首页

psql工具

— 数据库原理及应用实验 —

Haifeng Xu

(hfxu@yzu.edu.cn)

教材: Regina Obe Leo Hsu 著, 丁奇鹏 译《PostgreSQL即学即用》

References: https://www.sjkjc.com/postgresql-ref/array_to_string/

目录

psql

psql

psql 是 PostgreSQL 自带的一个不可或缺的命令行执行工具, 其用途除了执行 SQL 这个基本功能外, 还可用于

• 自动化执行脚本;
• 导入导出数据;
• 恢复表数据;
• 执行数据库管理任务;
• 报表生成器;
• 等等.

psql 的两种操作模式

psql 有两种操作模式: 交互模式与非交互模式.

交互模式

在操作系统的命令行界面输入 psql (可以加上一些选项等)并回车, 从操作系统提示符切换到 psql 提示符后, 就进入了 psql 的交互模式界面. 例如:

PS D:\work\cs\Database\postgres> psql
用户 postgres 的口令：

psql (17.6)
输入 "help" 来获取帮助信息.

postgres=#

此时就可以执行命令了, 注意要输入分号作为命令结束标记. 要是没有输入分号, 则 psql 会任务输入尚未结束, 会在换行后等待继续输入.

非交互模式

如果在操作系统命令提示符下执行 psql, 后面给其传送一个脚本文件, 即以选项的形式指定要执行的脚本, 则psql 运行非交互模式. 例如:

psql -f some_script_file

脚本中可以含有任意数量的 SQL 语句和 psql 语句.

除执行脚本外, 非交互模式还支持执行一条或多条 SQL 语句. 使用方法如下: 加上 -c 选项, SQL 语句两侧需要加上双引号, 如果是多条语句, 则语句之间需要用分号隔开.

psql -d postgresql_book -c "DROP TABLE IF EXISTS dross; CREATE SCHEMA staging;"

在脚本中可以使用交互命令

下面是 ch03 目录下的 build_stage.psql 文件的内容, 用于创建 staging 模式下的表 factfinder_import.

\a
\t
SELECT 'CREATE TABLE staging.factfinder_import(
  geo_id varchar(255),
  geo_id2 varchar(255),
  geo_display varchar(255), '
  || array_to_string(
  array_agg('s' || lpad(i::text,2, '0') || ' varchar(255), s' || lpad(i::text,2, '0') || '_perc varchar(255) ' ),
 ',') 
 || ');' As create_sql 
FROM generate_series(1,51) As i \g create_script.sql
\i create_script.sql 

要理解上面的代码, 首先熟悉几个函数.

函数

函数简介

generate_series()函数

generate_series(1,9) 会生成 1,2,...,9这九个数.

postgres=# select generate_series(1,9);
 generate_series
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
(9 行记录)

array_agg()函数

array_agg() 函数是一个聚合函数, 它接受一组值并返回一个数组, 其中集合中的每个值被分配给数组的一个元素.

postgres=# select array_agg('x' || i ) from generate_series(1,9) AS i;
          array_agg
------------------------------
 {x1,x2,x3,x4,x5,x6,x7,x8,x9}
(1 行记录)

array_to_string()函数

array_to_string() 函数返回一个字符串, 是将数组中的元素通过分隔符连接起来组成一个字符串, 其中 NULL 元素可以被指定字符串替代. 其语法如下

array_to_string(array, delimiter[, null_string])

如果指定的数组为 NULL, array_to_string() 函数将返回 NULL.

postgres=# SELECT array_to_string(ARRAY[1, NULL, 2, 1], ',');
 array_to_string
-----------------
 1,2,1
(1 行记录)

postgres=# select array_to_string(array_agg('x'||i),',') FROM generate_series(1,9) AS i;
      array_to_string
----------------------------
 x1,x2,x3,x4,x5,x6,x7,x8,x9
(1 行记录)


postgres=# select array_to_string(array_agg('x'||i),'+') FROM generate_series(1,9) AS i;
      array_to_string
----------------------------
 x1+x2+x3+x4+x5+x6+x7+x8+x9
(1 行记录)

lpad() 函数在一个给定字符串的左侧用指定字符串填充, 使其达到指定的长度. 而 rpad() 函数则在给定字符串的右侧填充.

mydb=> select lpad(i::text, 3, '0') from generate_series(1,12) as i;
 lpad
------
 001
 002
 003
 004
 005
 006
 007
 008
 009
 010
 011
 012
(12 行记录)

mydb=> select lpad(i::text, 5, '0x') from generate_series(1,12) as i;
 lpad
-------
 0x0x1
 0x0x2
 0x0x3
 0x0x4
 0x0x5
 0x0x6
 0x0x7
 0x0x8
 0x0x9
 0x010
 0x011
 0x012
(12 行记录)

例子

创建一张复杂的表

现在要建立一张名为 count_to_9 的表, 表的结构中有很多元素有明显的类似现象.

CREATE TABLE count_to_9(
 s01 varchar(255), 
 s01_perc varchar(255),
 ...
 s09 varchar(255), 
 s09_perc varchar(255)
 );

使用 array_agg() 函数生成一个含有9个元素的字符串数组, 其中每个元素形如 "s0i varchar(255), s0i_perc varchar(255) ". 其中 i 用数字 1 到 9 替代.

mydb=> select array_agg('s' || lpad(i::text,2, '0') || ' varchar(255), s' || lpad(i::text,2, '0') || '_perc varchar(255) ' )
mydb-> from generate_series(1,9) as i;
                                                                                                                                                                                              array_agg                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"s01 varchar(255), s01_perc varchar(255) ","s02 varchar(255), s02_perc varchar(255) ","s03 varchar(255), s03_perc varchar(255) ","s04 varchar(255), s04_perc varchar(255) ","s05 varchar(255), s05_perc varchar(255) ","s06 varchar(255), s06_perc varchar(255) ","s07 varchar(255), s07_perc varchar(255) ","s08 varchar(255), s08_perc varchar(255) ","s09 varchar(255), s09_perc varchar(255) "}

然后将该数组中的元素用分隔符 ',' 连接成一个字符串.

mydb=> select array_to_string(array_agg('s' || lpad(i::text,2, '0') || ' varchar(255), s' || lpad(i::text,2, '0') || '_perc varchar(255) ' ),',')  FROM generate_series(1,9) as i;
                                                                                                                                                                                 array_to_string                                                                                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 s01 varchar(255), s01_perc varchar(255) ,s02 varchar(255), s02_perc varchar(255) ,s03 varchar(255), s03_perc varchar(255) ,s04 varchar(255), s04_perc varchar(255) ,s05 varchar(255), s05_perc varchar(255) ,s06 varchar(255), s06_perc varchar(255) ,s07 varchar(255), s07_perc varchar(255) ,s08 varchar(255), s08_perc varchar(255) ,s09 varchar(255), s09_perc varchar(255)
(1 行记录)

将上面的代码组合起来.

mydb=> SELECT 'CREATE TABLE count_to_9(' ||
mydb->   array_to_string(array_agg('s' || lpad(i::text,2, '0') || ' varchar(255), s' || lpad(i::text,2, '0') || '_perc varchar(255) ' ),',')
mydb->   || ');'
mydb->   FROM generate_series(1,9) as i;
                                                                                                                                                                                                  ?column?                                                                                                                                
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 CREATE TABLE count_to_9(s01 varchar(255), s01_perc varchar(255) ,s02 varchar(255), s02_perc varchar(255) ,s03 varchar(255), s03_perc varchar(255) ,s04 varchar(255), s04_perc varchar(255) ,s05 varchar(255), s05_perc varchar(255) ,s06 varchar(255), s06_perc varchar(255) ,s07 varchar(255), s07_perc varchar(255) ,s08 varchar(255), s08_perc varchar(255) ,s09 varchar(255), s09_perc varchar(255) );
(1 行记录)

mydb=>  CREATE TABLE count_to_9(s01 varchar(255), s01_perc varchar(255) ,s02 varchar(255), s02_perc varchar(255) ,s03 varchar(255), s03_perc varchar(255) ,s04 varchar(255), s04_perc varchar(255) ,s05 varchar(255), s05_perc varchar(255) ,s06 varchar(255), s06_perc varchar(255) ,s07 varchar(255), s07_perc varchar(255) ,s08 varchar(255), s08_perc varchar(255) ,s09 varchar(255), s09_perc varchar(255) );
CREATE TABLE
mydb=> \d
                   关联列表
 架构模式 |       名称       |  类型  | 拥有者
----------+------------------+--------+--------
 public   | count_to_9       | 数据表 | haife
 public   | finitesumformula | 数据表 | haife
(2 行记录)

将上面生成的 CREATE TABLE 语句保持到指定的 .sql 文件中

mydb=> SELECT 'CREATE TABLE count_to_9(' ||
mydb->   array_to_string(array_agg('s' || lpad(i::text,2, '0') || ' varchar(255), s' || lpad(i::text,2, '0') || '_perc varchar(255) ' ),',')
mydb->   || ');'
mydb->   FROM generate_series(1,9) as i
mydb-> \g create_script.sql
mydb=>

\g create_script.sql 是指将上面生成 CREATE TABLE 语句保存到文件 create_script.sql 中. 打开此文件, 内容如下:

                                                                                                                                                                                                  ?column?                                                                                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 CREATE TABLE count_to_9(s01 varchar(255), s01_perc varchar(255) ,s02 varchar(255), s02_perc varchar(255) ,s03 varchar(255), s03_perc varchar(255) ,s04 varchar(255), s04_perc varchar(255) ,s05 varchar(255), s05_perc varchar(255) ,s06 varchar(255), s06_perc varchar(255) ,s07 varchar(255), s07_perc varchar(255) ,s08 varchar(255), s08_perc varchar(255) ,s09 varchar(255), s09_perc varchar(255) );
(1 行记录)

这里 ?column? 表示未指定列(属性)的名称. 若要指定属性名, 可以使用 As 关键字, 例如:

mydb=> SELECT 'CREATE TABLE count_to_9(' ||
mydb->   array_to_string(array_agg('s' || lpad(i::text,2, '0') || ' varchar(255), s' || lpad(i::text,2, '0') || '_perc varchar(255) ' ),',')
mydb->   || ');'
mydb->   As create_sql
mydb->   FROM generate_series(1,9) as i
mydb-> \g create_script.sql
mydb=>

代码的最后 \i create_script.sql 指执行脚本文件 create_script.sql .

mydb=> \i create_table_script.sql
psql:create_table_script.sql:3: 错误:  语法错误 在 "create_sql" 或附近的
第1行create_sql                                                  ...
     ^
psql:create_table_script.sql:5: 错误:  语法错误 在 "1" 或附近的
第1行(1 行记录)

原因在于文件中有多余的用于格式输出的字符, 比如标题栏和换行符等. 用 \t 来忽略标题栏的输出, 同时使用 \a 关闭对齐模式以防 psql 为对齐输出结果而自动加上换行符.

mydb=> \a
输出格式是 unaligned.
mydb=> \t
开启只显示元组.

再次执行上面的语句

mydb=> SELECT 'CREATE TABLE count_to_9(' ||
mydb->   array_to_string(array_agg('s' || lpad(i::text,2, '0') || ' varchar(255), s' || lpad(i::text,2, '0') || '_perc varchar(255) ' ),',')
mydb->   || ');'
mydb-> As create_sql FROM generate_series(1,9) as i;
CREATE TABLE count_to_9(s01 varchar(255), s01_perc varchar(255) ,s02 varchar(255), s02_perc varchar(255) ,s03 varchar(255), s03_perc varchar(255) ,s04 varchar(255), s04_perc varchar(255) ,s05 varchar(255), s05_perc varchar(255) ,s06 varchar(255), s06_perc varchar(255) ,s07 varchar(255), s07_perc varchar(255) ,s08 varchar(255), s08_perc varchar(255) ,s09 varchar(255), s09_perc varchar(255) );

当然, 使用了 \t,\a 命令后, 在显示数据库时, 会不友好.

mydb=> \l
mydb|haife|UTF8|libc|zh-CN|zh-CN|||
postgres|postgres|UTF8|libc|zh-CN|zh-CN|||
template0|postgres|UTF8|libc|zh-CN|zh-CN|||=c/postgres
postgres=CTc/postgres
template1|postgres|UTF8|libc|zh-CN|zh-CN|||=c/postgres
postgres=CTc/postgres

定制 psql 操作环境

定制 psql 操作环境

psql 默认在连接数据库后显示所连接的数据库名称, 后面加 => 这两个字符. 如果经常远程连接多个数据库, 那么就有必要区分所连接的数据库是在哪个主机上的, 或者需要显示当前用户名等信息.

psql 支持用户自定义操作环境. 它在启动阶段会搜索一个名为 psqlrc 的配置文件, 其存储了个人的偏好设置.

在 Linux/Unix 环境中, 该文件一般会被命名为 .psqlrc 并放置于 postgres 用户的 home 目录.

在 Windows 上, 该文件叫做 psqlrc.conf, 并被放置于 %APPDATA%\postgresql 文件夹下.

haifeng@LAPTOP-Q34L5TP8:~> su postgres
Password:
postgres@LAPTOP-Q34L5TP8:/home/haifeng> cd
postgres@LAPTOP-Q34L5TP8:~> ls
data  initlog  logfile  pg_wal_1.txt
postgres@LAPTOP-Q34L5TP8:~> ls -la
total 4836
drwxr-x---  3 postgres postgres    4096 Oct  9 17:15 .
drwxr-xr-x 27 root     root        4096 Sep  7 22:03 ..
-rw-------  1 postgres postgres    2047 Oct  9 17:35 .bash_history
-rw-r-----  1 postgres postgres     192 Nov 13  2024 .bash_profile
drwx------ 20 postgres postgres    4096 Oct 10 09:23 data
-rw-r--r--  1 postgres postgres     905 Sep 11 08:57 initlog
-rw-------  1 postgres postgres      20 Oct  9 17:15 .lesshst
-rw-------  1 postgres postgres     187 Sep  7 22:37 logfile
-rw-r--r--  1 postgres postgres 4902526 Sep 19 16:54 pg_wal_1.txt
-rw-------  1 postgres postgres     868 Oct  9 17:35 .psql_history
-rw-------  1 postgres postgres   10096 Oct  9 09:14 .viminfo
postgres@LAPTOP-Q34L5TP8:~> vim .psql_history
postgres@LAPTOP-Q34L5TP8:~>

该文件一般需要手动创建. 我们使用 vim 创建此文件.

postgres@LAPTOP-Q34L5TP8:~> vim .psqlrc

输入下面的内容.

\pset null 'NULL'
\set PROMPT1 '%n@%M:%> %x %/# '
\pset pager always
\timing on

\set PROMPT1 '字符串' 是设定 psql 界面提示符.

• %n : 登录角色, 即 name;
• %M : 主机名, 即 Machine;
• %> : 侦听端口;
• %x : 事务状态;
• %/ : 当前使用的数据库名;
• 其他字符, 如 @:# 和空格属于自己加上去的.

\timing on 指打开计时开关. 在每个查询执行完毕后都附带输出执行时长.

postgres@LAPTOP-Q34L5TP8:~> psql
Null display is "NULL".
Pager is always used.
Timing is on.
psql (17.6)
Type "help" for help.

postgres@[local]:5432 postgres#

执行 \c mydb 切换到数据库 mydb, 则提示符会变为如下形式.

postgres@[local]:5432 postgres# \c mydb
You are now connected to database "mydb" as user "postgres".
postgres@[local]:5432 mydb#

注意: 在指定路径时都应使用 Linux/Unix 风格的正斜杠 /.

如果希望 psql 在启动时跳过加载 psqlrc 文件, 则请加 -X 选项.

postgres@[local]:5432 postgres# \q
postgres@LAPTOP-Q34L5TP8:~> psql -X
psql (17.6)
Type "help" for help.

postgres=#

事务自动提交

事务自动提交

默认情况下, AUTOCOMMIT 是打开的. 也就是说任何一个 SQL 语句执行完毕后, 它所做的数据修改都会被立即提交. 这种情况下每个语句都是一个独立的事务, 一旦执行完毕后结果就不可撤销.

如果你需要运行大量的 DML 语句（数据操纵语言Data Manipulation Language, 主要是进行插入、删除、修改元组的操作.）并且这些语句还未经充分测试, 那么自动提交功能会带来麻烦, 此时有必要关闭事务自动提交机制来对数据进行保护.

下面的命令将 AUTOCOMMIT 关闭.

\set AUTOCOMMIT off

PS D:\work\cs\Database\postgres\book> psql -d postgresql_book
psql (17.6)
输入 "help" 来获取帮助信息.

postgresql_book=> \set AUTOCOMMIT off
postgresql_book=>

如果忘了是否将 AUTOCOMMIT 的状态, 则可以使用 \echo :AUTOCOMMIT 命令显示当前 AUTOCOMMIT 参数的值.

postgresql_book=# \echo :AUTOCOMMIT
off
postgresql_book=#

下面我们尝试更改 census.facts 或其他表中的数据.

postgresql_book=> \d census.facts
                       数据表 "census.facts"
     栏位     |         类型          | 校对规则 |  可空的  | 预设
--------------+-----------------------+----------+----------+------
 fact_type_id | integer               |          | not null |
 tract_id     | character varying(11) |          | not null |
 yr           | integer               |          | not null |
 val          | numeric(12,3)         |          |          |
 perc         | numeric(6,2)          |          |          |
索引：
    "pk_facts" PRIMARY KEY, btree (fact_type_id, tract_id, yr)

postgresql_book=> select count(*) from census.facts;
错误:  对模式 census 权限不够
第1行select count(*) from census.facts;
                          ^
postgresql_book=!> \du
错误:  当前事务被终止, 事务块结束之前的查询被忽略
postgresql_book=!> ROLLBACK;
ROLLBACK
postgresql_book=> \du
                       角色列表
 角色名称 |                    属性
----------+--------------------------------------------
 haife    | 建立 DB
 haifeng  |
 postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS

上面显示权限不够, 使用 select user; 或 select * from current_user; 查看一下当前用户是谁. 因为登录时未指定用户.

postgresql_book=> select * from current_user;
 current_user
--------------
 haife
(1 行记录)

退出, 使用 postgres 用户重新登录. 当然也可以将相关权限赋予 haife 后再操作.

PS D:\work\cs\Database\postgres\book> psql -U postgres -d postgresql_book
psql (17.6)
输入 "help" 来获取帮助信息.

postgresql_book=# \set AUTOCOMMIT off
postgresql_book=#

census.facts 中含有大量记录.

postgresql_book=# select count(*) from census.facts;
 count
--------
 100504
(1 行记录)


postgresql_book=*#

注意上面提示符中多了一个 *.

postgresql_book=*# \d+ census.facts
                                         数据表 "census.facts"
     栏位     |         类型          | 校对规则 |  可空的  | 预设 |   存储   | 压缩 | 统计目标 | 描述
--------------+-----------------------+----------+----------+------+----------+------+----------+------
 fact_type_id | integer               |          | not null |      | plain    |      |          |
 tract_id     | character varying(11) |          | not null |      | extended |      |          |
 yr           | integer               |          | not null |      | plain    |      |          |
 val          | numeric(12,3)         |          |          |      | main     |      |          |
 perc         | numeric(6,2)          |          |          |      | main     |      |          |
索引：
    "pk_facts" PRIMARY KEY, btree (fact_type_id, tract_id, yr)
访问方法 heap

为安全起见, 我们尝试更改 census.lu_fact_types 表中的内容. 这张表中的内容不多.

postgresql_book=# select count(*) from census.lu_fact_types;
 count
-------
    68
(1 行记录)

该表的结构为

postgresql_book=# \d census.lu_fact_types
                                               数据表 "census.lu_fact_types"
     栏位     |           类型           | 校对规则 |  可空的  |                            预设
--------------+--------------------------+----------+----------+------------------------------------------------------------
 fact_type_id | integer                  |          | not null | nextval('census.lu_fact_types_fact_type_id_seq'::regclass)
 category     | character varying(100)   |          |          |
 fact_subcats | character varying(255)[] |          |          |
 short_name   | character varying(50)    |          |          |
索引：
    "pk_lu_fact_types" PRIMARY KEY, btree (fact_type_id)

尝试更新 lu_fact_types 中的数据.

postgresql_book=*# UPDATE census.lu_fact_types SET short_name='This is a mistake.';
UPDATE 68
postgresql_book=*#

postgresql_book=*# SELECT * FROM census.lu_fact_types;
 fact_type_id |  category  |                                                                                                                        fact_subcats                                                                                                                        |     short_name
--------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------
            1 | Housing    | {S01,"OCCUPANCY STATUS","Total housing units",HD01,Number}                                                                                                                                                                                                 | This is a mistake.
            2 | Housing    | {S02,"OCCUPANCY STATUS","Total housing units","Occupied housing units",HD01,Number}                                                                                                                                                                        | This is a mistake.
            3 | Housing    | {S03,"OCCUPANCY STATUS","Total housing units","Vacant housing units",HD01,Number}                                                                                                                                                                          | This is a mistake.
            4 | Housing    | {S04,TENURE,"Occupied housing units",HD01,Number}                                                                                                                                                                                                          | This is a mistake.
            5 | Housing    | {S05,TENURE,"Occupied housing units","Owner occupied",HD01,Number}                                                                                                                                                                                         | This is a mistake.
            6 | Housing    | {S06,TENURE,"Occupied housing units","Owner occupied","Owned with a mortgage or loan",HD01,Number}                                                                                                                                                         | This is a mistake.
            7 | Housing    | {S07,TENURE,"Occupied housing units","Owner occupied","Owned free and clear",HD01,Number}                                                                                                                                                                  | This is a mistake.
            8 | Housing    | {S08,TENURE,"Occupied housing units","Renter occupied",HD01,Number}                                                                                                                                                                                        | This is a mistake.
            9 | Housing    | {S09,"VACANCY STATUS","Vacant housing units",HD01,Number}                                                                                                                                                                                                  | This is a mistake.
           10 | Housing    | {S10,"VACANCY STATUS","Vacant housing units","For rent",HD01,Number}                                                                                                                                                                                       | This is a mistake.
           11 | Housing    | {S11,"VACANCY STATUS","Vacant housing units","Rented, not occupied",HD01,Number}                                                                                                                                                                           | This is a mistake.
           12 | Housing    | {S12,"VACANCY STATUS","Vacant housing units","For sale only",HD01,Number}                                                                                                                                                                                  | This is a mistake.
           13 | Housing    | {S13,"VACANCY STATUS","Vacant housing units","Sold, not occupied",HD01,Number}                                                                                                                                                                             | This is a mistake.
           14 | Housing    | {S14,"VACANCY STATUS","Vacant housing units","For seasonal, recreational, or occasional use",HD01,Number}                                                                                                                                                  | This is a mistake.
           15 | Housing    | {S15,"VACANCY STATUS","Vacant housing units","For migratory workers",HD01,Number}                                                                                                                                                                          | This is a mistake.
           16 | Housing    | {S16,"VACANCY STATUS","Vacant housing units","Other vacant",HD01,Number}                                                                                                                                                                                   | This is a mistake.
           17 | Housing    | {S17,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units",HD01,Number}                                                                                                                                     | This is a mistake.
           18 | Housing    | {S18,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units",HD01,Number}                                                                                                      | This is a mistake.
           19 | Housing    | {S19,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder",HD01,Number}                                                                 | This is a mistake.
           20 | Housing    | {S20,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder","White alone householder",HD01,Number}                                       | This is a mistake.
           21 | Housing    | {S21,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder","Black or African American alone householder",HD01,Number}                   | This is a mistake.
           22 | Housing    | {S22,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder","American Indian and Alaska Native alone householder",HD01,Number}           | This is a mistake.
           23 | Housing    | {S23,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder","Asian alone householder",HD01,Number}                                       | This is a mistake.
           24 | Housing    | {S24,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder","Native Hawaiian and Other Pacific Islander alone householder",HD01,Number}  | This is a mistake.
           25 | Housing    | {S25,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder","Some Other Race alone householder",HD01,Number}                             | This is a mistake.
           26 | Housing    | {S26,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder","Two or More Races householder",HD01,Number}                                 | This is a mistake.
           27 | Housing    | {S27,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder",HD01,Number}                                                                     | This is a mistake.
           28 | Housing    | {S28,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder","White alone householder",HD01,Number}                                           | This is a mistake.
           29 | Housing    | {S29,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder","Black or African American alone householder",HD01,Number}                       | This is a mistake.
           30 | Housing    | {S30,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder","American Indian and Alaska Native alone householder",HD01,Number}               | This is a mistake.
           31 | Housing    | {S31,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder","Asian alone householder",HD01,Number}                                           | This is a mistake.
           32 | Housing    | {S32,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder","Native Hawaiian and Other Pacific Islander alone householder",HD01,Number}      | This is a mistake.
           33 | Housing    | {S33,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder","Some Other Race alone householder",HD01,Number}                                 | This is a mistake.
           66 | Population | {D015,Total:,"Hispanic or Latino:","Native Hawaiian and Other Pacific Islander alone"}                                                                                                                                                                     | This is a mistake.
           34 | Housing    | {S34,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder","Two or More Races householder",HD01,Number}                                     | This is a mistake.
           35 | Housing    | {S35,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units",HD01,Number}                                                                                                     | This is a mistake.
           36 | Housing    | {S36,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder",HD01,Number}                                                                | This is a mistake.
           37 | Housing    | {S37,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder","White alone householder",HD01,Number}                                      | This is a mistake.
           38 | Housing    | {S38,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder","Black or African American alone householder",HD01,Number}                  | This is a mistake.
           39 | Housing    | {S39,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder","American Indian and Alaska Native alone householder",HD01,Number}          | This is a mistake.
           40 | Housing    | {S40,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder","Asian alone householder",HD01,Number}                                      | This is a mistake.
           41 | Housing    | {S41,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder","Native Hawaiian and Other Pacific Islander alone householder",HD01,Number} | This is a mistake.
           42 | Housing    | {S42,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder","Some Other Race alone householder",HD01,Number}                            | This is a mistake.
           43 | Housing    | {S43,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder","Two or More Races householder",HD01,Number}                                | This is a mistake.
           44 | Housing    | {S44,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder",HD01,Number}                                                                    | This is a mistake.
           45 | Housing    | {S45,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder","White alone householder",HD01,Number}                                          | This is a mistake.
           46 | Housing    | {S46,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder","Black or African American alone householder",HD01,Number}                      | This is a mistake.
           47 | Housing    | {S47,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder","American Indian and Alaska Native alone householder",HD01,Number}              | This is a mistake.
           48 | Housing    | {S48,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder","Asian alone householder",HD01,Number}                                          | This is a mistake.
           49 | Housing    | {S49,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder","Native Hawaiian and Other Pacific Islander alone householder",HD01,Number}     | This is a mistake.
           50 | Housing    | {S50,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder","Some Other Race alone householder",HD01,Number}                                | This is a mistake.
           51 | Housing    | {S51,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder","Two or More Races householder",HD01,Number}                                    | This is a mistake.
           52 | Population | {D001,Total:}                                                                                                                                                                                                                                              | This is a mistake.
           53 | Population | {D002,Total:,"Not Hispanic or Latino:"}                                                                                                                                                                                                                    | This is a mistake.
           54 | Population | {D003,Total:,"Not Hispanic or Latino:","White alone"}                                                                                                                                                                                                      | This is a mistake.
           55 | Population | {D004,Total:,"Not Hispanic or Latino:","Black or African American alone"}                                                                                                                                                                                  | This is a mistake.
           56 | Population | {D005,Total:,"Not Hispanic or Latino:","American Indian and Alaska Native alone"}                                                                                               | This is a mistake.
           57 | Population | {D006,Total:,"Not Hispanic or Latino:","Asian alone"}                                                                                                                                                                                                      | This is a mistake.
           58 | Population | {D007,Total:,"Not Hispanic or Latino:","Native Hawaiian and Other Pacific Islander alone"}                                                                                                                                                                 | This is a mistake.
           59 | Population | {D008,Total:,"Not Hispanic or Latino:","Some Other Race alone"}                                                                                                                                                                                            | This is a mistake.
           60 | Population | {D009,Total:,"Not Hispanic or Latino:","Two or More Races"}                                                                                                                                                                                                | This is a mistake.
           61 | Population | {D010,Total:,"Hispanic or Latino:"}                                                                                                                                                                                                                        | This is a mistake.
           62 | Population | {D011,Total:,"Hispanic or Latino:","White alone"}                                                                                                                                                                                                          | This is a mistake.
           63 | Population | {D012,Total:,"Hispanic or Latino:","Black or African American alone"}                                                                                                                                                                                      | This is a mistake.
           64 | Population | {D013,Total:,"Hispanic or Latino:","American Indian and Alaska Native alone"}                                                                                                                                                                              | This is a mistake.
           65 | Population | {D014,Total:,"Hispanic or Latino:","Asian alone"}                                                                                                                                                                                                          | This is a mistake.
           67 | Population | {D016,Total:,"Hispanic or Latino:","Some Other Race alone"}                                                                                                                                                                                                | This is a mistake.
           68 | Population | {D017,Total:,"Hispanic or Latino:","Two or More Races"}                                                                                                                                                                                                    | This is a mistake.
(68 行记录)


postgresql_book=*#

现将其回滚.

postgresql_book=*# ROLLBACK;
ROLLBACK
postgresql_book=#

重新查询其中的数据.

postgresql_book=# SELECT * FROM census.lu_fact_types;
 fact_type_id |  category  |                                                                                                                        fact_subcats                                                                                                                        | short_name
--------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------
            1 | Housing    | {S01,"OCCUPANCY STATUS","Total housing units",HD01,Number}                                                                                                                                                                                                 | s01
            2 | Housing    | {S02,"OCCUPANCY STATUS","Total housing units","Occupied housing units",HD01,Number}                                                                                                                                                                        | s02
            3 | Housing    | {S03,"OCCUPANCY STATUS","Total housing units","Vacant housing units",HD01,Number}                                                                                                                                                                          | s03
            4 | Housing    | {S04,TENURE,"Occupied housing units",HD01,Number}                                                                                                                                                                                                          | s04
            5 | Housing    | {S05,TENURE,"Occupied housing units","Owner occupied",HD01,Number}                                                                                                                                                                                         | s05
            6 | Housing    | {S06,TENURE,"Occupied housing units","Owner occupied","Owned with a mortgage or loan",HD01,Number}                                                                                                                                                         | s06
            7 | Housing    | {S07,TENURE,"Occupied housing units","Owner occupied","Owned free and clear",HD01,Number}                                                                                                                                                                  | s07
            8 | Housing    | {S08,TENURE,"Occupied housing units","Renter occupied",HD01,Number}                                                                                                                                                                                        | s08
            9 | Housing    | {S09,"VACANCY STATUS","Vacant housing units",HD01,Number}                                                                                                                                                                                                  | s09
           10 | Housing    | {S10,"VACANCY STATUS","Vacant housing units","For rent",HD01,Number}                                                                                                                                                                                       | s10
           11 | Housing    | {S11,"VACANCY STATUS","Vacant housing units","Rented, not occupied",HD01,Number}                                                                                                                                                                           | s11
           12 | Housing    | {S12,"VACANCY STATUS","Vacant housing units","For sale only",HD01,Number}                                                                                                                                                                                  | s12
           13 | Housing    | {S13,"VACANCY STATUS","Vacant housing units","Sold, not occupied",HD01,Number}                                                                                                                                                                             | s13
           14 | Housing    | {S14,"VACANCY STATUS","Vacant housing units","For seasonal, recreational, or occasional use",HD01,Number}                                                                                                                                                  | s14
           15 | Housing    | {S15,"VACANCY STATUS","Vacant housing units","For migratory workers",HD01,Number}                                                                                                                                                                          | s15
           16 | Housing    | {S16,"VACANCY STATUS","Vacant housing units","Other vacant",HD01,Number}                                                                                                                                                                                   | s16
           17 | Housing    | {S17,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units",HD01,Number}                                                                                                                                     | s17
           18 | Housing    | {S18,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units",HD01,Number}                                                                                                      | s18
           19 | Housing    | {S19,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder",HD01,Number}                                                                 | s19
           20 | Housing    | {S20,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder","White alone householder",HD01,Number}                                       | s20
           21 | Housing    | {S21,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder","Black or African American alone householder",HD01,Number}                   | s21
           22 | Housing    | {S22,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder","American Indian and Alaska Native alone householder",HD01,Number}           | s22
           23 | Housing    | {S23,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder","Asian alone householder",HD01,Number}                                       | s23
           24 | Housing    | {S24,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder","Native Hawaiian and Other Pacific Islander alone householder",HD01,Number}  | s24
           25 | Housing    | {S25,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder","Some Other Race alone householder",HD01,Number}                             | s25
           26 | Housing    | {S26,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Not Hispanic or Latino householder","Two or More Races householder",HD01,Number}                                 | s26
           27 | Housing    | {S27,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder",HD01,Number}                                                                     | s27
           28 | Housing    | {S28,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder","White alone householder",HD01,Number}                                           | s28
           29 | Housing    | {S29,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder","Black or African American alone householder",HD01,Number}                       | s29
           30 | Housing    | {S30,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder","American Indian and Alaska Native alone householder",HD01,Number}               | s30
           31 | Housing    | {S31,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder","Asian alone householder",HD01,Number}                                           | s31
           32 | Housing    | {S32,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder","Native Hawaiian and Other Pacific Islander alone householder",HD01,Number}      | s32
           33 | Housing    | {S33,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder","Some Other Race alone householder",HD01,Number}                                 | s33
           34 | Housing    | {S34,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Owner-occupied housing units","Hispanic or Latino householder","Two or More Races householder",HD01,Number}                                     | s34
           35 | Housing    | {S35,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units",HD01,Number}                                                                                                     | s35
           36 | Housing    | {S36,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder",HD01,Number}                                                                | s36
           37 | Housing    | {S37,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder","White alone householder",HD01,Number}                                      | s37
           38 | Housing    | {S38,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder","Black or African American alone householder",HD01,Number}                  | s38
           39 | Housing    | {S39,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder","American Indian and Alaska Native alone householder",HD01,Number}          | s39
           40 | Housing    | {S40,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder","Asian alone householder",HD01,Number}                                      | s40
           41 | Housing    | {S41,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder","Native Hawaiian and Other Pacific Islander alone householder",HD01,Number} | s41
           42 | Housing    | {S42,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder","Some Other Race alone householder",HD01,Number}                            | s42
           43 | Housing    | {S43,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Not Hispanic or Latino householder","Two or More Races householder",HD01,Number}                                | s43
           44 | Housing    | {S44,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder",HD01,Number}                                                                    | s44
           45 | Housing    | {S45,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder","White alone householder",HD01,Number}                                          | s45
           46 | Housing    | {S46,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder","Black or African American alone householder",HD01,Number}                      | s46
           47 | Housing    | {S47,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder","American Indian and Alaska Native alone householder",HD01,Number}              | s47
           48 | Housing    | {S48,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder","Asian alone householder",HD01,Number}                                          | s48
           49 | Housing    | {S49,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder","Native Hawaiian and Other Pacific Islander alone householder",HD01,Number}     | s49
           50 | Housing    | {S50,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder","Some Other Race alone householder",HD01,Number}                                | s50
           51 | Housing    | {S51,"TENURE BY HISPANIC OR LATINO ORIGIN OF HOUSEHOLDER BY RACE OF HOUSEHOLDER","Occupied housing units","Renter-occupied housing units","Hispanic or Latino householder","Two or More Races householder",HD01,Number}                                    | s51
           52 | Population | {D001,Total:}                                                                                                                                                                                                                                              | d001
           53 | Population | {D002,Total:,"Not Hispanic or Latino:"}                                                                                                                                                                                                                    | d002
           54 | Population | {D003,Total:,"Not Hispanic or Latino:","White alone"}                                                                                                                                                                                                      | d003
           55 | Population | {D004,Total:,"Not Hispanic or Latino:","Black or African American alone"}                                                                                                                                                                                  | d004
           56 | Population | {D005,Total:,"Not Hispanic or Latino:","American Indian and Alaska Native alone"}                                                                                                                                                                          | d005
           57 | Population | {D006,Total:,"Not Hispanic or Latino:","Asian alone"}                                                                                                                                                                                                      | d006
           58 | Population | {D007,Total:,"Not Hispanic or Latino:","Native Hawaiian and Other Pacific Islander alone"}                                                                                                                                                                 | d007
           59 | Population | {D008,Total:,"Not Hispanic or Latino:","Some Other Race alone"}                                                                                                                                                                                            | d008
           60 | Population | {D009,Total:,"Not Hispanic or Latino:","Two or More Races"}                                                                                                                                                                                                | d009
           61 | Population | {D010,Total:,"Hispanic or Latino:"}                                                                                                                                                                                                                        | d010
           62 | Population | {D011,Total:,"Hispanic or Latino:","White alone"}                                                                                                                                                                                                          | d011
           63 | Population | {D012,Total:,"Hispanic or Latino:","Black or African American alone"}                                                                                                                                                                                      | d012
           64 | Population | {D013,Total:,"Hispanic or Latino:","American Indian and Alaska Native alone"}                                                                                                                                                                              | d013
           65 | Population | {D014,Total:,"Hispanic or Latino:","Asian alone"}                                                                                                                                                                                                          | d014
           66 | Population | {D015,Total:,"Hispanic or Latino:","Native Hawaiian and Other Pacific Islander alone"}                                                                                                                                                                     | d015
           67 | Population | {D016,Total:,"Hispanic or Latino:","Some Other Race alone"}                                                                                                                                                                                                | d016
           68 | Population | {D017,Total:,"Hispanic or Latino:","Two or More Races"}                                                                                                                                                                                                    | d017
(68 行记录)


postgresql_book=*#

命令别名

给命令起别名

EXPLAIN ANALYZE VERBOSE 后面跟一个 SQL 语句, 这将分析后面的SQL语句执行的情况.

postgresql_book=*# EXPLAIN ANALYZE VERBOSE select count(*) from pg_tables;
                                                                                                                                                                                                                                                    QUERY PLAN                                                                                                                                                 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=19.36..19.37 rows=1 width=8) (actual time=0.333..0.334 rows=1 loops=1)
   Output: count(*)
   ->  Seq Scan on pg_catalog.pg_class c  (cost=0.00..19.19 rows=68 width=0) (actual time=0.040..0.309 rows=75 loops=1)
         Output: c.oid, c.relname, c.relnamespace, c.reltype, c.reloftype, c.relowner, c.relam, c.relfilenode, c.reltablespace, c.relpages, c.reltuples, c.relallvisible, c.reltoastrelid, c.relhasindex, c.relisshared, c.relpersistence, c.relkind, c.relnatts, c.relchecks, c.relhasrules, c.relhastriggers, c.relhassubclass, c.relrowsecurity, c.relforcerowsecurity, c.relispopulated, c.relreplident, c.relispartition, c.relrewrite, c.relfrozenxid, c.relminmxid, c.relacl, c.reloptions, c.relpartbound
         Filter: (c.relkind = ANY ('{r,p}'::"char"[]))
         Rows Removed by Filter: 359
 Planning Time: 0.515 ms
 Execution Time: 0.398 ms
(8 行记录)

由于这个EXPLAIN ANALYZE VERBOSE 命令经常要用到, 为此可以用 \set 命令为其起个别名.

postgresql_book=*# \set eav 'EXPLAIN ANALYZE VERBOSE'
postgresql_book=*#

现在就可以使用 :eav 直接调用EXPLAIN ANALYZE VERBOSE 命令了. 注意别名前要加冒号.

postgresql_book=*# :eav SELECT count(*) FROM pg_tables;
                                                                                                                                                                                                                                                    QUERY PLAN                                                                                                                                                 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=19.36..19.37 rows=1 width=8) (actual time=0.477..0.478 rows=1 loops=1)
   Output: count(*)
   ->  Seq Scan on pg_catalog.pg_class c  (cost=0.00..19.19 rows=68 width=0) (actual time=0.065..0.450 rows=75 loops=1)
         Output: c.oid, c.relname, c.relnamespace, c.reltype, c.reloftype, c.relowner, c.relam, c.relfilenode, c.reltablespace, c.relpages, c.reltuples, c.relallvisible, c.reltoastrelid, c.relhasindex, c.relisshared, c.relpersistence, c.relkind, c.relnatts, c.relchecks, c.relhasrules, c.relhastriggers, c.relhassubclass, c.relrowsecurity, c.relforcerowsecurity, c.relispopulated, c.relreplident, c.relispartition, c.relrewrite, c.relfrozenxid, c.relminmxid, c.relacl, c.reloptions, c.relpartbound
         Filter: (c.relkind = ANY ('{r,p}'::"char"[]))
         Rows Removed by Filter: 359
 Planning Time: 0.925 ms
 Execution Time: 0.554 ms
(8 行记录)

我们还可以将该查询做成别名存入到 psqlrc 文件中. 例如, 编辑 C:\Users\haife\AppData\Roaming\postgresql 下的 psqlrc.conf 文件.

\pset null 'NULL'
\set PROMPT1 '%n@%M:%>%x %/# '
\pset pager always
\timing on
\set eav_q_pgtables 'EXPLAIN ANALYZE VERBOSE SELECT count(*) FROM pg_tables;'

重新登录

PS D:\work\cs\Database\postgres\book> psql -U postgres -d postgresql_book
 "NULL" 是空值显示.
总是使用分页器.
启用计时功能.
psql (17.6)
输入 "help" 来获取帮助信息.

postgres@localhost:5432 postgresql_book#

使用别名 eav_q_pgtables 运行上面的分析查询语句. 注意别名前要加分号.

postgres@localhost:5432 postgresql_book# :eav_q_pgtables
                                                                                                                                                                                                                                                    QUERY PLAN                                                                                                                                                 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=19.36..19.37 rows=1 width=8) (actual time=0.320..0.321 rows=1 loops=1)
   Output: count(*)
   ->  Seq Scan on pg_catalog.pg_class c  (cost=0.00..19.19 rows=68 width=0) (actual time=0.044..0.289 rows=75 loops=1)
         Output: c.oid, c.relname, c.relnamespace, c.reltype, c.reloftype, c.relowner, c.relam, c.relfilenode, c.reltablespace, c.relpages, c.reltuples, c.relallvisible, c.reltoastrelid, c.relhasindex, c.relisshared, c.relpersistence, c.relkind, c.relnatts, c.relchecks, c.relhasrules, c.relhastriggers, c.relhassubclass, c.relrowsecurity, c.relforcerowsecurity, c.relispopulated, c.relreplident, c.relispartition, c.relrewrite, c.relfrozenxid, c.relminmxid, c.relacl, c.reloptions, c.relpartbound
         Filter: (c.relkind = ANY ('{r,p}'::"char"[]))
         Rows Removed by Filter: 359
 Planning Time: 13.548 ms
 Execution Time: 0.486 ms
(8 行记录)


时间：17.672 ms
postgres@localhost:5432 postgresql_book#

对于使用 \set 设置的 psql 的配置变量, 都可以使用 \unset 删除或将其设置回默认值. 例如删除 psqlrc.conf 中定义的 eav_q_pgtables 别名.

postgres@localhost:5432 postgresql_book# \unset eav_q_pgtables
postgres@localhost:5432 postgresql_book# :eav_q_pgtables
postgresql_book-# ;
错误:  语法错误 在 ":" 或附近的
第1行:eav_q_pgtables
     ^
时间：7.861 ms
postgres@localhost:5432 postgresql_book#

历史命令

取出前面执行过的命令行

跟许多命令行工具一样, psql 中也可以通过上下方向键快速找出之前执行过的历史命令.

Linux/Unix 操作系统中可以保持历史命令.

使用 HISTFILE 环境变量将历史命令记录到指定文件中以备后查.

执行 shell 命令

执行 shell 命令

psql 中通过 \! 可以直接执行操作系统命令. 有两种方式运行 shell 命令.

输入 \! 后回车

输入 \! 后回车, 则转到操作系统命令行界面.

Linux 下:

postgres@[local]:5432 postgres# \!
postgres@LAPTOP-Q34L5TP8:~>

Windows 下:

postgres@localhost:5432 postgresql_book# \!
Microsoft Windows [版本 10.0.19045.6396]
(c) Microsoft Corporation。保留所有权利。

D:\work\cs\Database\postgres\book>

输入 exit 并回车, 则返回到 psql 界面.

postgres@LAPTOP-Q34L5TP8:~> exit
exit
postgres@[local]:5432 postgres#

D:\work\cs\Database\postgres\book>exit
postgres@localhost:5432 postgresql_book#

\! 后跟 shell 命令

haife@localhost:5432 postgresql_book# \! dir
 驱动器 D 中的卷是 计算机
 卷的序列号是 9C68-468A

 D:\work\cs\Database\postgres 的目录

2025/09/29  09:23    <DIR>          .
2025/09/29  09:23    <DIR>          ..
2025/10/10  09:08    <DIR>          book
2025/09/24  10:46    <DIR>          bpsimple
2025/09/24  10:46             2,053 bpsimple.7z
2025/09/25  17:01               396 create_script.sql
2025/09/25  16:44             1,207 create_table_script.sql
2017/10/21  13:31           495,224 nginx+mapserver-20171021-13_31 - 副本.docx
2017/10/22  23:10           802,529 nginx+mapserver-20171022-00_18 - 副本.docx
2017/10/22  22:54         1,398,018 nginx+mapserver-20171022-00_18.pdf
2017/10/20  23:31             4,415 nginx.conf
2025/09/25  08:31    <DIR>          psql_history
2025/09/26  13:16    <DIR>          sakila
2025/09/18  22:26    <DIR>          sowya
2025/09/29  16:01    <DIR>          testbar
2017/10/20  23:40             3,741 如何登录noooin.com.txt
2017/10/20  23:30                 0 新建文本文档.txt.bak
               9 个文件      2,707,583 字节

watch命令

使用watch命令重复执行语句

\watch 命令是 PostgreSQL 9.3 版中为 psql 引入的一项新功能. 它可以实现以固定的频率反复执行某个语句, 以便持续观察其输出.

例. 查询所有数据库连接上的活跃负载.

haife@localhost:5432 postgresql_book# SELECT datname, waiting, query
postgresql_book-> FROM pg_stat_activity
postgresql_book-> WHERE state='active' AND pid != pg_backend_pid();
错误:  字段 "waiting" 不存在
第1行SELECT datname, waiting, query
                     ^
时间：80.994 ms
haife@localhost:5432 postgresql_book#

PostgreSQL 17.6, pg_stat_activity 表中没有 waiting 这个属性.

haife@localhost:5432 postgresql_book# \d pg_stat_activity
                   视图 "pg_catalog.pg_stat_activity"
       栏位       |           类型           | 校对规则 | 可空的 | 预设
------------------+--------------------------+----------+--------+------
 datid            | oid                      |          |        |
 datname          | name                     |          |        |
 pid              | integer                  |          |        |
 leader_pid       | integer                  |          |        |
 usesysid         | oid                      |          |        |
 usename          | name                     |          |        |
 application_name | text                     |          |        |
 client_addr      | inet                     |          |        |
 client_hostname  | text                     |          |        |
 client_port      | integer                  |          |        |
 backend_start    | timestamp with time zone |          |        |
 xact_start       | timestamp with time zone |          |        |
 query_start      | timestamp with time zone |          |        |
 state_change     | timestamp with time zone |          |        |
 wait_event_type  | text                     |          |        |
 wait_event       | text                     |          |        |
 state            | text                     |          |        |
 backend_xid      | xid                      |          |        |
 backend_xmin     | xid                      |          |        |
 query_id         | bigint                   |          |        |
 query            | text                     |          |        |
 backend_type     | text                     |          |        |


haife@localhost:5432 postgresql_book#

haife@localhost:5432 postgresql_book# SELECT datname, wait_event, query
postgresql_book-> FROM pg_stat_activity
postgresql_book-> WHERE state='active' AND pid != pg_backend_pid();
 datname | wait_event | query
---------+------------+-------
(0 行记录)

例. 每10秒钟查询一次所有数据库连接上的活跃负载.

haife@localhost:5432 postgresql_book# SELECT datname, wait_event, query
postgresql_book-> FROM pg_stat_activity
postgresql_book-> WHERE state='active' AND pid != pg_backend_pid(); \watch 10
 datname | wait_event | query
---------+------------+-------
(0 行记录)


时间：3.071 ms
 2025/10/10 13:11:48 (每 10s)

 datname | wait_event | query
---------+------------+-------
(0 行记录)

时间：2.487 ms
 2025/10/10 13:11:58 (每 10s)

 datname | wait_event | query
---------+------------+-------
(0 行记录)

时间：2.605 ms
 2025/10/10 13:12:08 (每 10s)

 datname | wait_event | query
---------+------------+-------
(0 行记录)

时间：2.930 ms

如果需要终止 watch 进程, 请执行 CTRL+x 加 CTRL+c. 事实上, Ctrl+c 就可以终止.

注意

注意: watch 动作只能在 psql 交互模式下使用.

除了监控类语句, \watch 命令也可以重复执行其他查询语句. 事实上 \watch 命令并不关心重复执行的语句内容是什么.

例. 每5秒记录一次系统负载情况.

haife@localhost:5432 postgresql_book# SELECT * INTO log_activity FROM pg_stat_activity;
SELECT 6
时间：119.475 ms

上面的语句以 pg_stat_activity 为模板新建一张结构与数据都完全相同的 log_activity 表.

haife@localhost:5432 postgresql_book# \d
                  关联列表
 架构模式 |     名称     |  类型  |  拥有者
----------+--------------+--------+----------
 public   | log_activity | 数据表 | haife
 public   | test_scores  | 数据表 | postgres
(2 行记录)

haife@localhost:5432 postgresql_book# select datid, datname, wait_event, query from log_activity;
 datid |     datname     | wait_event |                       query
-------+-----------------+------------+---------------------------------------------------
 16480 | postgresql_book | NULL       | SELECT * INTO log_activity FROM pg_stat_activity;
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
(6 行记录)

每5秒执行一次.

haife@localhost:5432 postgresql_book# INSERT INTO log_activity SELECT * FROM pg_stat_activity; \watch 5
INSERT 0 6
时间：43.338 ms
INSERT 0 6
时间：4.010 ms
INSERT 0 6
时间：41.918 ms
INSERT 0 6
时间：41.634 ms
INSERT 0 6
时间：41.455 ms
INSERT 0 6
时间：41.050 ms

haife@localhost:5432 postgresql_book# select datid, datname, wait_event, query from log_activity;
 datid |     datname     | wait_event |                          query
-------+-----------------+------------+----------------------------------------------------------
 16480 | postgresql_book | NULL       | SELECT * INTO log_activity FROM pg_stat_activity;
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
 16480 | postgresql_book | NULL       | INSERT INTO log_activity SELECT * FROM pg_stat_activity;
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
 16480 | postgresql_book | NULL       | INSERT INTO log_activity SELECT * FROM pg_stat_activity;
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
 16480 | postgresql_book | NULL       | INSERT INTO log_activity SELECT * FROM pg_stat_activity;
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
 16480 | postgresql_book | NULL       | INSERT INTO log_activity SELECT * FROM pg_stat_activity;
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
 16480 | postgresql_book | NULL       | INSERT INTO log_activity SELECT * FROM pg_stat_activity;
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
 16480 | postgresql_book | NULL       | INSERT INTO log_activity SELECT * FROM pg_stat_activity;
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
  NULL | NULL            | NULL       | 
(42 行记录)


时间：1.090 ms
haife@localhost:5432 postgresql_book#

显示对象信息

显示对象信息

有多条 psql 命令都能用于显示数据库对象列表, 并附带给出每个对象的详细信息.

使用 \dt+ 命令列出表信息.

haife@localhost:5432 postgresql_book# \dt+ pg_catalog.pg_t*
                                         关联列表
  架构模式  |       名称       |  类型  |  拥有者  | 持续的 | 访问方法 |    大小    | 描述
------------+------------------+--------+----------+--------+----------+------------+------
 pg_catalog | pg_tablespace    | 数据表 | postgres | 永久的 | heap     | 48 kB      | NULL
 pg_catalog | pg_transform     | 数据表 | postgres | 永久的 | heap     | 0 bytes    | NULL
 pg_catalog | pg_trigger       | 数据表 | postgres | 永久的 | heap     | 8192 bytes | NULL
 pg_catalog | pg_ts_config     | 数据表 | postgres | 永久的 | heap     | 40 kB      | NULL
 pg_catalog | pg_ts_config_map | 数据表 | postgres | 永久的 | heap     | 56 kB      | NULL
 pg_catalog | pg_ts_dict       | 数据表 | postgres | 永久的 | heap     | 48 kB      | NULL
 pg_catalog | pg_ts_parser     | 数据表 | postgres | 永久的 | heap     | 40 kB      | NULL
 pg_catalog | pg_ts_template   | 数据表 | postgres | 永久的 | heap     | 40 kB      | NULL
 pg_catalog | pg_type          | 数据表 | postgres | 永久的 | heap     | 160 kB     | NULL
(9 行记录)

通过 \d+ 命令得到对象的详细信息.

haife@localhost:5432 postgresql_book# \dt+ pg_ts_dict
                                    关联列表
  架构模式  |    名称    |  类型  |  拥有者  | 持续的 | 访问方法 | 大小  | 描述
------------+------------+--------+----------+--------+----------+-------+------
 pg_catalog | pg_ts_dict | 数据表 | postgres | 永久的 | heap     | 48 kB | NULL
(1 行记录)

使用psql制作简单的报表

使用psql制作简单的报表

psql 命令的 -H 或 --html 选项是 HTML 输出模式. -c 后跟要运行的 SQL 或内部命令, 或写成 --command=命令. -o 指定输出文件名(或 --output=FILENAME).

将 pg_settings 表中类别名(category)中含有 Query 的分组排序输出, 每组统计数目, 该属性命名为 num_per_category, 其中按类别名(category)排序. 输出到文件 test.html.

PS D:\work\cs\Database\postgres\book> psql -d postgresql_book -H -c "SELECT category, count(*) AS num_per_category FROM
>> pg_settings
>> WHERE category LIKE '%Query%'
>> GROUP BY category
>> ORDER BY category;" -o test.html

用浏览器打开此文件, 效果如下

  

    
category
    
num_per_category
  
  

    
Statistics / Cumulative Query and Index Statistics
    
7
  

(1 行记录)

以下是 test.html 的内容

<table border="1">
  <tr>
    <th align="center">category</th>
    <th align="center">num_per_category</th>
  </tr>
  <tr valign="top">
    <td align="left">Statistics / Cumulative Query and Index Statistics</td>
    <td align="right">7</td>
  </tr>
</table>
<p>(1 行记录)<br />

复杂 HTML 报表

复杂 HTML 报表

编写复杂的 HTML 报表需要脚本来辅助. 将下面的内容存储到文件 settings_report.psql.

\o settings_report.html
-- 指定输出文件名, 即将查询结果输出到此文件中.

\T 'cellspacing=0 cellpadding=0'
-- \T 后跟字符串, 用于设置HTML. 这里设置表格的输出格式.
-- cellspacing 属性用于控制表格中单个单元格之间的距离
-- cellpadding 属性用于控制内容和外围边界之间的距离


\qecho '<html><head><style>H2{color:maroon}</style>'
-- \qecho 后面跟字符串, 功能是将该字符串写入到查询输出流, 该命令等效于 \echo, 区别是这里将所有输出写入到由\o指定的输出通道中

\qecho '<title>PostgreSQL Settings</title></head><body>'
\qecho '<table><tr valign=''top''><td><h2>Planner Settings</h2>'

\x on 
-- 切换扩展模式, 打开记录输出的展开模式. 即重复每一个记录的列标题, 并将每一个记录的每一列作为一个单独的记录输出.

\t on
-- 设置“是否仅输出记录”开关. 如果此开关打开, 则会忽略列标题和行计数.

\pset format html
-- 指定按 HTML 格式输出结果

SELECT category, string_agg(name || '=' || setting, E'\n' ORDER BY name) As settings
FROM pg_settings
WHERE category LIKE '%Planner%'
GROUP BY category
ORDER BY category;

\H 
-- 切换为 HTML 输出模式

\qecho '</td><td><h2>File Locations</h2>'
\x off
\t on
\pset format html

SELECT name, setting FROM pg_settings WHERE category = 'File Locations' ORDER BY name;

\qecho '<h2>Memory Settings</h2>'

SELECT name, setting, unit FROM pg_settings WHERE category ILIKE '%memory%' ORDER BY name;
-- 这里 ILIKE 中的 I 是大小写不敏感的意思.

\qecho '</td></tr></table>'
\qecho '</body></html>'
\o

在操作系统的命令行界面上运行 psql -f settings_report.psql 即可得到文件 settings_report.html

下面是 settings_report.html 在浏览器中显示的样子.

也可以在 psql 的交互模式下连接数据库并运行上面的代码. 即使用 \i settings_report.psql 运行此文件中的命令.

End

Thanks very much!