PostgreSQL psql copy

1 背景知识

PostgreSQL COPY 命令是一个强大的工具，可用于将数据从各种格式的文件中快速加载到数据库表中。它支持多种数据格式，包括 JSON。jq 是一个命令行工具，可以用于处理和转换 JSON 数据。结合使用 PostgreSQL COPY 和 jq，我们可以轻松地将 JSON 数据集直接上传到 PostgreSQL 表中。

2 环境准备

• 安装 PostgreSQL
• 安装 jq

3 监控copy 命令导出和导入进度

pg_stat_progress_copy 视图可以监控copy 命令的导出和导入进度。这里提供了一个较为好用的SQL 语句查询。以便监控导入和导出进度

SELECT pid ,datid,datname,relid,
command,pg_size_pretty(bytes_processed) ,
pg_size_pretty(bytes_total) 
FROM pg_stat_progress_copy ; \watch 2

4 使用copy 命令导入和导出JSON 文件

4.1 创建一个 PostgreSQL 表来存储 JSON 数据。

CREATE TABLE json_data (
  id SERIAL PRIMARY KEY,
  json_data JSONB
);

4.2 将 JSON 数据集转换为 CSV 格式。

我们可以使用 jq 将 JSON 数据集转换为 CSV 格式。CSV 格式是一种简单的文本格式，很容易被 PostgreSQL COPY 命令解析。

jq -rc --stream 'fromstream(1|truncate_stream(inputs)) | .[] | tostream' data.json > data.csv

4.3 使用 PostgreSQL COPY 命令将 CSV 文件中的数据加载到表中。

COPY json_data (json_data)
FROM 'data.csv'
DELIMITER ','
CSV HEADER;

4.4 验证数据是否已成功加载。

我们可以使用以下查询来验证数据是否已成功加载到表中：

SELECT * FROM json_data;

4.5 优点

使用 PostgreSQL COPY 和 jq 直接上传 JSON 数据集到 PostgreSQL 表具有以下优点：

• 快速：COPY 命令是一种非常高效的数据加载工具，可以快速地将大量数据加载到表中。
• 灵活：COPY 命令支持多种数据格式，包括 JSON、CSV、XML 等。
• 方便：jq 是一个强大的 JSON 处理工具，可以轻松地将 JSON 数据集转换为 CSV 格式。

4.6 缺点

使用 PostgreSQL COPY 和 jq 直接上传 JSON 数据集到 PostgreSQL 表也存在一些缺点：

• 需要一定的技术技能：使用 COPY 命令和 jq 需要一定的技术技能，可能对新手来说比较困难。
• 可能存在数据完整性问题：如果 CSV 文件中的数据格式不正确，可能会导致数据完整性问题。

4.7 结论

使用 PostgreSQL COPY 和 jq 直接上传 JSON 数据集到 PostgreSQL 表是一种快速、灵活且方便的方法。但是，它也需要一定的技术技能，并且可能存在数据完整性问题。在使用此方法之前，请务必仔细考虑您的具体情况。

5 参考连接