Thursday, May 20, 2010

Postgresql users are spoiled with conveniences

Saw this question on stackoverflow

Are there any updates in SQL 2008 to allow a variable for the IN of a where clause?

Declare @InParams varchar(100)
Set @InParams = '1,2'

Select * from Category
Where CategoryID in @InParams

Well it is easy to do that in Postgres:

Select * from Category
Where CategoryID = Any(string_to_array('1,2', ',')::int[])


  1. It's possible to replace """string_to_array('1,2', ',')::int[]"""

    with something that doesn't need to explode and then typecast the string? like: """[1, 2, 3]""" ?

  2. Hi Arthur, thanks for the observation. However, the proof of concept I'm showing is even the source input come from external, let's say you don't have any control and you cannot make the caller(other developer) modify their code and pass an array instead. The combo of string_to_array and typecasting will let you achieve what you want to do without too much fuss

  3. Another approach is:

    create table example as
    select '1,2,3'::text as s;

    select ('{' || s || '}')::int[] from example