regular expressions troubles with char cols

dbahena@tpv.com.mx writes:

ventasge2000=# create table t1 (c1 char(10),c2 varchar(10));
CREATE
ventasge2000=# insert into t1 values('XXX666','XXX666');
INSERT 182218 1
ventasge2000=# select * from t1 where c1 ~ '666$';
c1 | c2
----+----
(0 rows)
ventasge2000=# select * from t1 where c2 ~ '666$';
c1 | c2
------------+--------
XXX666 | XXX666
(1 row)

Doesn't regular expressions(in particular the $ metachar) work properly
with char columns????

I see no bug there --- you've forgotten about the trailing spaces in
the char(10) column. Try c1 ~ '666 *$' if you want to match against a
variable amount of padding in a char(N) column. But really I'd suggest
using the appropriate datatype to begin with, and evidently char(N) is
not it for your use of this table.

regards, tom lane

dbahena@tpv.com.mx writes:

ventasge2000=# create table t1 (c1 char(10),c2 varchar(10));
CREATE
ventasge2000=# insert into t1 values('XXX666','XXX666');
INSERT 182218 1
ventasge2000=# select * from t1 where c1 ~ '666$';
c1 | c2
----+----
(0 rows)
ventasge2000=# select * from t1 where c2 ~ '666$';
c1 | c2
------------+--------
XXX666 | XXX666
(1 row)

Doesn't regular expressions(in particular the $ metachar)

work properly

with char columns????

I see no bug there --- you've forgotten about the trailing spaces in
the char(10) column. Try c1 ~ '666 *$' if you want to match against a
variable amount of padding in a char(N) column.

No, imho char(n) is defined to return trailing blanks but be insensitive to
the actual amount of trailing spaces. Thus I do see that this behavior can
be interpreted as a bug here.
In char(n) speak 'ab' = 'ab ' is supposed to be true.
Imho a change from char(6) to char(8) should only require more storage
space in a client program, but be otherwise transparent,
which it currently is not.

Imho a similar problem is that char_length does not return the count to
the last non space character, which imho also is a bug.

Andreas