RE: FW: BUG #17258: Unexpected results in CHAR(1) data type

I disagree about it being a feature request.

I was only using substr to demonstrate the problem.

Here is the problem without using any functions:

create table x (a char(1));

insert into x values ('x');

insert into x values (' ');

select length(a) from x;

1

0

From: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Sent: Friday, October 29, 2021 3:40 PM
To: David(at)calascibetta(dot)com
Cc: PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: FW: BUG #17258: Unexpected results in CHAR(1) data type

On Fri, Oct 29, 2021 at 1:17 PM David M. Calascibetta <david(at)calascibetta(dot)com <mailto:david(at)calascibetta(dot)com> > wrote:

Subject: RE: BUG #17258: Unexpected results in CHAR(1) data type

Ok, but my example was just a simplified version of what is going on.
The actual problem stems from a CHAR(1) column data type that is behaving the same way.
I was just trying to create a super-simple example of the problem.
It still seems to me that a CHAR(1) should never be zero length, regardless of how it's implemented.

This qualifies as a feature request, not a bug. One could write a version of substr that does what you expect (it probably wouldn't be named substr though) and takes in a character data type. It's just no one has, nor is likely to. Thus you are stuck using versions that take in text and you get the char-to-text casting side effects.

If you do octet_length(' ':: character(1)) it will return 1, not zero. So it indeed has a length one.

David J.