BPCHAR description in 8.3. Character Types is misleading and incomplete

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:

/messages/by-id/E1odZyZ-0000g2-AE@gemulon.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

Hello,

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

Yes, I agree. And there is also "blank-agnostic" if you prefer fancy
expressions :)

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.

The patch is correct, in my opinion. Although it would be nice to have
additionally a clear explanation in which contexts blanks in BPCHAR are
insignificant (like in comparison) and in which they are still
meaningful (like in concatenation), that issue could be addressed later
separately, and maybe not even in the docs but rather in wiki.

Regarding my name. I'm new to this list, so I didn't quite understand what
you were asking for exactly. If you just need to know the spelling of my
name, it's Sergei Katkoskii.

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.

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

On Thursday, October 16, 2025, Sergei Katkovsky <skatkovsky@gmail.com>
wrote:

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.

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.

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

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

David J.

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 the input are considered padding for purposes of comparison i.e., manually padding is added by the user as opposed to the system.

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 blanks but 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

On Thursday, October 16, 2025, Sergei Katkovsky <skatkovsky@gmail.com>
wrote:

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.

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”.

And yes, this is intended as a way to make the name of the type and its
behavior consistent. You sorta have to want it to work/make sense; not
fight it on minor nuance.

David J.

On Thursday, October 16, 2025, Jeff Davis <pgsql@j-davis.com> wrote:

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).

Yeah, your original conclusion this couldn’t be labeled blank-padded seems
to have drawn this confusion. If padding is indeed noun-like and not
verb-like then calling it blank-padded works ok. The clarifying text is
then just fine as-is: the comment about any included spaces are
semantically insignificant ties back to calling those spaces “padding” and
thus the type itself remains “blank-padded” in both the and N and non-N
cases. There isn’t a way to make “trimmed” a noun here but those spaces
are not actually removed/trimmed away.

In short, I would change trimmed to padded in the table.

I’d also add “padding” here just to actually use the word in its noun form.

…but trailing spaces are [stored as] semantically insignificant [padding].

(I thought also about trying to remove the phrase “semantically
[in]significant” altogether but at the moment would rather not touch the
following paragraph.)

If we really have to drive the point home I'd also add a footnote marker to
“blank-padded” and then say in the footnote text (right below the table):
A blank-padded value contains zero or more trailing spaces which are
ignored for comparison purposes. These spaces are also called
“semantically insignificant”.

David J.

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. 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 Thursday, October 16, 2025, Sergei Katkovsky <skatkovsky@gmail.com>
wrote:

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.

We are talking about the properties of values stored within the type and
the behavior during their construction and use. For bpchar those values
have no length limitation and any trailing spaces present are considered
padding.

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.

I’m on board with that. Blank-padded is IMO the best “more appropriate”
choice.

In PostgreSQL the behavior and stored contents/representation of a value
are not influenced by a type modifier. It is only used during input to
perform some either or both a validation or transformation. Here, when
present, it performs a padding transform. But present or absent, trailing
spaces, if any, are considered padding. The prose for the section 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 as padding.

David J.

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 is only used during input to perform some either or both a validation or transformation. Here, when present, it performs a padding transform. But present or absent, trailing spaces, if any, are considered padding. The prose for the section 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 as padding.

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 Thursday, October 16, 2025, Sergei Katkovsky <skatkovsky@gmail.com>
wrote:

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.

I’m just trying to phrase the documentation for bpchar so that the “bp”,
which stands for “blank-padded”, is justified. The generic term for the
trailing spaces here is padding in the noun sense.

I do understand the terminology confusion with the verb padding. And see
why “trimmed” is actively confusing. The prose probably 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. Provide a third choice besides
status-quo and my option.

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

David J.

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 prose probably 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. Provide a 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 semantically insignificant”.

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

On Friday, October 17, 2025, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

I suggest the following simplification:

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

Calling bpchar an alias of text/varchar does not improve matters. Sure,
the type itself doesn’t actually care about trailing spaces, but in
practice operations on bpchar values do not behave the same as those on
text values.

select '123 '::bpchar = '123 '::bpchar; // true

David J.

On Fri, Oct 17, 2025, 03:38 Sergei Katkovsky <skatkovsky@gmail.com> wrote:

Perhaps a better term would be
"trailing-blanks-insignificant", with or without hyphens.

"Insignificant trailing blanks."

David J.

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