Feedback-ID: david@calascibe
Reply-To: <David@Calascibetta.com>
From: "David M. Calascibetta" <david@calascibetta.com>
To: "'Mark Dilger'" <mark.dilger@enterprisedb.com>
Cc: <pgsql-bugs@lists.postgresql.org>
References: <17258-a7c164c5fb955221@postgresql.org>
 <19D58355-E7C3-47D7-9C87-51EC751AE2BB@enterprisedb.com>
 <003701d7ccfb$c2a85bb0$47f91310$@calascibetta.com>
 <0EE9669C-6E65-40E1-B492-F989F452DAC4@enterprisedb.com>
 <005501d7cd0b$9ba5d5f0$d2f181d0$@calascibetta.com>
 <F04C22E4-C392-446E-840E-264F77397945@enterprisedb.com>
In-Reply-To: <F04C22E4-C392-446E-840E-264F77397945@enterprisedb.com>
Subject: RE: BUG #17258: Unexpected results in CHAR(1) data type
Date: Fri, 29 Oct 2021 16:52:46 -0500
Message-ID: <005901d7cd0f$500f1c10$f02d5430$@calascibetta.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: 
 AQLB1uYWs1aJVPkX1BZhIF63SkMLgwH/iU5GAnFecvcC9n3sDwIkuI8lAg+cjF+puZGT0A==
Content-Language: en-us
Archived-At: 
 <https://www.postgresql.org/message-id/005901d7cd0f%24500f1c10%24f02d5430%24%40calascibetta.com>
Precedence: bulk

> I agree, the behavior is nuts, and is inconsistent with every other =
RDBMS out there.

I haven't studied the behavior of char(n) on other RDBMS products.  I'd =
be curious if the SQL spec says anything that we're violating in this =
regard.  If so, we should at least have a warning in the docs about =
that.  (We already have a warning about how char(n) behaves, but nothing =
I see about the behavior being non-compliant.)  But I'm wondering if =
other RDBMS products really differ in this regard?  Are you perhaps =
thinking about how varchar(n) works?=20

In every other rdbms, if it's fixed length character, values will always =
be that fixed length.
PG behaves that way unless the value is blank. I believe this is in =
violation of the spec,
which is why I reported it. Even the PG doc says it will pad with =
blanks. It does not give
an exception for blank values.


> I was only reporting it to improve the product, but if you think this=20
> is appropriate behavior, I'm good with it.

I tend to think of char(n) as a misfeature and avoid using it.  Based on =
your experience with other RDBMSs, would you expect char(n) and =
varchar(n) to behave the same or to behave differently?  In postgres, =
they are different, and varchar(n) would behave more like you seem to =
want.

They should behave the same, in that if I insert a blank into a char(1) =
column, it should store a blank value.
If I insert a blank into a varchar(1) column, it should store a blank =
value.  Fixed length characters would pad
on the right, but it should not distort the value I entered in any other =
way. In this case, I enter a blank value and
PG removes it.  That is not appropriate behavior.