3 ms·

I’d never stumbled across the “don’t do this” wiki entry[0] before. Very handy. [0] https://wiki.postgresql.org/wiki/Don%27t_Do_This https://wiki.postgresql.or

I’d never stumbled across the “don’t do this” wiki entry[0] before. Very handy. [0] https://wiki.postgresql.org/wiki/Don%27t_Do_This https://wiki.postgresql.org/wiki/Don%27t_Do_This

datadrivenangel 2y ago
This reminds me of SQL Anti-patterns, which is a book that everyone who works with databases should read.
gwbas1c 2y ago
Why don't they deprecate some of these features? If they're such easy stumbling blocks, seems like it makes sense to disable things like table inheritance in new schemas, and require some kind of arcane setting to re-enable them.
- mixmastamyk 2y ago
  Several of the broken are SQL standard.
  - FridgeSeal 2y ago
    At what point can we have an update to the standard that fixes a good number of these old hangups?
- __loam 2y ago
  Changing defaults can screw over existing users.
- SoftTalker 2y ago
  Presumably there are rare exceptions where you DO want to do the thing.
- Macha 2y ago
  e.g. the suggested replacement for timestamp is timestamptz, which has its own problems (notably, it eagerly converts to UTC, which means it cannot account for TZ rule changes between storing the date and reading it). If medium term scheduling across multiple countries is something that needs to work in your app, you're kind of stuck with a column with a timestamp and another column with a timezone.
  - stickfigure 2y ago
    `timestamptz` doesn't convert to UTC, it has no timezone - or rather, it gets interpreted as having whatever TZ the session is set to, which could change anytime. Postgres stores the value as a 64 bit microseconds-since-epoch. `timestamp` is the same. It's sad that even the official PG docs get this wrong, and it causes problems downstream like the JDBC driver natively mapping it to OffsetDateTime instead of Instant. But you're right that timestamptz on postgres is different from timestamptz on oracle, which _does_ store a timezone field.
    - Ndymium 2y ago
      Here's the PostgreSQL documentation about timestamptz: > For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone. > When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.4). To me it seems to state quite clearly that timestamptz is converted on write from the input offset to UTC and on read from UTC to whatever the connection timezone is. Can you elaborate on which part of this is wrong? Or maybe we're talking past each other?
      - Terr_ 2y ago
- Parodper 2y ago
  The money one honestly sounds like a bug.
samarthr1 2y ago
That was a fun read, thanks! Made me reconsider a few habits I picked up from MySQL land

I’d never stumbled across the “don’t do this” wiki entry[0] before. Very handy. [0] https://wiki.postgresql.org/wiki/Don%27t_Do_This https://wiki.postgresql.org/wiki/Don%27t_Do_This

datadrivenangel 2y ago

This reminds me of SQL Anti-patterns, which is a book that everyone who works with databases should read.

gwbas1c 2y ago

Why don't they deprecate some of these features? If they're such easy stumbling blocks, seems like it makes sense to disable things like table inheritance in new schemas, and require some kind of arcane setting to re-enable them.

mixmastamyk 2y ago
Several of the broken are SQL standard.
- FridgeSeal 2y ago
  At what point can we have an update to the standard that fixes a good number of these old hangups?
__loam 2y ago
Changing defaults can screw over existing users.
SoftTalker 2y ago
Presumably there are rare exceptions where you DO want to do the thing.
Macha 2y ago
e.g. the suggested replacement for timestamp is timestamptz, which has its own problems (notably, it eagerly converts to UTC, which means it cannot account for TZ rule changes between storing the date and reading it). If medium term scheduling across multiple countries is something that needs to work in your app, you're kind of stuck with a column with a timestamp and another column with a timezone.
- stickfigure 2y ago
  `timestamptz` doesn't convert to UTC, it has no timezone - or rather, it gets interpreted as having whatever TZ the session is set to, which could change anytime. Postgres stores the value as a 64 bit microseconds-since-epoch. `timestamp` is the same. It's sad that even the official PG docs get this wrong, and it causes problems downstream like the JDBC driver natively mapping it to OffsetDateTime instead of Instant. But you're right that timestamptz on postgres is different from timestamptz on oracle, which _does_ store a timezone field.
  - Ndymium 2y ago
    Here's the PostgreSQL documentation about timestamptz: > For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone. > When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.4). To me it seems to state quite clearly that timestamptz is converted on write from the input offset to UTC and on read from UTC to whatever the connection timezone is. Can you elaborate on which part of this is wrong? Or maybe we're talking past each other?
    - Terr_ 2y ago
Parodper 2y ago
The money one honestly sounds like a bug.

samarthr1 2y ago

That was a fun read, thanks! Made me reconsider a few habits I picked up from MySQL land