Don't Do This with PostgreSQL | Hacker News Reader

Don't Do This with PostgreSQL

wiki.postgresql.org

64 pointssh_tomer15 comments

Most of the points here are "don't do it, but if you do it's not a problem". It should be renamed with "Be careful about this"

I think it's more like, "don't do this" because there is perfectly saner alternatives available.

Each section has 'why not' and 'when should you', so really it's a "Do (only) when" recommendation.

I don't see sufficient justification for "Don't use serial"--the some weird behaviors links to a 404 page.

The "Don't use varchar(n) by default" reasoning is similarly weak.

This post doesn't doesn't give enough detail reasoning to be self-evident, and other than the quality of formatting could be a collection of anyone's blog posts. Some recommendations seem to be solid, but mixed with the arbitrary ones loses some of its recommendation strength.

This linter tool checks a schema for those issues: https://www.npmjs.com/package/schemalint (it is linked from the doc itself, too). It is kind of handy.

Enforcing UTC inside the system and converting to the correct time zone for display purposes is fine though. Makes a lot of sense when the recorded time zone isn't relevant in itself, booking systems for example.

Use UTC + convert to local time for display if the time is a global event, i.e. something that happens at the same time everywhere. E.g. an online meeting, conference start time, missile launch schedule, etc.

Use time + timezone if the time is always local to a particular user and should follow their time zone. E.g. wake up alarm, eat breakfast schedule,

The problem is these definitions are already a bit fuzzy and it's not always clear which type it is even to the person creating it.

this was mentioned in the other post:

https://news.ycombinator.com/item?id=42111896 "What I Wish Someone Told Me About Postgres"

> 5.2 Don't use char(n) even for fixed-length identifiers

Well, i think "char(1) not null" is a valid choice for a very compact 1-byte field, "smallint" is twice as big

A char can be multiple bytes. If 1 byte is what you want, you probably want bytea (with a constraint) or bit(8).

There's also "char", with quotes, as a special type. It's always 1 byte.

The other types have overhead. A single-byte bytea will actually use 2 bytes, char(1) will use at least that much, and bit(8) will use 7 bytes. Smallint is strictly better for representing small numbers.

TIL: https://www.postgresql.org/docs/17/datatype-character.html (it's in the table at the bottom). I'd still be suspicious of using it, and the docs recommend against it, but it exists.

A good reason not to use it is that 49::"char"::int == 49, but 49::"char"::smallint == 1. In other words, int casts directly interpret the value as an integer, but smallint (and bigint) casts interpret it as an ASCII character, '1', which they then convert to the integer 1.

This is such a useful doc. My favorite part is that it has the authority of the postgresql.org domain making it nice to cite.

I am amazed how a good chunk of language runtimes, databases etc do not have a simple timestamp type like unix time.

I don't use postgreSqll but Sooner I will use thanks

Reply on news.ycombinator.com