Postgres Pro
Downloads
Home   >   mailing lists

Re: CVS to In_list without dynamic SQL, how? - Mailing list pgsql-sql

From Corey Huinker
Subject Re: CVS to In_list without dynamic SQL, how?
Date
Msg-id CADkLM=fh8XseWwC9nWwEW4KxzLDGdpjGKVYgiuGVw-6LbwH0Ww@mail.gmail.com
Whole thread Raw
In response to Re: CVS to In_list without dynamic SQL, how?  (Michael Moore <michaeljmoore@gmail.com>)
Responses Re: CVS to In_list without dynamic SQL, how?  (Michael Moore <michaeljmoore@gmail.com>)
List pgsql-sql
Tree view
On Wed, Dec 9, 2015 at 4:30 PM, Michael Moore <michaeljmoore@gmail.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)


pgsql-sql by date:

Previous
From: Michael Moore
Date:
Subject: Re: CVS to In_list without dynamic SQL, how?
Next
From: Christopher Molnar
Date:
Subject: Regexp_replace question/assistance needed