[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [HACKERS] [BUGS] 7.4: CHAR padding inconsistency
From:       Bruce Momjian <pgman () candle ! pha ! pa ! us>
Date:       2003-11-30 4:44:45
[Download RAW message or body]


Added to TODO:

	Make LENGTH() of CHAR() not count trailing spaces

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Well, that certainly is interesting.  Oracle and MS-SQL preserve the
> > trailing space when concatenating.  Does anyone remember the logic for
> > trimming space with ||?
> 
> "trimming space with ||" is a completely inaccurate description of
> what's happening.
> 
> 7.4 trims spaces from char(n) data when converting it to text (or
> varchar).  Since we don't have a bpchar version of ||, only a text
> version, the implicitly invoked conversion is what's making the
> difference.
> 
> AFAICS the Oracle and SQL Server behaviors are at least as inconsistent
> as our own.  If trailing spaces are significant during concatenation,
> why aren't they significant to LENGTH()?  I can't see a justification
> in the SQL spec for handling one case differently from the other.
> 
> Actually the SQL spec is pretty inconsistent itself.  It's clear that
> trailing spaces are insignificant in comparisons, if you are using a
> PAD SPACE collation which I think is the implication of CHAR(n), but
> I don't see anything that says that they are insignificant for other
> purposes such as LENGTH() and concatenation.
> 
> I'd agree with changing bpcharlen() to not count trailing spaces,
> I think.  That would be consistent with ignoring them in other contexts.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(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
[prev in list] [next in list] [prev in thread] [next in thread]