Re: Content for talk on Postgres Type System at PostgresConf

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?

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

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