From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: Guyren Howe <guyren@gmail.com>
To: Postgres General <pgsql-general@postgresql.org>
Subject: Re: Content for talk on Postgres Type System at PostgresConf
Date: Fri, 1 Mar 2024 08:14:48 -0800
Message-ID: <c40d6a84-c589-4e22-b515-95d034da7bc2@aklaver.com> (raw)
In-Reply-To: <b82f8886db61a3395e6eab94981258274cdbacd3.camel@cybertec.at>
References: <F9A4875C-503C-4D1F-8AA6-0C204F60D3F4@gmail.com>
<08e99a8fa744e063ea31030d6516bfc64c26e3f8.camel@cybertec.at>
<372fd5d1-8d7c-411e-8a35-41793978e74b@Spark>
<b82f8886db61a3395e6eab94981258274cdbacd3.camel@cybertec.at>
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.klaver@aklaver.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, laurenz.albe@cybertec.at, guyren@gmail.com
Subject: Re: Content for talk on Postgres Type System at PostgresConf
In-Reply-To: <c40d6a84-c589-4e22-b515-95d034da7bc2@aklaver.com>
References: <F9A4875C-503C-4D1F-8AA6-0C204F60D3F4@gmail.com> <08e99a8fa744e063ea31030d6516bfc64c26e3f8.camel@cybertec.at> <372fd5d1-8d7c-411e-8a35-41793978e74b@Spark> <b82f8886db61a3395e6eab94981258274cdbacd3.camel@cybertec.at> <c40d6a84-c589-4e22-b515-95d034da7bc2@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox