Subject: BPCHAR description in 8.3. Character Types is misleading and incomplete

From: PG Doc comments form <[email-masked]>
Date: Tue, 14 Oct 2025 12:14:53 +0000

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.

---

From: Jeff Davis <[email-masked]>
Date: Wed, 15 Oct 2025 10:14:46 -0700

> The description of BPCHAR in section 8.3. Character Types is

Hi,

There was a previous discussion here:

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

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

> 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

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index b81d89e2608..ed9c1cba211 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -1178,7 +1178,7 @@ SELECT '52093.89'::money::numeric::float8;
        </row>
        <row>
         <entry><type>bpchar</type></entry>
-        <entry>variable unlimited length, blank-trimmed</entry>
+        <entry>variable unlimited length, blank-insignificant</entry>
        </row>
        <row>
         <entry><type>text</type></entry>
@@ -1298,7 +1298,7 @@ SELECT '52093.89'::money::numeric::float8;
 
    <tip>
     <para>
-     There is no performance difference among these three types,
+     There is no performance difference among these four types,
      apart from increased storage space when using the blank-padded
      type, and a few extra CPU cycles to check the length when storing into
      a length-constrained column.  While

---

From: Tom Lane <[email-masked]>
Date: Wed, 15 Oct 2025 15:29:36 -0400

> 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

---

From: Jeff Davis <[email-masked]>
Date: Wed, 15 Oct 2025 13:09:54 -0700

> 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

---

From: Sergei Katkovsky <[email-masked]>
Date: Thu, 16 Oct 2025 10:30:28 +0400

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

>  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

---

From: Sergei Katkovsky <[email-masked]>
Date: Thu, 16 Oct 2025 10:53:59 +0400

> 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

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 16 Oct 2025 08:36:50 -0400

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

---

From: Sergei Katkovsky <[email-masked]>
Date: Thu, 16 Oct 2025 18:19:51 +0400

On Thu, Oct 16, 2025 at 4:36 PM David G. Johnston
<[email-masked]> 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

---

From: Jeff Davis <[email-masked]>
Date: Thu, 16 Oct 2025 08:21:16 -0700

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

> 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

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 16 Oct 2025 14:11:10 -0400

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

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 16 Oct 2025 14:40:36 -0400

> I see -- so it means that the padding came from somewhere else (not the

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.

---

From: Sergei Katkovsky <[email-masked]>
Date: Thu, 16 Oct 2025 23:00:04 +0400

On Thu, Oct 16, 2025 at 10:11 PM David G. Johnston
<[email-masked]> 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

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 16 Oct 2025 15:18:52 -0400

On Thursday, October 16, 2025, Sergei Katkovsky <[email-masked]>
wrote:

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

> is to change the wording 'blank-trimming' to something more

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.

---

From: Sergei Katkovsky <[email-masked]>
Date: Fri, 17 Oct 2025 00:41:37 +0400

On Thu, Oct 16, 2025 at 11:18 PM David G. Johnston
<[email-masked]> wrote:

> 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

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 16 Oct 2025 17:08:41 -0400

On Thursday, October 16, 2025, Sergei Katkovsky <[email-masked]>
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

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.

---

From: Sergei Katkovsky <[email-masked]>
Date: Fri, 17 Oct 2025 11:37:40 +0400

On Fri, Oct 17, 2025 at 1:08 AM David G. Johnston
<[email-masked]> 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

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 17 Oct 2025 14:49:12 +0200

> 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

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 17 Oct 2025 09:14:51 -0400

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

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 17 Oct 2025 09:46:02 -0400

On Fri, Oct 17, 2025, 03:38 Sergei Katkovsky <[email-masked]> wrote:

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

"Insignificant trailing blanks."

David J.

---

From: Sergei Katkovsky <[email-masked]>
Date: Fri, 17 Oct 2025 18:18:08 +0400

>  octet_length │ octet_length │ octet_length

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

---

From: Sergei Katkovsky <[email-masked]>
Date: Fri, 17 Oct 2025 18:25:35 +0400

> "Insignificant trailing blanks."

OK, looks good to me too.

With best regards,
Sergei Katkovskii

---

From: Laurenz Albe <[email-masked]>
Date: Sat, 18 Oct 2025 00:36:02 +0200

> 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

---

From: Sergei Katkovsky <[email-masked]>
Date: Mon, 20 Oct 2025 16:42:30 +0400

> replacing "blank-trimming", perhaps as "variable-length string that ignores

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

With best regards,
Sergei Katkovskii

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 20 Oct 2025 16:22:50 +0200

> 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

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 23 Oct 2025 08:02:21 +0300

> is a fairly accurate description.

Here is a patch along these lines.

Yours,
Laurenz Albe
From eb7160f4e22777bb9c70f660da2ff4f72792c499 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <[email-masked]>
Date: Thu, 23 Oct 2025 08:00:08 +0300
Subject: [PATCH v1] Improve doc for bpchar without length limit

The documentation stated that "bpchar" was "blank trimmed", which sounds
like blanks are removed, but they aren't.  Rather, trailing blanks
get ignored by most operations.

Author: Jeff Davis <[email-masked]>
Author: Laurenz Albe <[email-masked]>
Reported-By: Sergei Katkovsky <[email-masked]>
Discussion: https://postgr.es/m/176044409338.770.16064383081308443747%40wrigleys.postgresql.org
---
 doc/src/sgml/datatype.sgml | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

index b81d89e2608..ad7ee225973 100644
+        <entry>variable unlimited length, ignores trailing blanks</entry>
 
    <tip>
     <para>
-- 
2.51.0