pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedchar column with a single space as the default not working
8+ messages / 7 participants
[nested] [flat]
* char column with a single space as the default not working
@ 2023-11-30 15:34 Sbob <sbob@quadratum-braccas.com>
2023-11-30 15:51 ` Re: char column with a single space as the default not working David G. Johnston <david.g.johnston@gmail.com>
2023-11-30 17:44 ` Re: char column with a single space as the default not working Peter Gram <peter.m.gram@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Sbob @ 2023-11-30 15:34 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: char column with a single space as the default not working
2023-11-30 15:34 char column with a single space as the default not working Sbob <sbob@quadratum-braccas.com>
@ 2023-11-30 15:51 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2023-11-30 15:51 UTC (permalink / raw)
To: Sbob <sbob@quadratum-braccas.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, Nov 30, 2023 at 8:34 AM Sbob <sbob@quadratum-braccas.com> wrote:
> Thoughts? Is it possible to add a single space to a char column without
> it getting truncated?
>
>
Just don't use char as a data type and things will behave as you expect.
https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_char.28n.29
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: char column with a single space as the default not working
2023-11-30 15:34 char column with a single space as the default not working Sbob <sbob@quadratum-braccas.com>
@ 2023-11-30 17:44 ` Peter Gram <peter.m.gram@gmail.com>
2023-11-30 19:55 ` Re: char column with a single space as the default not working Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 8+ messages in thread
From: Peter Gram @ 2023-11-30 17:44 UTC (permalink / raw)
To: Sbob <sbob@quadratum-braccas.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi
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
~/postgres/17/dev/data/ [master*] psql
psql (17devel)
Type "help" for help.
postgres PSQL> create table z (charcol char(1) default ' ', intcol int);
CREATE TABLE
postgres PSQL> insert into z values (' ', 1);
INSERT 0 1
postgres PSQL> insert into z (intcol) values (2);
INSERT 0 1
postgres PSQL> select encode(charcol::bytea, 'hex') , intcol from z;
encode | intcol
--------+--------
20 | 1
20 | 2
(2 rows)
postgres PSQL> select '[' || charcol || ']', intcol from z;
?column? | intcol
----------+--------
[] | 1
[] | 2
(2 rows)
Med venlig hilsen
Peter Gram
Sæbyholmsvej 18
2500 Valby
Mobile: (+45) 5374 7107
Email: peter.m.gram@gmail.com
On Thu, 30 Nov 2023 at 16:34, Sbob <sbob@quadratum-braccas.com> wrote:
> 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
>
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: char column with a single space as the default not working
2023-11-30 15:34 char column with a single space as the default not working Sbob <sbob@quadratum-braccas.com>
2023-11-30 17:44 ` Re: char column with a single space as the default not working Peter Gram <peter.m.gram@gmail.com>
@ 2023-11-30 19:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-30 22:00 ` Re: char column with a single space as the default not working Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2023-11-30 19:55 UTC (permalink / raw)
To: Peter Gram <peter.m.gram@gmail.com>; +Cc: Sbob <sbob@quadratum-braccas.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: char column with a single space as the default not working
2023-11-30 15:34 char column with a single space as the default not working Sbob <sbob@quadratum-braccas.com>
2023-11-30 17:44 ` Re: char column with a single space as the default not working Peter Gram <peter.m.gram@gmail.com>
2023-11-30 19:55 ` Re: char column with a single space as the default not working Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-11-30 22:00 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-11-30 22:34 ` Re: char column with a single space as the default not working Tom Lane <tgl@sss.pgh.pa.us>
2023-12-01 07:15 ` Re: char column with a single space as the default not working Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 8+ messages in thread
From: Ron Johnson @ 2023-11-30 22:00 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, Nov 30, 2023 at 2:56 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.
>
How do you get a trailing space when trailing spaces are significant?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: char column with a single space as the default not working
2023-11-30 15:34 char column with a single space as the default not working Sbob <sbob@quadratum-braccas.com>
2023-11-30 17:44 ` Re: char column with a single space as the default not working Peter Gram <peter.m.gram@gmail.com>
2023-11-30 19:55 ` Re: char column with a single space as the default not working Tom Lane <tgl@sss.pgh.pa.us>
2023-11-30 22:00 ` Re: char column with a single space as the default not working Ron Johnson <ronljohnsonjr@gmail.com>
@ 2023-11-30 22:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-30 22:43 ` Re: char column with a single space as the default not working M Sarwar <sarwarmd02@outlook.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2023-11-30 22:34 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: char column with a single space as the default not working
2023-11-30 15:34 char column with a single space as the default not working Sbob <sbob@quadratum-braccas.com>
2023-11-30 17:44 ` Re: char column with a single space as the default not working Peter Gram <peter.m.gram@gmail.com>
2023-11-30 19:55 ` Re: char column with a single space as the default not working Tom Lane <tgl@sss.pgh.pa.us>
2023-11-30 22:00 ` Re: char column with a single space as the default not working Ron Johnson <ronljohnsonjr@gmail.com>
2023-11-30 22:34 ` Re: char column with a single space as the default not working Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-11-30 22:43 ` M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 8+ messages in thread
From: M Sarwar @ 2023-11-30 22:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
I had the similar issue few months ago when I was using CHAR. The issue is resolved when I started using VARYING CHARACTERS data types.
Thanks,
Sarwar
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, November 30, 2023 5:34 PM
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: char column with a single space as the default not working
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: char column with a single space as the default not working
2023-11-30 15:34 char column with a single space as the default not working Sbob <sbob@quadratum-braccas.com>
2023-11-30 17:44 ` Re: char column with a single space as the default not working Peter Gram <peter.m.gram@gmail.com>
2023-11-30 19:55 ` Re: char column with a single space as the default not working Tom Lane <tgl@sss.pgh.pa.us>
2023-11-30 22:00 ` Re: char column with a single space as the default not working Ron Johnson <ronljohnsonjr@gmail.com>
@ 2023-12-01 07:15 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 8+ messages in thread
From: Laurenz Albe @ 2023-12-01 07:15 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-12-01 07:15 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-30 15:34 char column with a single space as the default not working Sbob <sbob@quadratum-braccas.com>
2023-11-30 15:51 ` David G. Johnston <david.g.johnston@gmail.com>
2023-11-30 17:44 ` Peter Gram <peter.m.gram@gmail.com>
2023-11-30 19:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-30 22:00 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-11-30 22:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-30 22:43 ` M Sarwar <sarwarmd02@outlook.com>
2023-12-01 07:15 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox