Re: BUG #6609: pattern matching (version 8.2 or so...)

On Mon, Apr 23, 2012 at 8:16 AM, <biju(dot)george(at)ust-global(dot)com> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      6609
> Logged by:          biju george
> Email address:      biju(dot)george(at)ust-global(dot)com
> PostgreSQL version: Unsupported/Unknown
> Operating system:   Linux
> Description:
>
> I have a text column which have values like
> '01abcd','012345','abcde',etc...
> Now I am trying to take the first 2 characters of the column and pass into a
> function which takes only integer values and returns an integer. So, I need
> to check before I input into the function whether the substring is integer
> or not. If integer then the return value else default value say 99.
> select my_function(case when substr(my_column,1,2) like '[0-9][0-9]' then
> substr(my_column,1,2) else 99 end) from my_table;
> I tried like, =, ~. Nothing seems to work. Tried ::text, ::integer and
> all...
>
> It always throws error --
> ERROR:  CASE types integer and text cannot be matched
>
> The darn thing just don't work... :mad:

[This isn't really a bug report, and would be better discussed on the
-general or -sql list]

Well, the error message is fairly clear about what's wrong: you are
trying to mix text and integer types in the values returned from your
CASE statement. To "fix" this, you could make sure that the CASE
statement always returns an integer value, e.g.

SELECT
(CASE WHEN substr(my_column, 1, 2) ~ '[0-9][0-9]'
THEN substr(my_column, 1, 2)::int
ELSE 99
END)::int

FROM my_table;

By the way, LIKE in your example was incorrect: I think you wanted
either 'SIMILAR TO' or the '~' operator depending on your needs. You
might want to wrap the above SQL statement into a standalone function
for cleanliness, if a schema redesign is not feasible.

Josh