pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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






view thread (17+ messages)  latest in thread

Message-ID: <c40d6a84-c589-4e22-b515-95d034da7bc2@aklaver.com>
Permalink:  ../c40d6a84-c589-4e22-b515-95d034da7bc2@aklaver.com/
Also on:    postgresql.org/message-id/c40d6a84-c589-4e22-b515-95d034da7bc2@aklaver.com

 · 

reply

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