我正在尝试计算 postgres 数据库中 NUMERIC 列的最大长度。数据库中有许多表,其中大多数表包含许多数字列。
我正在将相当大量的 json 数据导入数据库。如果目标列精度/小数位数小于输入的,则 SQLModel 或 pydantic 无法插入数字字段。目前,我正在将数据播种到通用NUMERIC(16,5)
列中,但我想通过优化列大小来减少存储空间。(我的是一个半只读数据集,以后列的大小不会有太大差异)
作为参考,以下是我解决问题的失败......
SELECT
table_schema,
TABLE_NAME,
COLUMN_NAME,
(
xpath (
'/row/max/text()',
query_to_xml (
format (
'SELECT LENGTH ( CAST ( MAX ( %I ) AS CHARACTER VARYING ( 40 ) ) ) from %I.%I',
COLUMN_NAME,
table_schema,
TABLE_NAME
),
TRUE,
TRUE,
''
)
)
) [ 1 ] :: TEXT :: INT AS max_length
FROM
information_schema.COLUMNS
WHERE
table_schema = 'public'
AND data_type = 'numeric'
ORDER BY
table_schema,
TABLE_NAME,
COLUMN_NAME;
更好的方法是将最大列长度拆分为精度和比例。
由于数字来自外部来源 - 检查这些数字的最佳位置是在数据库之外。使用任何语言、脚本或编译语言来扫描 JSON 文件。您还可以使用诸如
jq
提取字段和查找最大值之类的工具。PostgreSql 本身可以处理高达荒谬数量的数值。
但是如果
numeric(16,5)
现在对你有用,那么你可以用bigint
数据类型替换它并将值存储为整数。只是不要忘记在显示之前在客户端上将其除以 10e+5。在速度方面,您会得到改进——大小bigint
仅为 8 个字节,并且由 64 位处理器原生支持。也可以将值拆分为两个整数,但如果您的值确实需要 (16,5),则
integer
数据类型可能不足以满足值的整数部分。4 个字节integer
不能容纳 10e+11 个值,所以无论如何你都需要去bigint
。在您展示的示例代码中,您使用的是 XML,但之前您声明数据以 JSON 形式提供。那么是哪一个呢?如果需要,要解析数据库中的 JSON blob,可以使用JSON 函数。或者这是一个错误,您的数据实际上是 XML 格式的?
好吧,我让它工作了。修复了列名 (
length
):有没有办法将上述值拆分为数字比例和精度?