Re: CVS to In_list without dynamic SQL, how?

Wow, postgresql makes it so easy compared to Oracle. Thanks so much.

On Wed, Dec 9, 2015 at 1:57 PM, Corey Huinker <corey(dot)huinker(at)gmail(dot)com>
wrote:

> On Wed, Dec 9, 2015 at 4:30 PM, Michael Moore <michaeljmoore(at)gmail(dot)com>
> wrote:
>
>> Very nice!
>> In my case the value being compared is numeric. I tried:
>> SELECT to_char( 1 , '999') = ANY( string_to_array('1,2,3,4,5', ','));
>> but the result is FALSE
>>
>
> to_char returns a string. In this case, your spec has explicitly stated
> that the string must be 3 characters wide.
>
> # select 'X' || to_char(1,'999') || 'X';
> ?column?
> ----------
> X 1X
> (1 row)
>
> '1' is not 3 characters wide. ' 1' <> '1'.
>
> Skipping the forced formatting, you get the answer you want:
>
> SELECT 1::text = ANY( string_to_array('1,2,3,4,5', ','));
> ?column?
> ----------
> t
> (1 row)
>
> Similarly you do the comparison with integers, you'll get happy results:
>
> # SELECT 1 = ANY( string_to_array('1,2,3,4,5', ',')::integer[]);
> ?column?
> ----------
> t
> (1 row)
>
> # SELECT 6 = ANY( string_to_array('1,2,3,4,5', ',')::integer[]);
> ?column?
> ----------
> f
> (1 row)
>
>
>