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