4 ms·
Don't Do This with PostgreSQL
- h1fra 2y agoMost 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"
- spprashant 2y agoI think it's more like, "don't do this" because there is perfectly saner alternatives available.
- karmakaze 2y agoEach 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.
- theamk 2y agothis was mentioned in the other post: https://news.ycombinator.com/item?id=42111896 https://news.ycombinator.com/item?id=42111896 "What I Wish Someone Told Me About Postgres"
- avg_dev 2y agoThis linter tool checks a schema for those issues: https://www.npmjs.com/package/schemalint https://www.npmjs.com/package/schemalint (it is linked from the doc itself, too). It is kind of handy.
- hasnain99 2y agoI don't use postgreSqll but Sooner I will use thanks
- deleted 2y ago[deleted]
- out_of_protocol 2y ago> 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
- deleted 2y ago[deleted]
- Volundr 2y agoA char can be multiple bytes. If 1 byte is what you want, you probably want bytea (with a constraint) or bit(8).
- FreakLegion 2y agoThere'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.
- Volundr 2y agoTIL: https://www.postgresql.org/docs/17/datatype-character.html 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.
- FreakLegion 2y agoA 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.
- codr7 2y agoEnforcing 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.
- infogulch 2y agoUse 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.
- onionisafruit 2y agoThis is such a useful doc. My favorite part is that it has the authority of the postgresql.org domain making it nice to cite.
- lostmsu 2y agoI am amazed how a good chunk of language runtimes, databases etc do not have a simple timestamp type like unix time.