PostgreSQL：比较数组会导致“格式错误的数组文字”。错误 [英] Postgresql: comparing arrays results in "malformed array literal" error

问题描述

我有一个Rails应用和一个包含 [ manager， engineer] 等数组和一个where语句的列：

I have a Rails app and a column that contains an array like ["manager", "engineer"] etc. and a where statement like this:

where("? = ANY roles", query)

可以找到是否为查询传递单个值。我希望能够传递多个值。我做了一些Google搜索，发现了一个简单的解决方案：

which works find if I pass a single value for query. I want to be able to pass multiple values. I did some Googling and an simple solution was found:

where("? && roles", query )

除非我传递类似 ['admin'，'guest' ] 出现此错误：

except that if I pass something in like "['admin', 'guest']" I get this error:

PG::InvalidTextRepresentation: ERROR:  malformed array literal: "['admin', 'guest']"
LINE 1: ....                                       $1 AND ('[''admin'...
                                                             ^
DETAIL:  "[" must introduce explicitly-specified array dimensions.

我怀疑存在一些奇怪的引号转义问题，但我不知道这些错误消息会导致一堆JSON Q& As，但没有解决方案会跳出来。

I suspect that there is some weird quote escaping issue but I can't figure it out. Those error messages results in a bunch of JSON Q&As but nothing that jumps out with a solution.

UPDATE

发布问题后，我似乎总是能找到线索-我尝试过：

I always seem to find a clue after posting a question - I tried:

where("'{guest, admin}'::text[] && roles", query )

它可以正常工作-我仍然不知道为什么，但是确实可以。现在我看不到如何将？重新带回那里，以便我可以对其进行搜索。

and it works - I still don't really know why but it does. Now I can't see how to get the ? back in there now so I can search on it.

更新2

我在下面做了第一个答案，做了一些重构，以获得我认为是简单的优雅解决方案：

I took the first answer below and did a bit of refactoring to get what I think is a simple elegant solution:

where("'{#{roles}}'::text[] && roles")

通过这种方式，我可以将一个简单的文本字符串传递给我的应用程序帮助程序（该where子句位于其中）。它处理单个和多个查询。

This way I can pass a simple text string to my application helper that this where clause sits. It handles single and multiple queries.

推荐答案

使用 array构造函数几乎总是更好。数组的语法。从精细手册：

You're almost always better off using the array constructor syntax for arrays. From the fine manual:

4.2.12。数组构造器

数组构造器是一个表达式，该表达式使用其成员元素的值构建数组值。一个简单的数组构造函数由关键字 ARRAY ，左方括号 [，表达式列表（以逗号）作为数组元素的值，最后是右方括号] 。例如：

An array constructor is an expression that builds an array value using values for its member elements. A simple array constructor consists of the key word ARRAY, a left square bracket [, a list of expressions (separated by commas) for the array element values, and finally a right square bracket ]. For example:

SELECT ARRAY[1,2,3+4];
  array
---------
 {1,2,7}
(1 row)

ActiveRecord会将占位符的数组值扩展为逗号分隔的列表，这正是 array的含义[...] 语法要放在方括号之间。所以你会说：

ActiveRecord will expand an array value for a placeholder into a comma delimited list and that's exactly what the array[...] syntax wants between the brackets. So you'd say:

where('array[?] && roles', query)

如果 query 是单个值，这甚至可以做正确的事情。

This even does the right thing if query is a single value.

就您的 UPDATE 而言，这是

'{guest, admin}'::text[]

是字符串文字（'{guest，admin}'），其后是类型强制转换（ :: ）到文本数组（ text [] ）。字符串中的’{...}’语法是数组的另一种形式，它易于阅读，但难以正确构建。 高级手册也涵盖了以下形式：

is a string literal ('{guest, admin}') followed by a type cast (::) to an array-of-text (text[]). The '{...}' syntax inside the string is another form of an array that is easy to read but a hassle to properly build; the fine manual also covers this form:

8.15.2。数组值输入

要将数组值写为文字常量，请将元素值括在花括号中并用逗号分隔。

To write an array value as a literal constant, enclose the element values within curly braces and separate them by commas.

我专门使用 array [...] 版本，因为它更易于使用和关于数组元素的类型更明确。

I use the array[...] version exclusively because it is easier to work with and more explicit as to what type the array elements are.

这篇关于PostgreSQL：比较数组会导致“格式错误的数组文字”。错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！