Answering to a realy old message... The problem is that cayenne uses
RTRIM() on char-columns in a query.
Our database uses char(1) one some status columns. Should we switch to
varchar(1)? We also have som columns with char(8) that is a id that
always is 8 chars long. No trailing spaces. It seems a bit wasted to
use varchar on these fields. What do you guys think?
Regards,
- Tore.
On Sep 5, 2003, at 15:17, Andrus Adamchik wrote:
>
> Hi Tore,
>
> the thing is - without RTRIM "equal" comparison will fail in most
> cases since all CHAR columns are padded with spaces to their max
> width. And yes, with RTRIM index will most likely be ignored. Define
> the column as VARCHAR, and RTRIM won't be used.
>
> Andrus
>
> On Friday, September 5, 2003, at 08:57 AM, Tore Halset wrote:
>
>> Hello!
>>
>> I have a table cell with a primary key named cell_id. The database is
>> PostgreSQL. cell_id is char(8).
>>
>> The datamap:
>> <db-entity name="cell" schema="public">
>> <db-attribute name="cell_id"
>> type="CHAR"
>> isPrimaryKey="true"
>> isMandatory="true"
>> length="8"/>
>>
>> I am using the follwing code to query for one specific cell
>> ExpressionFactory.binaryPathExp(Expression.EQUAL_TO, "cellId",
>> cellId);
>> SelectQuery query = new SelectQuery(Cell.class, qual);
>> List cells = getDataContext().performQuery(query);
>>
>> The generated SQL performs a rtrim
>> RTRIM(cell_id) = ?
>>
>> I guess the rtrim-code are not needed? Will PostgreSQL still be able
>> to
>> use the index?
>>
>> => explain select * from cell where cell_id='ABCDEFGH';
>> QUERY PLAN
>> ----------------------------------------------------------------------
>> -
>> Index Scan using cell_pkey on cell (cost=0.00..5.89 rows=1 width=86)
>> Index Cond: (cell_id = 'ABCDEFGH'::bpchar)
>> (2 rows)
>>
>> => explain select * from cell where rtrim(cell_id)='ABCDEFGH';
>> QUERY PLAN
>> --------------------------------------------------------
>> Seq Scan on cell (cost=0.00..110.02 rows=18 width=86)
>> Filter: (rtrim((cell_id)::text) = 'ABCDEFGH'::text)
>> (2 rows)
>>
>> Regards,
>> - Tore.
>>
This archive was generated by hypermail 2.0.0 : Fri Sep 17 2004 - 08:27:31 EDT