Rails 4使用postgresql列进行数组数据类型查询时出现错误

Question

我正在尝试在Rails 4中查询一个包含PostgreSQL数组数据类型列的表。

以下是表模式：

create_table "db_of_exercises", force: true do |t|
    t.text     "preparation"
    t.text     "execution"
    t.string   "category"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.string   "name"
    t.string   "body_part",      default: [], array: true
    t.hstore   "muscle_groups"
    t.string   "equipment_type", default: [], array: true
  end

以下查询有效：

SELECT * FROM db_of_exercises WHERE ('Arms') = ANY (body_part);

然而，这个查询并没有：

SELECT * FROM db_of_exercises WHERE ('Arms', 'Chest') = ANY (body_part);

它会抛出以下错误：

ERROR:  operator does not exist: record = character varying

这对我也不起作用：

SELECT * FROM "db_of_exercises" WHERE "body_part" IN ('Arms', 'Chest');

这会导致出现以下错误：

ERROR:  array value must start with "{" or dimension information

那么，在 ActiveRecord 中，我该如何查询一个包含数组数据类型的列呢？

我现在拥有的是：

@exercises = DbOfExercise.where(body_part: params[:body_parts])

我希望能查询与多个身体部位相关联的记录，这也是使用数组数据类型的整个目的，如果有人能够告诉我如何做到这一点，那就太好了。但我在文档中没有看到这个方法。

最终解决方案为：使用重叠运算符(&&)：

SELECT * FROM db_of_exercises WHERE ARRAY['Arms', 'Chest'] && body_part;

我遇到了这个错误：

ERROR:  operator does not exist: text[] && character varying[]

我将ARRAY ['Arms'，'Chest']强制转换为varchar：

 SELECT * FROM db_of_exercises WHERE ARRAY['Arms', 'Chest']::varchar[] && body_part;

然后那个起作用了。

- kwyoung11

---

感谢您付出努力撰写详细的问题，包括您的代码、精确的错误信息等。始终包含您的PostgreSQL版本 (SELECT version()) 是很好的习惯，尽管在这种情况下似乎并不重要。 - Craig Ringer

Answer 1

我认为这与Rails无关。

如果你按照以下步骤操作会怎样呢？

SELECT * FROM db_of_exercises WHERE 'Arms' = ANY (body_part) OR 'Chest' = ANY (body_part)

我知道Rails 4支持Postgresql数组数据类型，但我不确定ActiveRecord是否创建了用于查询该数据类型的新方法。也许你可以使用Array Overlap，我的意思是&&运算符，然后做一些像下面这样的事情：

WHERE ARRAY['Arms', 'Chest'] && body_part

或者您可以看一下这个宝石：https://github.com/dockyard/postgres_ext/blob/master/docs/querying.md

然后执行以下查询：

DBOfExercise.where.overlap(:body_part => params[:body_parts])

注：该查询与身体部位重叠的练习将被返回。

- Aguardientico

---

是的，那样是可行的，但我应该更具体。实际上，我正在寻找如何使用ActiveRecord进行查询，就像这样：DbOfExercise.where(body_part: params[:body_parts])。我事先不知道用户将选择哪些值，因此使用OR语句似乎不可行。我已经编辑了我的问题。 - kwyoung11

数组重叠肯定是你想要的。对于多值左侧，= ANY 没有意义。 - Craig Ringer

+1 对于 postgres_ext。解决了在Rails中进行查询的问题。 - JacobEvelyn

Answer 2

@Aguardientico说得对，你需要的是数组重叠运算符&&。我接下来会提供更多解释，但我希望您接受那个答案，而不是这个。

匿名行（记录）

('item1', 'item2', ...) 构造函数是一个 行构造函数，除非它出现在一个 IN (...) 列表中。它创建了一个匿名行，PostgreSQL 称之为“记录”。错误信息：

ERROR:  operator does not exist: record = character varying

这是因为('Arms', 'Chest')被解释为ROW('Arms', 'Chest')，从而产生了单个record值：

craig=> SELECT ('Arms', 'Chest'), ROW('Arms', 'Chest'), pg_typeof(('Arms', 'Chest'));
     row      |     row      | pg_typeof 
--------------+--------------+-----------
 (Arms,Chest) | (Arms,Chest) | record
(1 row)

并且PostgreSQL不知道应该如何将其与字符串进行比较，我不是很喜欢这种行为。我更希望PostgreSQL在您想要匿名行时需要明确使用ROW()构造函数。我认为此处所示的行为是为了支持SET(col1,col2,col3) = (val1,val2,val3)和其他类似操作而存在的，其中ROW(...)构造函数不太合适。

但是单个项目的情况下同样有效吗？

单个('Arms')的情况之所以有效，是因为除非有逗号，否则它只是一个括号中的单个值，括号是多余的，可以忽略：

craig=> SELECT ('Arms'), ROW('Arms'), pg_typeof(('Arms')), pg_typeof(ROW('Arms'));
 ?column? |  row   | pg_typeof | pg_typeof 
----------+--------+-----------+-----------
 Arms     | (Arms) | unknown   | record
(1 row)

不要因为类型为unknown而感到惊慌。这只是一个字符串字面量，尚未应用类型：

craig=> SELECT pg_typeof('blah');
 pg_typeof 
-----------
 unknown
(1 row)

比较数组和标量值

这是一个比较数组和标量值的例子：

SELECT * FROM "db_of_exercises" WHERE "body_part" IN ('Arms', 'Chest');

出现以下错误:

ERROR:  array value must start with "{" or dimension information

由于隐式转换，问题出现了。 body_part 列的类型是 text[]（或 varchar[]；在 PostgreSQL 中为同一类型）。您正在使用 IN 子句中未知类型的文字值来比较它们的相等性。数组的唯一有效相等运算符是与相同类型的另一个数组相等的 = 运算符，因此 PostgreSQL 认为 IN 子句中的值也必须是 text[] 数组，并尝试将其解析为数组。

由于它们不是像 {"FirstValue","SecondValue"} 那样写成的数组文字，所以这个解析失败了。请注意：

craig=> SELECT 'Arms'::text[];
ERROR:  array value must start with "{" or dimension information
LINE 1: SELECT 'Arms'::text[];
               ^

看到了吗？

一旦你明白了IN实际上只是= ANY的简写，就更容易理解了。它是对IN列表中每个元素进行相等性比较。如果你真的想要找出两个数组是否重叠，那么这并不是你想要的。

因此，你需要使用数组重叠运算符&&。

- Craig Ringer

---

谢谢，解释得非常好。但是text[]和varchar[]真的是同一件事吗？当我在使用&&运算符与body_part列进行比较时（在我的问题中有详细说明），我遇到了一个错误，不得不将ARRAY ['Arms'，'Chest']强制转换为varchar。 - kwyoung11

@kwyoung11 它们并不是完全相同的东西; varchar(n)带有长度约束。它们可以隐式地互相转换，在大多数情况下是等效的，但在一些情况下，Pg不允许将它们视为相同的类型。它们在内存和磁盘数据类型上是相同的，只是长度约束有时会强制它稍微以不同的方式处理它们。我避免使用varchar，而是在任何地方都使用text。如果我想要一个长度约束，我会添加一个显式的check约束。我认为这更清晰、更一致，但我不必编写可移植的模式。 - Craig Ringer

明白了，这很有道理。我原以为会有性能差异，但根据文档来看并非如此。 - kwyoung11