I have where clause where all fields are CHAR(10) type and may have trailing
spaces.

SELECT ...

FROM r1, r2

WHERE r1.c1 || r1.c2 || r1.c3 >= r2.c1 || r2.c2 || r2.c3;



This WHERE clause produces wrong result since || operator removes trailing
spaces.

Correct result is produced if || operator does not remove trailing spaces.

How to concat strings in this expression so that trailing spaces are
significiant or how to re-write this join condition so that trailing spaces
are used for comparison?

Andrus.

"Andrus" <eetasoft@online.ee> writes:
> I have where clause where all fields are CHAR(10) type and may have trailing
> spaces.
> ...
> Correct result is produced if || operator does not remove trailing spaces.

If you think that trailing spaces are significant data, you should
probably be using VARCHAR not CHAR datatype.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

> If you think that trailing spaces are significant data, you should
> probably be using VARCHAR not CHAR datatype.

I have existing database where there are only CHAR columns, no any VARCHAR
column.
I'm not sure will my appl work if I change all char columns to varchar
columns .
Is it reasonable to change all occurences of CHAR to VARCHAR in database ?


Are the following clauses exactly same for fields of type CHAR(10):

WHERE r1.c1::VARCHAR(10) || r1.c2::VARCHAR(10) || r1.c3::VARCHAR(10) >=
r2.c1::VARCHAR(10) || r2.c2::VARCHAR(10) ||
r2.c3::VARCHAR(10)

and

WHERE r1.c1>=r2.c1 and r1.c2>=r2.c2 and r1.c3>=r2.c3

and

WHERE (r1.c1, r1.c2,r1.c3) >= (r2.c1, r2.c2,r2.c3)

Andrus.