Обсуждение: BPCHAR description in 8.3. Character Types is misleading and incomplete

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/18/datatype-character.html
Description:

The description of BPCHAR in section 8.3. Character Types is misleading and
incomplete.
The first problem is that, contrary to table 8.4, BPCHAR is not actually
blank-trimmed. The wording "as-if-blank-trimmed" or "blank-ignoring" may be
better suited here. The following query explains the problem:

SELECT bpchar_val, length(bpchar_val) as bplen, concat('[', bpchar_val, ']')
as bpbrack
        , varchar_val, length(varchar_val) as vclen, concat('[',
varchar_val, ']') as vcbrack
FROM (VALUES
        ('abc   '::bpchar, 'abc   '::varchar),
        ('abc   '::bpchar, 'abc'::varchar),
        ('abc'::bpchar, 'abc   '::varchar),
        ('abc'::bpchar, 'abc'::varchar))
AS bpchar_test (bpchar_val, varchar_val)
WHERE bpchar_val = varchar_val;

As can be seen, it returns four rows (so, it indeed treats values that
differ only in number of trailing spaces as equal, regardless of the type on
the other side, and the length of bpchar_val is always 3 in all 4 rows -
trailing spaces are ignored in comparison. But the bpbrack column shows that
actual value is NOT trimmed: two of four rows have '[abc   ]' value (with 3
spaces before the closing bracket).
The example above also illustrate the need of more detailed explanation of
how BPCHAR actually behaves in different situation, particularly, in which
contexts trailing blanks are ignored (comparison, length, what else?) and in
which they are still significant (displaying, client interfaces,
concatenation, what else?).

The second problem is that 'Tip' before the example 8.1 mentions only three
types, also in a misleading way: 'There is no performance difference among
these three types' - as if there were only 3, not 4 distinct types.

On Tue, 2025-10-14 at 12:14 +0000, PG Doc comments form wrote:
> The description of BPCHAR in section 8.3. Character Types is
> misleading and
> incomplete.

Hi,

There was a previous discussion here:

https://www.postgresql.org/message-id/E1odZyZ-0000g2-AE%40gemulon.postgresql.org

> The first problem is that, contrary to table 8.4, BPCHAR is not
> actually
> blank-trimmed. The wording "as-if-blank-trimmed" or "blank-ignoring"
> may be
> better suited here. The following query explains the problem:

Correct, it does not actually trim the blanks before storing. The
paragraph below the table clarifies: "If bpchar lacks a length
specifier, it also accepts strings of any length, but trailing spaces
are semantically insignificant."

I think "blank-insignificant" is slightly better than "blank-ignoring".

> The second problem is that 'Tip' before the example 8.1 mentions only
> three
> types, also in a misleading way: 'There is no performance difference
> among
> these three types' - as if there were only 3, not 4 distinct types.

Thank you.

Please take a look at the attached patch. If you'd like your name
included in the commit, please send it as you'd like it to appear.

Regards,
    Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:
> Please take a look at the attached patch. If you'd like your name
> included in the commit, please send it as you'd like it to appear.

I don't understand why any of these variants are better than the
original wording "blank-padded".  That has the non-negligible
advantage of corresponding to the type name, and furthermore
appears in many other places in our docs and source code.

There may be some other wording improvements we could make here,
but I think b69db5173 was fundamentally misguided in this respect.

            regards, tom lane

On Wed, 2025-10-15 at 15:29 -0400, Tom Lane wrote:
> Jeff Davis <pgsql@j-davis.com> writes:
> > Please take a look at the attached patch. If you'd like your name
> > included in the commit, please send it as you'd like it to appear.
>
> I don't understand why any of these variants are better than the
> original wording "blank-padded".  That has the non-negligible
> advantage of corresponding to the type name, and furthermore
> appears in many other places in our docs and source code.

I don't have a strong opinion on the subject, but I'll explain the
reasoning:

"Padded" is confusing in the sense that it raises the question: "padded
to what length?". Since bpchar doesn't have a specific length
associated with it, then it's just taking the spaces that went into the
type input function, which doesn't sound like "padding" in the same
sense as CHAR(10).

Regards,
    Jeff Davis

> I don't understand why any of these variants are better than the
> original wording "blank-padded".  That has the non-negligible
> advantage of corresponding to the type name, and furthermore
> appears in many other places in our docs and source code.

The wording for BPCHAR (not to be confused with BPCHAR(N) is already
"blank-trimming", not "blank-padded". And "blank-padded" is probably
the least correct wording variant for BPCHAR, because this type has
unlimited length and it's impossible to pad to the infinity.

The following example (slight modification of my original example, I
replaced varchar with bpchar(6)) can perhaps explain the difference.

SELECT  length(bpchar_val) as bplen, concat('[', bpchar_val, ']') as bpbrack
    , length(char6_val) as c6len, concat('[', char6_val, ']') as c6brack
FROM (VALUES
    ('abc   '::bpchar, 'abc   '::bpchar(6)),
    ('abc   '::bpchar, 'abc'::bpchar(6)),
    ('abc'::bpchar, 'abc   '::bpchar(6)),
    ('abc'::bpchar, 'abc'::bpchar(6)))
AS bpchar_test (bpchar_val, char6_val)
WHERE bpchar_val = char6_val;

There results are:
bplen  bpbrack   c6len  c6brack
3      [abc   ]  3      [abc   ]
3      [abc   ]  3      [abc   ]
3      [abc]     3      [abc   ]
3      [abc]     3      [abc   ]

As you can see, there are four rows, so, comparison ignores blanks,
length() also ignores blank, but the results of concatenation show
that while BPCHAR(6) was actually padded to 6 characters ('abc' became
'abc   '), BPCHARwas not. 'abc' remained 'abc'. Therefore, I don't
think it's a good idea to call BPCHAR "blank-padded".

With best regards,
Sergei Katkovskii

> I don't understand why any of these variants are better than the
> original wording "blank-padded".  That has the non-negligible
> advantage of corresponding to the type name, and furthermore
> appears in many other places in our docs and source code.

The wording for BPCHAR (not to be confused with BPCHAR(N) is already
"blank-trimming", not "blank-padded". And "blank-padded" is probably
the least correct wording variant for BPCHAR, because this type has
unlimited length and it's impossible to pad to the infinity.

On Thu, Oct 16, 2025 at 4:36 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> A given value has a finite length and there is just no restriction on what that length is.  All trailing spaces in
theinput are considered padding for purposes of comparison i.e., manually padding is added by the user as opposed to
thesystem. 

A given value of BPCHAR is stored as is, without padding or trimming
(contrary to the current wording in Table 8.4 about black-trimming).
But what is the point of saying that it is manually padded, if the
user is free to store it without any padding? And, although
technically you can say that BPCHAR is blank-padded for purposes of
comparison (but saying that blanks are trimmed or ignored for that
purpose is also technically correct), it is definitely not padded in
other contexts, neither for concatenation, where not padding or
trimming occurs at all, nor for length evaluation, where blanks are
trimmed or ignored.

> So bpchar(n) is automatically blank padded to a total length for a value of n characters.  bpchar also has padding
blanksbut they must be manually inserted during value creation. 

BPCHAR(n) is definitely blank-padded to n, no doubt. BPCHAR may have
trailing blanks, if and only if they are added manually. But the
ability to store trailing blanks is not the same as blank-padding.
Manual addition is not padding, If it were, then VARCHAR would also be
"blank-padded", because you can manually add trailing blanks to values
of this type too. But of course it isn't.

> I would leave the note of blank-padded for both and just point out the automatic vs manual distinction.

The current wording in Table 8.4 is that BPCHAR is blank-trimmed, not
blank-padded anyway.

With best regards,
Sergei Katkovskii

On Thu, 2025-10-16 at 08:36 -0400, David G. Johnston wrote:
> A given value has a finite length and there is just no restriction on
> what that length is.  All trailing spaces in the input are considered
> padding for purposes of comparison i.e., manually padding is added by
> the user as opposed to the system.

I see -- so it means that the padding came from somewhere else (not the
type).

> I would leave the note of blank-padded for both and just point out
> the automatic vs manual distinction.

Yeah, it's a table, so if a user is confused, they can read the text
below it. I'm fine with that.

If you think the text below should be improved, do you have a wording
suggestion?

Regards,
    Jeff Davis

Manual addition is not padding, If it were, then VARCHAR would also be
"blank-padded", because you can manually add trailing blanks to values
of this type too. But of course it isn't.

On Thu, 2025-10-16 at 08:36 -0400, David G. Johnston wrote:
> A given value has a finite length and there is just no restriction on
> what that length is.  All trailing spaces in the input are considered
> padding for purposes of comparison i.e., manually padding is added by
> the user as opposed to the system.

I see -- so it means that the padding came from somewhere else (not the
type).

On Thu, Oct 16, 2025 at 10:11 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

> The spaces added to the end of a bpchar manually can and are considered “padding” - or “present but lack
semantic/valuesignificance”. The reason they are not padding for varchar is that such spaces are considered part of the
storedvalue from a semantic perspective. 
>
> Think of padding as a noun, not a verb.  “The value contains padding”.  Not, “ I am padding the value”.

The value may sometimes contain padding. And sometimes not. But we are
talking about the type, not value. The value 'abc'::bpchar does not
contain any blanks. But it's still a BPCHAR value.  And it is not
padded, despite that its type is BPCHAR.

> And yes, this is intended as a way to make the name of the type and its behavior consistent.

The behavior of 'abc'::BPCHAR is, for example, the following:
length('abc'::BPCHAR) = 3
CONCAT('abc::BPCHAR, 'def') = 'abcdef'.
What of the above is consistent with the notion of being 'blank-padded'?

And, the proposal is not to change the name of the type. The proposal
is to change the wording 'blank-trimming' to something more
appropriate.

> You sorta have to want it to work/make sense; not fight it on minor nuance.

The sole reason that I am here is that recently I came across a
(year-old) question on stackoverflow where some person was curious why
the actual behavior of BPCHAR was inconsistent with the documentation.
What is worse is that the question was answered, but answered
incorrectly. So, I doubt that it is just a minor nuance. Probably, not
just one person tried to use BPCHAR as a 'blank-trimming' type.

With best regards,
Sergei Katkovskii

On Thu, Oct 16, 2025 at 10:11 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

> The spaces added to the end of a bpchar manually can and are considered “padding” - or “present but lack semantic/value significance”. The reason they are not padding for varchar is that such spaces are considered part of the stored value from a semantic perspective.
>
> Think of padding as a noun, not a verb.  “The value contains padding”.  Not, “ I am padding the value”.

The value may sometimes contain padding. And sometimes not. 

But we are
talking about the type, not value.

And, the proposal is not to change the name of the type. The proposal
is to change the wording 'blank-trimming' to something more
appropriate.

On Thu, Oct 16, 2025 at 11:18 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

>> > Think of padding as a noun, not a verb.  “The value contains padding”.  Not, “ I am padding the value”.
>
> In PostgreSQL the behavior and stored contents/representation of a value are not influenced by a type modifier.  It
isonly used during input to perform some either or both a validation or transformation.  Here, when present, it
performsa padding transform.  But present or absent, trailing spaces, if any, are considered padding.  The prose for
thesection talk about the two key pieces: (n) imposes a length limit while also transforming an input by adding padding
spaces. The non-n case has unlimited length and no transformation.  Post-construction, all trailing spaces are treated
aspadding. 

Am I right that your reasoning, when expanded, turns into "BPCHAR is
blank-padded, where '-padded' means (Definition 1) that its values may
have trailing blanks, which, if present, are considered 'padding'
(Definition 2), which, in its turn, means a sequence of blanks
insignificant in some contexts (Definition 3)"? Yes, technically it is
correct, provided that others accept all those definitions. But don't
you think that in the documentation we should, whenever possible, use
words in their most commonly understood meanings? I'm not a language
expert, but I have never seen 'padded' in the sense different from
'something added to gain some needed size/shape/etc'. This is
anecdotal evidence, of course, so I can be wrong, but could you come
up with a counterexample?  Even in the PostgreSQL docs for BPCHAR(n)
'padded' means 'with spaces added up to n' ('values of type character
will be space-padded', 'Values of type character are physically padded
with spaces' - as a verb, not a noun, btw). I'm afraid that adding
another meaning to the word 'padded' in the same text will cause even
more confusion.


With best regards,
Sergei Katkovskii

On Thu, Oct 16, 2025 at 11:18 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

>> > Think of padding as a noun, not a verb.  “The value contains padding”.  Not, “ I am padding the value”.
>

I'm afraid that adding
another meaning to the word 'padded' in the same text will cause even
more confusion.

On Fri, Oct 17, 2025 at 1:08 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
>
> I’m just trying to phrase the documentation for bpchar so that the “bp”, which stands for “blank-padded”, is
justified.

That's what I thought. Yes, unfortunately, bp stands for
“blank-padded”, but this name is wrong and misleading. I don't know
why it was chosen, maybe it was actually considered as blank-padded at
that time, maybe it was an attempt to avoid introducing a new keyword
(an ancient curse that has ruined a lot of good ideas already). It
doesn't matter, though. That was a mistake in my opinion, but we don't
have to repeat it. There is no need to justify wrongs. An honest
remark (footnote, tip, whatever) in documentation "Although BP in
BPCHAR stands for 'blank-padded', no padding is actually performed for
this type" would be far better.

>
> I do understand the terminology confusion with the verb padding.  And see why “trimmed” is actively confusing.  The
proseprobably needs to resolve this - and technically does from what I’m reading. 
>
> You may wish to move on from critiquing my suggested changes and instead propose something concrete of your own.
Providea third choice besides status-quo and my option. 

I already did this. It was my original request that started this
discussion, and I suggested there, although more as a guide,
"as-if-blank-trimmed" and "blank-ignoring". But then Jeff Davis
prepared a patch and proposed "blank-insignificant" instead, which I
support now (after that, I also suggested "blank-agnostic", but that
was more of a joke). So, the third choice already exists, and there is
even a patch implementing it.

Of course, "blank-insignificant" is not perfect either, specifically,
it's not immediately obvious that only trailing blanks are
insignificant here. Perhaps a better term would be
"trailing-blanks-insignificant", with or without hyphens. But in my
opinion. both are much more appropriate and less misleading than
either "blank-padded" or "blank-trimmed".

>
> Though I’m doubtful there is a nice precise hyphenated word to be found here for “treats any trailing spaces as being
semanticallyinsignificant”. 

Yes, but nobody requires us to use such a word here. We can use a few
more words if needed.


With best regards,
Sergei Katkovskii

On Wed, 2025-10-15 at 15:29 -0400, Tom Lane wrote:
> Jeff Davis <pgsql@j-davis.com> writes:
> > Please take a look at the attached patch. If you'd like your name
> > included in the commit, please send it as you'd like it to appear.
>
> I don't understand why any of these variants are better than the
> original wording "blank-padded".  That has the non-negligible
> advantage of corresponding to the type name, and furthermore
> appears in many other places in our docs and source code.
>
> There may be some other wording improvements we could make here,
> but I think b69db5173 was fundamentally misguided in this respect.

My suggestion is to just remove the "blank-trimmed" from the documentation.

"bpchar" and "varchar", when used without type modifier, are actually
identical:

SELECT octet_length(BPCHAR 'x   '),
       octet_length(VARCHAR 'x   '),
       octet_length(TEXT 'x   ');

 octet_length │ octet_length │ octet_length
══════════════╪══════════════╪══════════════
            4 │            4 │            4

The blank-trimming only occurs when a "bpchar" is converted to "text",
for example when used with the concatenation operator.

I suggest the following simplification:

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index b81d89e2608..05edab3bd33 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -1177,11 +1177,7 @@ SELECT '52093.89'::money::numeric::float8;
         <entry>fixed-length, blank-padded</entry>
        </row>
        <row>
-        <entry><type>bpchar</type></entry>
-        <entry>variable unlimited length, blank-trimmed</entry>
-       </row>
-       <row>
-        <entry><type>text</type></entry>
+        <entry><type>text</type>, <type>varchar</type>, <type>bpchar</type></entry>
         <entry>variable unlimited length</entry>
        </row>
      </tbody>

Yours,
Laurenz Albe

I suggest the following simplification:

+        <entry><type>text</type>, <type>varchar</type>, <type>bpchar</type></entry>

On Fri, Oct 17, 2025 at 4:49 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

> "bpchar" and "varchar", when used without type modifier, are actually
> identical:
>
> SELECT octet_length(BPCHAR 'x   '),
>        octet_length(VARCHAR 'x   '),
>        octet_length(TEXT 'x   ');
>
>  octet_length │ octet_length │ octet_length
> ══════════════╪══════════════╪══════════════
>             4 │            4 │            4
>
> The blank-trimming only occurs when a "bpchar" is converted to "text",
> for example when used with the concatenation operator.

Unfortunately, BPCHAR and VARCHAR are not identical in other contexts.
The situation is not the same as with BCHAR(n), which is just an alias
for CHAR(n).
SELECT BPCHAR 'x' = VARCHAR 'x   ', VARCHAR 'x' = BPCHAR 'x   ',
VARCHAR 'x' = VARCHAR 'x   ';
true true false
For comparison with BPCHAR trailing blanks are insignificant, but when
we have VARCHAR on both sides, they matter.

With best regards,
Sergei Katkovskii

On Fri, Oct 17, 2025 at 5:46 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

> "Insignificant trailing blanks."

OK, looks good to me too.

With best regards,
Sergei Katkovskii

On Fri, 2025-10-17 at 18:18 +0400, Sergei Katkovsky wrote:
> On Fri, Oct 17, 2025 at 4:49 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> > "bpchar" and "varchar", when used without type modifier, are actually
> > identical:
> >
> > SELECT octet_length(BPCHAR 'x   '),
> >         octet_length(VARCHAR 'x   '),
> >         octet_length(TEXT 'x   ');
> >
> >   octet_length │ octet_length │ octet_length
> > ══════════════╪══════════════╪══════════════
> >              4 │            4 │            4
> >
> > The blank-trimming only occurs when a "bpchar" is converted to "text",
> > for example when used with the concatenation operator.
>
> Unfortunately, BPCHAR and VARCHAR are not identical in other contexts.
> The situation is not the same as with BCHAR(n), which is just an alias
> for CHAR(n).
> SELECT BPCHAR 'x' = VARCHAR 'x   ', VARCHAR 'x' = BPCHAR 'x   ',
> VARCHAR 'x' = VARCHAR 'x   ';
> true true false
> For comparison with BPCHAR trailing blanks are insignificant, but when
> we have VARCHAR on both sides, they matter.

They are stored identically, but behave differently, so I shouldn't have
said that they *are* identical.

Still, they all are variable-length strings, so we could list them together.

But perhaps it is really better to leave things as they are now, perhaps
replacing "blank-trimming", perhaps as "variable-length string that ignores
training blanks".

Yours,
Laurenz Albe

On Sat, Oct 18, 2025 at 2:36 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

> But perhaps it is really better to leave things as they are now, perhaps
> replacing "blank-trimming", perhaps as "variable-length string that ignores
> training blanks".

Looks like nobody is against "insignificant/ignorable trailing
whitespace" or anything like that anymore? Am I right?

With best regards,
Sergei Katkovskii

On Mon, 2025-10-20 at 16:42 +0400, Sergei Katkovsky wrote:
> On Sat, Oct 18, 2025 at 2:36 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> > But perhaps it is really better to leave things as they are now, perhaps
> > replacing "blank-trimming", perhaps as "variable-length string that ignores
> > training blanks".
>
> Looks like nobody is against "insignificant/ignorable trailing
> whitespace" or anything like that anymore? Am I right?

Any verbal description will never completely represent the facts.
But I'd say that "ignores trailing blanks" (not tabs, for example)
is a fairly accurate description.

Yours,
Laurenz Albe

On Mon, 2025-10-20 at 16:22 +0200, Laurenz Albe wrote:
> > Looks like nobody is against "insignificant/ignorable trailing
> > whitespace" or anything like that anymore? Am I right?
>
> Any verbal description will never completely represent the facts.
> But I'd say that "ignores trailing blanks" (not tabs, for example)
> is a fairly accurate description.

Here is a patch along these lines.

Yours,
Laurenz Albe