Обсуждение: char column with a single space as the default not working

All;


I created a table like this:

create table z (charcol char(1) default  ' ', intcol int);


Then I inserted a row with a single space in the charcol column and 
another insert letting the table use the default value:

insert into z values (' ', 1);

insert into z (intcol) values (2);


However neither of the inserted rows actually retained the single space 
in the charcol column:


select '[' || charcol || ']', intcol from z;
  ?column? | intcol
----------+--------
  []       |      1
  []       |      2
(2 rows)


Thoughts? Is it possible to add a single space to a char column without 
it getting truncated?


Thanks in advance

Peter Gram <peter.m.gram@gmail.com> writes:
> I think it behaves as expected. Look at my select "select
> encode(charcol::bytea, 'hex') , intcol from z;" it returns 20 which is a
> space in hex

Yeah, the space is stored.  What is probably surprising the OP
is that applying the || operator involves a coercion from "char"
to "text", which strips the defined-to-be-insignificant trailing
space(s) of the "char" value.

As David said, you're best off not using the char type.
The semantics around trailing spaces are too squishy for
my taste, and there's no real advantage compared to varchar
or text.

            regards, tom lane

Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Thu, Nov 30, 2023 at 2:56 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah, the space is stored.  What is probably surprising the OP
>> is that applying the || operator involves a coercion from "char"
>> to "text", which strips the defined-to-be-insignificant trailing
>> space(s) of the "char" value.

> How do you get a trailing space when trailing spaces are significant?

Use varchar or text.  If you want trailing spaces to be significant,
char is simply the wrong data type.

            regards, tom lane

On Thu, 2023-11-30 at 17:00 -0500, Ron Johnson wrote:
> > As David said, you're best off not using the char type.
> > The semantics around trailing spaces are too squishy for
> > my taste, and there's no real advantage compared to varchar
> > or text.
>
> How do you get a trailing space when trailing spaces are significant?

You would add a check constraint to verify it is there, and
if you want to automatically create training spaces, use a
BEFORE trigger.

Yours,
Laurenz Albe