Thread: bpchar datatype is not equal to character(1) data type
in the documents (https://www.postgresql.org/docs/current/typeconv-query.html),there is the following descrition:
“blank-padded char”, the internal name of the character data type.
===>>so bpchar datatype is equal to character data type
in the documents (https://www.postgresql.org/docs/15/datatype-character.html),there is the following descrition:
character without length specifier is equivalent to character(1).
===>>so character data type is equal to character(1) data type
Based on the above description, there is a deduction as follows:
bpchar datatype is equal to character(1) data type
but the following test tells us that: bpchar datatype is not equal to character(1) data type.
I want to know why? Maybe the document has error? and I want to know that: bpchar datatype is equal to which datatype?
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=# create table xxx2(c1 char,c2 int);
CREATE TABLE
postgres=# insert into xxx2 values('aaaaa',1);
ERROR: value too long for type character(1) <<<<----please note this error!
postgres=# \d+ xxx2;
Table "public.xxx2"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+--------------+-----------+----------+---------+----------+-------------+--------------+-------------
c1 | character(1) | | | | extended | | |
c2 | integer | | | | plain | | |
Access method: heap
postgres=# create table xxx3(c1 bpchar,c2 int);
CREATE TABLE
postgres=# insert into xxx3 values('aaaaa',1);
INSERT 0 1
postgres=# \d+ xxx3;
Table "public.xxx3"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
c1 | bpchar | | | | extended | | |
c2 | integer | | | | plain | | |
Access method: heap
postgres=# select * from xxx3; <<<<----please note this result.
c1 | c2
-------+----
aaaaa | 1
(1 row)
postgres=#
On Tue, 2023-06-06 at 22:30 +0800, yanliang lei wrote: > in the documents (https://www.postgresql.org/docs/current/typeconv-query.html),there is the following descrition: > “blank-padded char”, the internal name of the character data type. > ===>>so bpchar datatype is equal to character data type > > in the documents (https://www.postgresql.org/docs/15/datatype-character.html),there is the following descrition: > character without length specifier is equivalent to character(1). > ===>>so character data type is equal to character(1) data type > > Based on the above description, there is a deduction as follows: > bpchar datatype is equal to character(1) data type > > but the following test tells us that: bpchar datatype is not equal to character(1) data type. > I want to know why? Maybe the document has error? > > [...] > > postgres=# create table xxx3(c1 bpchar,c2 int); > CREATE TABLE > postgres=# insert into xxx3 values('aaaaa',1); > INSERT 0 1 > postgres=# \d+ xxx3; > Table "public.xxx3" > Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description > --------+---------+-----------+----------+---------+----------+-------------+--------------+------------- > c1 | bpchar | | | | extended | | | > c2 | integer | | | | plain | | | > Access method: heap > > postgres=# select * from xxx3; <<<<----please note this result. > c1 | c2 > -------+---- > aaaaa | 1 > (1 row) There is some special case code in "gram.y" that treats column definitions of CHAR or BIT special, since the standard requires that that means a length of 1. See the following source comment: /* We have a separate ConstTypename to allow defaulting fixed-length * types such as CHAR() and BIT() to an unspecified length. * SQL9x requires that these default to a length of one, but this * makes no sense for constructs like CHAR 'hi' and BIT '0101', * where there is an obvious better choice to make. * Note that ConstInterval is not included here since it must * be pushed up higher in the rules to accommodate the postfix * options (e.g. INTERVAL '1' YEAR). Likewise, we have to handle * the generic-type-name case in AexprConst to avoid premature * reduce/reduce conflicts against function names. */ So the standard demands that behavior for CHAR or BIT, but it does not define the behavior of "bpchar", so we have no special case code for that. If you read the documentation carefully, it says that "bpchar is the internal name of the character data type", but it doesn't say that the behave identical. "bpchar" is not even mentioned in https://www.postgresql.org/docs/current/datatype-character.html , so I think it is OK to consider it an internal thing that should not be used by users directly. I think that there is no need to document that small behavior difference. Yours, Laurenz Albe
On Tue, Jun 6, 2023 at 7:31 AM yanliang lei <msdnchina@163.com> wrote:
Based on the above description, there is a deduction as follows:
bpchar datatype is equal to character(1) data type
Nope, bpchar is not equal to any user-facing data type by virtue of it being an internal implementation-only data type. Based upon your example I'd say it is basically a length-unconstrained character equivalent and there is no such user-facing data type defined.
David J.