如何获得阵列列的维数? [英] How to get the dimensionality of an ARRAY column?
问题描述
我正在一个项目中,该项目直接从数据库中收集有关您的架构的信息。我可以使用 information_schema.columns
来获取列的 data_type
,它会告诉我它是否是 ARRAY
与否。我还可以获得 ARRAY的基础类型(
整数
, bytea
等)通过查询 information_schema.element_types
来确定code>,如下所述:
I'm working on a project that collects information about your schema from the database directly. I can get the data_type
of the column using information_schema.columns
, which will tell me if it's an ARRAY
or not. I can also get the underlying type (integer
, bytea
etc) of the ARRAY
by querying information_schema.element_types
as described here:
https://www.postgresql.org/docs/9.1/static/infoschema-element-types.html
我的问题是我还需要知道数组有多少维,是否为 integer []
或 integer [] []
。有人知道这样做的方法吗? Google在这里并不是很有帮助,希望对Postgres规范更熟悉的人可以引导我朝正确的方向发展。
My problem is that I also need to know how many dimensions the array has, whether it is integer[]
, or integer[][]
for example. Does anyone know of a way to do this? Google isn't being very helpful here, hopefully someone more familiar with the Postgres spec can lead me in the right direction.
推荐答案
对于初学者,数组的维数不会反映在Postgres中的数据类型中。语法 integer [] []
是可以接受的,但实际上它只是内部的 integer []
。
在此处阅读该手册。
For starters, the dimensionality of an array is not reflected in the data type in Postgres. The syntax integer[][]
is tolerated, but it's really just integer[]
internally.
Read the manual here.
这意味着维度可以在同一数据类型(同一表列)内变化。
This means, that dimensions can vary within the same data type (the same table column).
获取实际维度特定数组的 value :
To get actual dimensions of a particular array value:
SELECT array_dims(my_arr); -- [1:2][1:3]
或者仅获取维数:
SELECT array_ndims(my_arr); -- 2
有更多满足类似需求的数组函数。请参见手册中的数组函数表。
There are more array functions for similar needs. See table of array functions in the manual.
相关:
- Use string[][] with ngpsql
如果您需要在列中强制使用特定尺寸,请添加 检查
约束。强制执行二维数组:
If you need to enforce particular dimensions in a column, add a CHECK
constraint. To enforce 2-dimensional arrays:
ALTER TABLE tbl ADD CONSTRAINT tbl_arr_col_must_have_2_dims
CHECK (array_ndims(arr_col) = 2);
这篇关于如何获得阵列列的维数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!