PostgreSQL – Don't Do This
wiki.postgresql.org
PostgreSQL – Don't Do This
1–10 of 36 posts
Re: PostgreSQL – Don't Do This
#2No, i suggest reverse these kinds of advice and DO THIS instead.
Why ?
Because IF it's said not to do this, it should not be implemented in first place.
This is a trap.
For example, "Don't use table inheritance", you should always use this to reuse the table definition. If not, what's the alternative ?
Re: PostgreSQL – Don't Do This
#3No, i suggest reverse these kinds of advice and DO THIS instead. Why ? Because IF it's said not to do this, it should not be implemented in first place. This is a trap. For example, "Don't use table inheritance", you should always use this to reuse the table definition. If not, what's the alternative ?
The alternative is to just copy paste the table definition or do composition with foreign keys. Seems like field inheritance was invented only because we are too lazy, I have not seen yet valid cases for inheritance in Postgres or any OOP language.
Re: PostgreSQL – Don't Do This
#4I'm baffled by the insistence that "users" (backend services, really) should avoid timestamp in favor of timestampz.
I literally DO NOT understand, how is this good advice.
My backend service exists in some abstraction of a Linux environment, with a fixed timezone, most commonly UTC. It maintains a pool of connections to Postgres, all sharing the same Postgres user, and the same timezone.
Why on earth would I prefer a timestampz to timestamp? Why would I even involve the server timezone into this equation?
I want to store timestamps in a uniform way, I store them in UTC as timestamps. If I stored them in a timestampz column, if the server timezone were to adjust, I'd get literally different values.
If I want to store timezone values per user (of my service), or per operation (that my users perform), then again, surely I have to handle that users move and change their timezones. Again, how does timestampz with its reliance on the connection timezone serve me?
Re: PostgreSQL – Don't Do This
#5Another "valid" use of `timestamp` as opposed to `timestamptz` is for local times that don't all share the same time zone.
For example, if a user can create an event scheduled in their timezone, you probably would want to use a `timestamp` and not `timestamptz`. This way, if the user schedules the event for e.g. 5:00pm, and then the time zone changes (think DST or similar), the event is still at 5:00pm (it won't get shifted like a `timestamptz` would).
Re: PostgreSQL – Don't Do This
#6Another "valid" use of `timestamp` as opposed to `timestamptz` is for local times that don't all share the same time zone. For example, if a user can create an event scheduled in their timezone, you probably would want to use a `timestamp` and not `timestamptz`. This way, if the user schedules the event for e.g. 5:00pm, and then the time zone changes (think DST or similar), the event is still at 5:00pm (it won't get…
The article, although doesn’t mention the shift caused by TZ, does mention that the main difference between the two are that they’re a “point in time” vs “a photo of a calendar and clock”. A (local) event’s time does sound like a photo rather than a point
Re: PostgreSQL – Don't Do This
#7I'm baffled by the insistence that "users" (backend services, really) should avoid timestamp in favor of timestampz. I literally DO NOT understand, how is this good advice. My backend service exists in some abstraction of a Linux environment, with a fixed timezone, most commonly UTC. It maintains a pool of connections to Postgres, all sharing the same Postgres user, and the same timezone. Why on earth would I prefer…
This is actually incorrect, as per Postgres docs `timestamptz` is always stored as a UTC timestamp.
The benefit to this vs a regular timestamp is that when you insert/update a timestamp value, Postgres can then convert that timestamp to UTC if necessary before storing it, and if you select a timestamp value Postgres can convert it from UTC to the timezone you want.
If your connection is set to use UTC, and you always handle UTC timestamps, there probably isn't much practical difference between `timestamp` and `timestamptz`, however.
Re: PostgreSQL – Don't Do This
#8I'm baffled by the insistence that "users" (backend services, really) should avoid timestamp in favor of timestampz. I literally DO NOT understand, how is this good advice. My backend service exists in some abstraction of a Linux environment, with a fixed timezone, most commonly UTC. It maintains a pool of connections to Postgres, all sharing the same Postgres user, and the same timezone. Why on earth would I prefer…
[deleted]
Re: PostgreSQL – Don't Do This
#9I'm baffled by the insistence that "users" (backend services, really) should avoid timestamp in favor of timestampz. I literally DO NOT understand, how is this good advice. My backend service exists in some abstraction of a Linux environment, with a fixed timezone, most commonly UTC. It maintains a pool of connections to Postgres, all sharing the same Postgres user, and the same timezone. Why on earth would I prefer…
This is actually incorrect, as per Postgres docs `timestamptz` is always stored as a UTC timestamp. The benefit to this vs a regular timestamp is that when you insert/update a timestamp value, Postgres can then convert that timestamp to UTC if necessary before storing it, and if you select a timestamp value Postgres can convert it from UTC to the timezone you want. If your connection is set to use UTC, and you always…
Also if you're worried about the server having a different timestamp and possibly not having configured the connection timestamp correctly, as the following test shows Postgres always specifies the timezone of the timestamp it's giving you
server=# CREATE TABLE test1 (date TIMESTAMPTZ NOT NULL);
CREATE TABLE
server=# INSERT INTO test1 VALUES (NOW());
INSERT 0 1
server=# SELECT * FROM test1;
date
2023-06-01 08:00:30.40968+02
(1 row)
server=# SET timezone = 'UTC';
server=# SELECT * FROM test1;
date
2023-06-01 06:00:30.40968+00
(1 row)
Re: PostgreSQL – Don't Do This
#10Another "valid" use of `timestamp` as opposed to `timestamptz` is for local times that don't all share the same time zone. For example, if a user can create an event scheduled in their timezone, you probably would want to use a `timestamp` and not `timestamptz`. This way, if the user schedules the event for e.g. 5:00pm, and then the time zone changes (think DST or similar), the event is still at 5:00pm (it won't get…
The article, although doesn’t mention the shift caused by TZ, does mention that the main difference between the two are that they’re a “point in time” vs “a photo of a calendar and clock”. A (local) event’s time does sound like a photo rather than a point
I think the time of a local event sounds much more important, and a photo of a calendar and a clock sounds much more frivolous. Also, you can't take photos of events that haven't happened yet, and it's precisely with events that haven't happened yet that you most need to store a timestamp and a timezone rather than a postgres "timestamp with timezone" (which does not involve a timezone at all). So the analogy doesn't really tell you how to use it well.