PostGreSQL 7.2.4

Can anyone tell me where trailing spaces are significant in CHAR,
VARCHAR and TEXT fields?

We pass a VARCHAR variable into a 3rd party language product 'function'
as a CHAR(250). The CHAR(250) field is then used to interrogate a table
to see if that value exists. If not, a row with that value is inserted
into the table as a VARCHAR(N). The issue is that the value when
inserted into the VARCHAR in the table appears to carry trailing spaces,
so when a subsequent lookup is performed with the same value, the value
is not 'found' and a further insert occurs.
The problem appears to disappear if I change the definition of the
passed parameter to a VARCHAR as well.
Is this a known issue?


Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769

The char data type will automatically pad unused positions with space so
it stays fixed at 250.

Varchar will automatically trim any unused positions.

I assume in your code somewhere you are doing a CAST(char column as
varchar). You can enclose this in a TRIM to remove whitespace at
beginning and end. TRIM(CAST(char column as varchar))

HTH
On Fri, 2004-07-30 at 07:35, Steve Tucknott wrote:
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

At 12:12 AM 7/31/04, mike g wrote:

varchar doesn't 'trim' anything - it inserts text exactly as it is coded.


fbax=# create table vctest( c1 varchar );
CREATE
fbax=# insert into vctest values('abc ');
INSERT 11939819 1
fbax=# insert into vctest values('abcd');
INSERT 11939820 1
fbax=# select *,length(c1) from vctest where c1~' $';
c1 | length
--------+--------
abc | 6
(1 row)



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html