Re: Problem with a Pettern Matching Check

On Mon, Aug 15, 2005 at 08:21:23PM -0400, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > The CHAR(3) specification causes the value to be space-padded, so
> > '1' becomes '1 ' (the digit "one" followed by two spaces).
>
> Actually, we seem to be going out of our way to make this case fail.
> Given that we consider trailing spaces in char(n) to be semantically
> insignificant, would it make sense to strip them before doing the
> regex pattern match?

How standards-compliant would that be? Does the standard specify
what should happen when using SIMILAR TO with a char(n) value?
I notice that equality and SQL regular expressions treat trailing
spaces differently -- is that intentional or accidental?

CREATE TABLE foo (test char(3));
INSERT INTO foo VALUES ('1');

SELECT test, test = '1', test SIMILAR TO '1' FROM foo;
test | ?column? | ?column?
------+----------+----------
1 | t | f
(1 row)

SELECT test, test = '1 ', test SIMILAR TO '1 ' FROM foo;
test | ?column? | ?column?
------+----------+----------
1 | t | t
(1 row)

> That would happen automatically if we allowed
> the char(n) value to promote to text --- and the only reason it's
> not doing so is that there's an extra ~ operator definition that
> specifically prevents that (bpcharregexeq).

"bpcharregexeq" -- didn't Philip Glass write the score to that?

--
Michael Fuhr