You can login if you already have an account or register by clicking the button below.
Registering is free and all you need is a username and password. We never ask you for your e-mail.
[–]PuttItOut0 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?
[–]logos_ethos0 points
0 points
0 points
(+0|-0)
ago
(edited ago)
If it was for the data lengths, I added that with CHECK constraints. Constraints are a lot easier to change after the fact than data types.
If you are porting from a case insensitive database to a case sensitive one, then citext is the way to go. That is the most transparent way of porting the database. Otherwise, you will have a lot of unexpected changes to make. If you were developing on a case sensitive database from scratch, then text or varchar with functions might be better, but not necessarily. You would be adding all of the lower functions, case insensitive operators, and index expressions as you went along. If you use an ORM, citext might be required if the ORM does not know how to properly use PostgreSQL's case insensitive operators. In a little over a year, PostgreSQL might be getting case insensitive collations via ICU Collation, but it will basically be doing the same thing as citext, which is transparently making everything lower case for comparison operations and populating indexes.
Let me know if there is anything that I can do to help. If you want case sensitivity by default, I can help add the required case insensitive operations. This will add time to the port though. I do not know .Net very well, but I can be very useful on the database side. If the ORM has issues with case insensitive operations, then you might have to use citext anyway. I plan on helping with performance tuning. To start, try http://pgtune.leopard.in.ua/ if you have not already. When entering RAM, minus anything else running on the server, such as the web server and OS. Number of Connections is an upper limit, so be generous with it and add a few for the reserved connections for DBAs.
view the rest of the comments →
[–] 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?
[–] [deleted] ago
[–] logos_ethos ago (edited ago)
If it was for the data lengths, I added that with CHECK constraints. Constraints are a lot easier to change after the fact than data types.
If you are porting from a case insensitive database to a case sensitive one, then citext is the way to go. That is the most transparent way of porting the database. Otherwise, you will have a lot of unexpected changes to make. If you were developing on a case sensitive database from scratch, then text or varchar with functions might be better, but not necessarily. You would be adding all of the lower functions, case insensitive operators, and index expressions as you went along. If you use an ORM, citext might be required if the ORM does not know how to properly use PostgreSQL's case insensitive operators. In a little over a year, PostgreSQL might be getting case insensitive collations via ICU Collation, but it will basically be doing the same thing as citext, which is transparently making everything lower case for comparison operations and populating indexes.
Let me know if there is anything that I can do to help. If you want case sensitivity by default, I can help add the required case insensitive operations. This will add time to the port though. I do not know .Net very well, but I can be very useful on the database side. If the ORM has issues with case insensitive operations, then you might have to use citext anyway. I plan on helping with performance tuning. To start, try http://pgtune.leopard.in.ua/ if you have not already. When entering RAM, minus anything else running on the server, such as the web server and OS. Number of Connections is an upper limit, so be generous with it and add a few for the reserved connections for DBAs.