PostgreSQL中的数组数组 [英] Array of arrays in PostgreSQL

问题描述

我在PostgreSQL的hstore类型上使用%%运算符，该运算符将hstore(有效的键值类型)转换为数组，其元素交替为{{key，value}，{key value}}.

I'm using the %% operator on PostgreSQL's hstore type which converts a hstore (key-value type effectively) into an array whose elements alternate {{key, value}, {key value}}.

当我想返回这些扁平化的hstore的数组时，出现以下错误:could not find array type for data type text[]由于PostgreSQL缺少对数组数组的支持.

When I want to return array of these flattened hstores I get this error: could not find array type for data type text[]due to PostgreSQL lack of support for an array of arrays.

从好奇心的角度来看，有人知道为什么不支持这些吗?更重要的是，这种情况是否可以解决?

From a curiosity standpoint, does anyone know why these are not supported? And more importantly, is there a work around for this type of scenario?

此刻，我将结果串联成一个字符串(用逗号分隔)，并在应用程序端(C#和NPGSQL)对其进行解析.但是，这种方法感觉不太正确，我希望能够以.NET数组或键值数组等形式读回行.

At the moment I'm concatenating the results into a string (comma separated) and parsing them on the application (C# and NPGSQL) side. However, this approach doesn't feel quite right, I'd like to be able to read the row back as a .NET array of arrays or array of key-values etc.

非常感谢.

推荐答案

从好奇心的角度来看，有人知道为什么不支持这些吗?

From a curiosity standpoint, does anyone know why these are not supported?

一个通用的答案是因为数组本质上是反关系的.删除重复值是实现第一范式的方法.从关系理论的角度来看，拥有重复基团似乎是很疯狂的.

One generic answer is because arrays are intrinsically anti-relational. Removing repeating values is how you achieve 1st normal form. To have repeating groups of repeating groups seems quite insane from a relational theoretical standpoint.

通常，关系正确的事情是为您的重复值提取一个表.因此，如果您建模如下:

In general, the relationally-correct thing to do is to extract a table for your repeating values. So if you modeled something like this:

CREATE TABLE users (
  id integer primary key,
  name varchar,
  favorite_colors varchar[],
  ...
);

您应该像这样重新定义这种关系:

it would behoove you to redefine this relationally like so:

CREATE TABLE users (
  id integer primary key,
  name varchar,
  ...
);

CREATE TABLE favorite_colors (
  user_id integer references users,
  color varchar
);

甚至:

CREATE TABLE users (
  id integer primary key,
  name varchar,
  ...
);

CREATE TABLE colors (
  color varchar primary key
);

CREATE TABLE favorite_colors (
  user_id integer references users,
  color varchar references colors,
  primary key (user_id, color)
);

Hstore支持许多功能，其中许多功能使将其轻松集成到关系世界视图中变得很容易.我认为解决问题的最简单方法是使用each函数将hstore值转换为关系，然后可以像正常值集一样使用这些关系.无论如何，这就是您解决在其他数据库中具有多个值的方法:查询和使用结果集.

Hstore supports a lot of functions, many of which would make it easy to integrate it into a relational worldview. I think the simplest way to solve your problem would be to use the each function to convert your hstore values into relations you can then use like a normal set of values. This is how you address having multiple values in other databases anyway: querying, and working with result sets.

这篇关于PostgreSQL中的数组数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！