5 ms·

This is the correct answer. Whichever one you start out with, you will be annoyed if you switch to the other one 5 years later. I started out with mysql, and

This is the correct answer. Whichever one you start out with, you will be annoyed if you switch to the other one 5 years later. I started out with mysql, and when I started working on a postgres project, I was shocked at some of the ways it was lacking (how am I supposed to store email addresses in a database without collations?). But when postgres folks grouse about stuff in mysql, I'm usually nodding along and saying "yeah, that would be nice". They're both great options. If anybody on your team is already an expert at one of them, use that one.

robertlagrant 3y ago
> when I started working on a postgres project, I was shocked at some of the ways it was lacking (how am I supposed to store email addresses in a database without collations?) How long ago was this? :)
- TehShrike 3y ago
  3 years ago. From this comment thread https://news.ycombinator.com/item?id=35908169 https://news.ycombinator.com/item?id=35908169 I infer that Postgres still doesn't support collations.
  - robertlagrant 3y ago
    Oh - is this ci collations, not collations in general?
    - TehShrike 3y ago
      A lot of the collations are useful in different circumstances – but the CI ones probably come up the most often, and are necessary to store email addresses in a way where you store the case information that the user typed in, but can do case-insensitive lookups against in the future.
- funcDropShadow 3y ago
  According to the documentation of Postgres 12 [1] it is possible to use so called non-deterministic collations, which may express case-insensitivity. If that is what you need. The documentation of Postgres 11 [2] states that this was not possible: Note that while this system allows creating collations that “ignore case” or “ignore accents” or similar (using the ks key), PostgreSQL does not at the moment allow such collations to act in a truly case- or accent-insensitive manner. Any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values. [1]: https://www.postgresql.org/docs/12/collation.html https://www.postgresql.org/docs/12/collation.html [2]: https://www.postgresql.org/docs/11/collation.html https://www.postgresql.org/docs/11/collation.html
OJFord 3y ago
> how am I supposed to store email addresses in a database without collations? Not familiar with MySQL so trying to look that up, but with a constraint? Or just don't do that? - SO answer I found says 'it's much more useful to have johndoe@ and JohnDoe@ treated as the same than it is to support case sensitive email addresses'.. ok, it's also incompliant, but whatever's 'more useful’ I guess!

robertlagrant 3y ago

> when I started working on a postgres project, I was shocked at some of the ways it was lacking (how am I supposed to store email addresses in a database without collations?) How long ago was this? :)

TehShrike 3y ago
3 years ago. From this comment thread https://news.ycombinator.com/item?id=35908169 https://news.ycombinator.com/item?id=35908169 I infer that Postgres still doesn't support collations.
- robertlagrant 3y ago
  Oh - is this ci collations, not collations in general?
  - TehShrike 3y ago
    A lot of the collations are useful in different circumstances – but the CI ones probably come up the most often, and are necessary to store email addresses in a way where you store the case information that the user typed in, but can do case-insensitive lookups against in the future.
funcDropShadow 3y ago
According to the documentation of Postgres 12 [1] it is possible to use so called non-deterministic collations, which may express case-insensitivity. If that is what you need. The documentation of Postgres 11 [2] states that this was not possible: Note that while this system allows creating collations that “ignore case” or “ignore accents” or similar (using the ks key), PostgreSQL does not at the moment allow such collations to act in a truly case- or accent-insensitive manner. Any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values. [1]: https://www.postgresql.org/docs/12/collation.html https://www.postgresql.org/docs/12/collation.html [2]: https://www.postgresql.org/docs/11/collation.html https://www.postgresql.org/docs/11/collation.html

OJFord 3y ago

> how am I supposed to store email addresses in a database without collations? Not familiar with MySQL so trying to look that up, but with a constraint? Or just don't do that? - SO answer I found says 'it's much more useful to have johndoe@ and JohnDoe@ treated as the same than it is to support case sensitive email addresses'.. ok, it's also incompliant, but whatever's 'more useful’ I guess!