I have have a column name MyRoles that contains a list of items (integers) stored in a field named UserRoles. I want to write a query that checks to see if a specific item is in the list. The list will look like this: "1,2,3"
I can't use WHERE MyRoles
What should the query look like?
This is similar to what I am thinking:
SELECT *
FROM MyTable
WHERE MyRoles CONTAINS ('1')
The fact that no answer was easy to implement and would take me farther down an ugly road really makes clear that a normalized database is always the best bet.
Convert it to an array:
SELECT *
FROM MyTable
WHERE ('{' || MyRoles || '}')::int[] && array[1]
Better yet, you can use an index on the above mess. Casting text to an array type outright will get rejected when building an array, but you can work around it:
create function role_scan(text) returns int[] as $$
select ('{' || $1 || '}')::int[];
$$ language sql immutable strict;
create index on MyTable using gin (role_scan(MyRoles));
-- actually use the index
SELECT *
FROM MyTable
WHERE role_scan(MyRoles) && array[1]
There is a caveat in adding the index, that you should be aware of. The stats collector doesn't look (up to 9.1 anyway) into the actual array values. The selectivity of the overlap operator (1/200, i.e. very selective) is hard-coded for all intents and purposes. So if you're querying against very common values, you might get an index scan where inappropriate. One workaround is to call the underlying overlap method directly (which gives a selectivity of 1/3 and no potential index scan) when you know plenty of roles apply:
SELECT *
FROM MyTable
WHERE arrayoverlap(role_scan(MyRoles), array[1])