PG的Rails4 FIND_IN_SET不起作用 [英] Rails4 FIND_IN_SET with PG not working

问题描述

查询

select * 
from inboxes 
where find_in_set ('6',inboxes.to);

我正在使用上述查询从我的表收件箱中访问具有列 to之一的记录'（2,4,6,7）之类的值，以逗号分隔。我在pg中使用find_in_set函数，但它向我显示以下提示错误。

Above query i am using for accessing record from my table 'inboxes' having one of the column 'to' values like (2,4,6,7) string separated by commas. I am using find_in_set function in pg but it shows me error with below hint.

提示：没有函数与给定的名称和参数类型匹配。您可能需要添加显式类型转换。

Hint: No function matches the given name and argument types. You might need to add explicit type casts.

推荐答案

您可以模拟MySQL的 find_in_set（）函数在Postgres中使用数组。

You can simulate MySQL's find_in_set() function using an array in Postgres.

select * 
from inboxes 
where '6' = any (string_to_array("to",','));

请注意，至是保留关键字，因此需要用 to 引用。通常，您不应该将关键字保留为列名（或任何其他标识符）

Note that to is a reserved keyword and thus needs to be quoted "to". In general you should not reserved keywords as column names (or any other identifier)

但是您应该真的考虑修复您的数据模型。您的模型违反了第一个范式，从长远来看会给您带来更多麻烦。最大的问题之一是，您无法对存储在该字符串值中的值定义任何外键约束，也可以对数据实施任何其他约束。

But you should really think about fixing your data model. Your model is violating the first normal form and will give you many more headaches in the long run. One of the biggest problems is that you can't define any foreign key constraints on the values stored in that string value, or can you enforce any other constraints on the data.

这篇关于PG的Rails4 FIND_IN_SET不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！