Re: append all columns in where-clause

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(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!