Menu
I am new to Postgres. I have just started learning it from here. I found COLLATE "C" from a select statement
SELECT not_equal(first_name, last_name COLLATE "C")
What does COLLATE "C" do?
825
A collation is an SQL schema object that maps a SQL name to operating system locales. To create a collation, you must have a CREATE privilege on the destination schema. The Collation dialog organizes the development of a collation through the following dialog tabs: General and Definition.
In database systems, Collation specifies how data is sorted and compared in a database. Collation provides the sorting rules, case, and accent sensitivity properties for the data in the database.
You cannot to change these values for already created databases. In this moment, when there are not other databases, the most easy solution is a) stop database, b) delete data directory, c) run manually initdb with options --encoding and --locale (run this command under postgres user).
The older PostgreSQL method for performing case-insensitive text operations is the citext type; it is similar to the text type, but operators are functions between citext values are implicitly case-insensitive. The PostgreSQL docs provide more information on this type.
Collation is used to sort strings (text), for example by alphabetic order, whether or not case matters, how to deal with letters that have accents etc. COLLATE "C" tells the database not to use collation at all. One might use this if they were designing a database to hold data in different languages. Technically, COLLATE "C" will use byte order to drive text comparisons.
The first answer on https://dba.stackexchange.com/questions/94887/what-is-the-impact-of-lc-ctype-on-a-postgresql-database provides a good example of the differences between using COLLATE "C" vs. COLLATE "fr_FR" which uses the French localization.
112
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
