使用 PostgreSQL 命令的三种方法

在PostgreSQL 入门中，我解释了如何安装、设置和开始使用开源数据库软件。但是，您可以使用 PostgreSQL 中的命令执行更多操作。

例如，我使用 Postgres 来跟踪我的杂货购物清单。我家的大部分杂货购物都是我做的，而且大部分都是每周一次。我去好几个地方购买清单上的东西，因为每家商店都提供特定的选择或质量，或者可能更优惠的价格。最初，我制作了一个 HTML 表单页面来管理我的购物清单，但是它无法保存我的条目。所以，我不得不等到一次性列出我的清单，到那时我通常会忘记一些我们需要或想要的东西。

相反，使用 PostgreSQL，我可以在一周内想到什么就输入什么，并在购物前打印出整个清单。以下是您也可以这样做的方法。

创建一个简单的购物清单

首先，使用 psql 命令进入数据库，然后使用以下命令为您的清单创建一个表

Create table groc (item varchar(20), comment varchar(10));

键入如下命令以将项目添加到您的清单

insert into groc values ('milk', 'K');
insert into groc values ('bananas', 'KW');

括号内有两个信息（用逗号分隔）：您要购买的商品以及指示您要在哪里购买它的字母，以及它是否是您通常每周购买的商品 (W)。

由于 psql 具有历史记录，您可以按向上箭头键并编辑括号之间的数据，而不必为每个项目键入整行。

输入一些项目后，使用以下命令检查您输入的内容

Select * from groc order by comment;

      item      | comment 
----------------+---------
 ground coffee  | H
 butter         | K
 chips          | K
 steak          | K
 milk           | K
 bananas        | KW
 raisin bran    | KW
 raclette       | L
 goat cheese    | L
 onion          | P
 oranges        | P
 potatoes       | P
 spinach        | PW
 broccoli       | PW
 asparagus      | PW
 cucumber       | PW
 sugarsnap peas | PW
 salmon         | S
(18 rows)

此命令按注释列对结果进行排序，以便项目按您购买它们的地点分组，以便于购物。

通过使用 W 来指示您的每周购买商品，您可以在清除表格以准备下周的清单时，将您的每周商品保留在清单上。为此，请输入

delete from groc where comment not like '%W';

请注意，在 PostgreSQL 中，% 是通配符（而不是星号）。因此，为了节省打字，您可能会键入

delete from groc where item like 'goat%';

您不能使用 item = 'goat%'；它不起作用。

当您准备好购物时，输出您的清单以打印或发送到您的手机，使用

\o groclist.txt
select * from groc order by comment;
\o

最后一个命令 \o，后面没有任何内容，将输出重置为命令行。否则，所有输出将继续发送到您创建的 groc 文件。

分析复杂表格

对于短表格，这种逐项输入可能还可以，但是对于非常大的表格呢？几年前，我正在帮助 FreieFarbe.de 团队创建一个免费颜色色卡簿（freieFarbe 在德语中意为“免费颜色”），该色卡簿来自其 HLC 调色板，实际上任何可以想象到的印刷颜色都可以通过其色调、亮度（亮度）和色度（饱和度）来指定。结果是 HLC 颜色图谱，以下是我们如何做到这一点的。

该团队向我发送了包含颜色规范的文件，以便我可以编写 Python 脚本，这些脚本可以与 Scribus 一起使用，以轻松生成色块色卡簿。一个例子开始如下

HLC, C, M, Y, K
H010_L15_C010, 0.5, 49.1, 0.1, 84.5
H010_L15_C020, 0.0, 79.7, 15.1, 78.9
H010_L25_C010, 6.1, 38.3, 0.0, 72.5
H010_L25_C020, 0.0, 61.8, 10.6, 67.9
H010_L25_C030, 0.0, 79.5, 18.5, 62.7
H010_L25_C040, 0.4, 94.2, 17.3, 56.5
H010_L25_C050, 0.0, 100.0, 15.1, 50.6
H010_L35_C010, 6.1, 32.1, 0.0, 61.8
H010_L35_C020, 0.0, 51.7, 8.4, 57.5
H010_L35_C030, 0.0, 68.5, 17.1, 52.5
H010_L35_C040, 0.0, 81.2, 22.0, 46.2
H010_L35_C050, 0.0, 91.9, 20.4, 39.3
H010_L35_C060, 0.1, 100.0, 17.3, 31.5
H010_L45_C010, 4.3, 27.4, 0.1, 51.3

