postgresql 将逗号分隔的字符串转换为 Postgres 中的整数数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45304552/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting comma separated string to integer array in Postgres
提问by sam
I am trying to convert the Comma seperated string in to integer array (integer[]) to use in Where clause.
我正在尝试将逗号分隔的字符串转换为整数数组 (integer[]) 以在 Where 子句中使用。
I have tried cast, ::Intwhich didn't work. Appreciate your input
我试过演员表，::Int但没有用。感谢您的投入
Example
例子
Table A | Table B
ID | Set_id
2 | 14,16,17
1 | 15,19,20
3 | 21
My Query:
我的查询：
Select *
from Table a, table b
where a.id in b.set_id
回答by a_horse_with_no_name
You need to convert the string to a proper integer array if you want to use that for a join condition.
如果要将字符串用于连接条件，则需要将字符串转换为适当的整数数组。
Select *
from Table a
join table b on a.id = any(string_to_array(b.set_id, ',')::int[]);
But a muchbetter solution would be to properly normalize your tables (or at leaststores those IDs in an integer array, not a varchar column)
但很多更好的解决办法是正确正常化你的表（或至少保存在一个整数数组这些ID，而不是一个varchar列）
回答by Kaushik Nayak
Select * from Table_a a, table_b b
where a.id = any(regexp_split_to_array(b.set_id,',')::int[]);
