pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Guyren Howe <guyren@gmail.com>
To: Postgres General <pgsql-general@postgresql.org>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Subject: Re: Content for talk on Postgres Type System at PostgresConf
Date: Thu, 29 Feb 2024 13:58:41 -0800
Message-ID: <63cdee15-f11b-47cd-b023-d62aa8b8a8fa@aklaver.com> (raw)
In-Reply-To: <372fd5d1-8d7c-411e-8a35-41793978e74b@Spark>
References: <F9A4875C-503C-4D1F-8AA6-0C204F60D3F4@gmail.com>
	<08e99a8fa744e063ea31030d6516bfc64c26e3f8.camel@cybertec.at>
	<372fd5d1-8d7c-411e-8a35-41793978e74b@Spark>


On 2/29/24 1:38 PM, Guyren Howe wrote:
> In case I’m not correct on the issues with these types, myself, what 
> are the misconceptions, or where might I find them for  myself?
>
> 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?
>   * timestamptz is just converted to a timestamp in UTC. Folks might
>     imagine that it stores the time zone but it doesn’t.
>
The issue I see is people getting confused when it come back rotated to 
whatever TimeZone is set to, which can change from client to client.
>
>   * time with time zone *does* store the time zone, but this isn’t
>     actually useful and should be avoided (I’m not entirely sure why
>     and the docs only gesture at the problems without stating them, IIRC)
>   * money is a fixed-point decimal value, the number of decimal places
>     is locale determined. I’m not aware of any particular problems
>     with that
>

test=# show lc_monetary ;
  lc_monetary
-------------
  en_US.UTF8
(1 row)

test=# select 1000::money;
    money
-----------
  $1,000.00
(1 row)

test=# set lc_monetary = 'en_GB.utf8';
SET
test=# select 1000::money;
    money
-----------
  £1,000.00


> On 29 Feb 2024 at 01:11 -0800, Laurenz Albe 
> <laurenz.albe@cybertec.at>, wrote:
>> On Wed, 2024-02-28 at 17:08 -0800, Guyren Howe wrote:
>>> I am to talk about Postgres’s type system at PGConf:
>>>
>>> https://postgresconf.org/conferences/2024/program/proposals/postgres-s-type-system
>>>
>>> I picked the issue because I think it’s poorly understood, greatly
>>> under-discussed, and an excellent way to empower postgres users.
>>>
>>> What would you want to see in such a talk?
>>
>> It might be good to explain how "timestamp with time zone" works.
>> That's often confusing for beginners, because it is different from
>> other databases and arguably deviates from the SQL standard.
>>
>> Perhaps it would be good to warn people about using data types like
>> "character", "time with time zone" and "money".
>>
>> Yours,
>> Laurenz Albe

-- 
Adrian Klaver
adrian.klaver@aklaver.com

view thread (17+ messages)  latest in thread

Message-ID: <63cdee15-f11b-47cd-b023-d62aa8b8a8fa@aklaver.com>
Permalink:  ../63cdee15-f11b-47cd-b023-d62aa8b8a8fa@aklaver.com/
Also on:    postgresql.org/message-id/63cdee15-f11b-47cd-b023-d62aa8b8a8fa@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, guyren@gmail.com, laurenz.albe@cybertec.at
  Subject: Re: Content for talk on Postgres Type System at PostgresConf
  In-Reply-To: <63cdee15-f11b-47cd-b023-d62aa8b8a8fa@aklaver.com>
  References: <F9A4875C-503C-4D1F-8AA6-0C204F60D3F4@gmail.com> <08e99a8fa744e063ea31030d6516bfc64c26e3f8.camel@cybertec.at> <372fd5d1-8d7c-411e-8a35-41793978e74b@Spark> <63cdee15-f11b-47cd-b023-d62aa8b8a8fa@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