[–] logos_ethos 0 points 2 points 2 points (+2|-0) ago (edited ago)
@PuttItOut Use the citext extension (CREATE EXTENSION citext;) and citext data type if case insensitive matching is needed at the index or SQL level by default. Or use expression indexes with lower function (but you need this in every case insensitive query).
Here is a patch for the SQL files:
voat-citext.patch https://filetea.me/n3woeqSt4vKRregB0vXc0112g
Also, once you have applied that patch, you may want this one if you want time stored in absolute time / UTC instead of "wall clock time." See this: http://phili.pe/posts/timestamps-and-time-zones-in-postgresql/
voat-tz.patch: https://filetea.me/n3wtAeS205BTD6qDfR7WBxyLw
[–] logos_ethos 0 points 1 point 1 point (+1|-0) ago
Hi @PuttItOut
I am pinging you just to let you know that I fixed the citext patch link (I guess it expired) and added the tz patch if you want it.
[–] PuttItOut 0 points 3 points 3 points (+3|-0) ago
I don't remember what the reason was that we chose to not go with citext. There was some reason. I'm willing to look into this again but if I recall correctly there was a deal breaker.
@FuzzyWords, do your remember if you talked me out of this or I did that myself?
[–] Germ22 0 points 1 point 1 point (+1|-0) ago
i was going to post our findings here last night, but figured the case sencitivity of voutgres was already known.
it is very interesting how sensitive and far reaching this issue ended up being.
so sofar, the only thing i know that is not case sensitive are the usernames.
[–] PuttItOut 0 points 2 points 2 points (+2|-0) ago
This is a great bug. I love it.