这与原始版本略有不同，原始版本使用制表符分隔数据。我将其转换为 CSV（逗号分隔值）文件，我更喜欢将其与 Python 一起使用。（CSV 文件也非常有用，因为它们可以轻松导入到电子表格程序中。）

在每一行中，第一个项目是颜色名称，后面是其 C、M、Y 和 K 颜色值。该文件包含 1,793 种颜色，我想要一种分析信息以了解值范围的方法。这就是 PostgreSQL 发挥作用的地方。我不想手动输入所有这些数据——我认为我可能会出错（并且头痛）。幸运的是，PostgreSQL 有一个用于此的命令。

我的第一步是使用以下命令创建数据库

Create table hlc_cmyk (color varchar(40), c decimal, m decimal, y decimal, k decimal);

然后我使用以下命令导入数据

\copy  hlc_cmyk from '/home/gregp/HLC_Atlas_CMYK_SampleData.csv' with (header, format CSV);

开头的反斜杠在那里是因为使用普通的 copy 命令仅限于 root 用户和 Postgres 超级用户。在括号中，header 表示第一行包含标题，应该被忽略，CSV 表示文件格式是 CSV。请注意，在这种方法中，颜色名称周围不需要括号。

如果操作成功，我将看到一条消息，显示 COPY NNNN，其中 N 指的是插入到表中的行数。

最后，我可以使用以下命令查询表

select * from hlc_cmyk;

     color     |   c   |   m   |   y   |  k   
---------------+-------+-------+-------+------
 H010_L15_C010 |   0.5 |  49.1 |   0.1 | 84.5
 H010_L15_C020 |   0.0 |  79.7 |  15.1 | 78.9
 H010_L25_C010 |   6.1 |  38.3 |   0.0 | 72.5
 H010_L25_C020 |   0.0 |  61.8 |  10.6 | 67.9
 H010_L25_C030 |   0.0 |  79.5 |  18.5 | 62.7
 H010_L25_C040 |   0.4 |  94.2 |  17.3 | 56.5
 H010_L25_C050 |   0.0 | 100.0 |  15.1 | 50.6
 H010_L35_C010 |   6.1 |  32.1 |   0.0 | 61.8
 H010_L35_C020 |   0.0 |  51.7 |   8.4 | 57.5
 H010_L35_C030 |   0.0 |  68.5 |  17.1 | 52.5

对于所有 1,793 行数据，它都像这样继续。回想起来，我不能说这个查询对于 HLC 和 Scribus 任务绝对必要，但它减轻了我对该项目的一些焦虑。

为了生成 HLC 颜色图谱，我自动化了使用 Scribus 为这些色板页面的 13,000 多种颜色创建颜色图表的过程。

我可以使用 copy 命令输出我的数据

\copy hlc_cmyk to '/home/gregp/hlc_cmyk_backup.csv' with (header, format CSV);

我还可以使用 where 子句根据某些值限制输出。

例如，以下命令将仅发送以 H10 开头的色调的表值。

\copy hlc_cmyk to '/home/gregp/hlc_cmyk_backup.csv' with (header, format CSV) where color like 'H10%';

备份或传输数据库或表

我在这里要提到的最后一个命令是 pg_dump，它用于备份 PostgreSQL 数据库，并在 psql 控制台之外运行。例如

pg_dump gregp -t hlc_cmyk > hlc.out
pg_dump gregp > dball.out

第一行导出 hlc_cmyk 表及其结构。第二行转储 gregp 数据库中的所有表。这对于备份或传输数据库或表非常有用。

要将数据库或表传输到另一台计算机，首先，在另一台计算机上创建一个数据库（有关详细信息，请参阅我的“入门”文章），然后执行相反的过程

psql -d gregp -f dball.out

这将一步创建所有表并输入数据。

结论

在本文中，我们已经了解了如何使用 WHERE 参数来限制操作，以及 PostgreSQL 通配符 % 的用法。我们还了解了如何将大量数据加载到表中，然后将部分或全部表数据输出到文件，甚至包括所有单个表的整个数据库。