Skip site navigation (1) Skip section navigation (2)

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

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: biju(dot)george(at)ust-global(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6609: pattern matching (version 8.2 or so...)
Date: 2012-04-23 23:51:34
Message-ID: CAK3UJREkQqsuW4h_FPnONRL6q5SC08nHVZ0fHZcUEwvbHoH==g@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: atrigentDate: 2012-04-25 10:33:10
Subject: BUG #6612: Functions can be called inside CHECK statements
Previous:From: Kevin GrittnerDate: 2012-04-23 22:36:57
Subject: Re: BUG #6607: Strange select behavior

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group