Re: Content for talk on Postgres Type System at PostgresConf

On 3/1/24 01:18, Laurenz Albe wrote:
> On Thu, 2024-02-29 at 13:38 -0800, Guyren Howe wrote:
>> what are the misconceptions, or where might I find them for  myself?
>
> In addition to what was already said:
>
>> My current understanding:
>>  * character is fixed-length, blank-padded. Not sure when you’d
>> want that, but it seems clear. Is the name just confusing?
>
> I find the semantics confusing:
>
> test=> SELECT 'a'::character(10);
> bpchar
> ════════════
> a
> (1 row)
>
> Ok, it is 10 characters long.
>
> test=> SELECT length('a'::character(10));
> length
> ════════
> 1
> (1 row)
>
> Or is it?

https://www.postgresql.org/docs/current/datatype-character.html

"Values of type character are physically padded with spaces to the
specified width n, and are stored and displayed that way. However,
trailing spaces are treated as semantically insignificant and
disregarded when comparing two values of type character. In collations
where whitespace is significant, this behavior can produce unexpected
results; for example SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2)
returns true, even though C locale would consider a space to be greater
than a newline. Trailing spaces are removed when converting a character
value to one of the other string types. Note that trailing spaces are
semantically significant in character varying and text values, and when
using pattern matching, that is LIKE and regular expressions."

>
> test=> SELECT 'a'::character(10) || 'b'::character(10);
> ?column?
> ══════════
> ab
> (1 row)
>
> And why is the result not 20 characters long, with spaces between "a" and "b"?

SELECT pg_typeof('a'::character(10) || 'b'::character(10));
pg_typeof
-----------
text

This is covered by "Trailing spaces are removed when converting a
character value to one of the other string types.".

Though that still leaves you with:

SELECT pg_typeof(('a'::character(10) || 'b'::character(10))::char(20));
pg_typeof
-----------
character

SELECT ('a'::character(10) || 'b'::character(10))::char(20);
bpchar
----------------------
ab

>
> Best avoid "character".
>
>>  * timestamptz is just converted to a timestamp in UTC. Folks might
>> imagine that it stores the time zone but it doesn’t.
>
> Yes, and I find that lots of people are confused by that.
>
> You could talk about the interaction with the "timezone" parameter, and
> that it is not so much a timestamp with time zone, but an "absolute timestamp",
> and in combination with "timestamp" a great way to let the database handle
> the difficult task of time zone conversion for you.
>
> Yours,
> Laurenz Albe
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com