Thread: append all columns in where-clause

for doing a simple full text-search I add the following where-clause:

select c1,c2,c3...,c9 from table where c1||c2||c3 .. ||c9 ~ 'searchtext';

This seems to work even with integers or timestamps. Unfortunately it does not work with char-fields:

 author    | varchar(100)  |
 valid     | char(1)       |

#select author||valid from table;
ERROR:  Unable to identify an operator '||' for types 'varchar' and 'bpchar'
        You will have to retype this query using an explicit cast

Is there any way to ship around this problem or any more correct way
to avoid such errors in the future with different types.

thnx,

peter



--
mag. peter pilsl

phone: +43 676 3574035
fax  : +43 676 3546512
email: pilsl@goldfisch.at
sms  : pilsl@max.mail.at

pgp-key available

On Wed, Apr 18, 2001 at 11:26:54PM +0200, Peter Pilsl wrote:
> for doing a simple full text-search I add the following where-clause:
>
> select c1,c2,c3...,c9 from table where c1||c2||c3 .. ||c9 ~ 'searchtext';
>
> This seems to work even with integers or timestamps. Unfortunately it does not work with char-fields:
>
>  author    | varchar(100)  |
>  valid     | char(1)       |
>
> #select author||valid from table;
> ERROR:  Unable to identify an operator '||' for types 'varchar' and 'bpchar'
>         You will have to retype this query using an explicit cast
>
> Is there any way to ship around this problem or any more correct way
> to avoid such errors in the future with different types.

this is a good question ...

    "how can we cast 'varchar' to 'bpchar' or vice-versa?"

i think i got it to work by appending an inline null string, such
as

    select varcharfld || '' || bpcharfld from sometable;

but it sure seems like there otta be built-in operators for
converting between those very-similar (conceptually) types!

(i use postgresql 7.0.3 on a debian/potato system.)

--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

will trillich <will@serensoft.com> writes:
>     "how can we cast 'varchar' to 'bpchar' or vice-versa?"

You just do it:

    select bpcharfld::varchar ...

or whatever.

            regards, tom lane