To change the database’s collation ALTER DATABASE MyDataBase COLLATE [NewCollation] To change the collation of a column ALTER TABLE MyTable ALTER COLUMN Column1 [TYPE] COLLATE [NewCollation] But there are a number of limitations on when you can do this, very notably that this is denied if the column is used in any index. You can … Read more
This answer is outdated. For full emoji support, see this answer. As the character set, if you can, definitely UTF-8. As the collation – that’s a bit nasty for languages with special characters. There are various types of collations. They can all store all Umlauts and other characters, but they differ in how they treat … Read more
Yes, you can be more specific. For example: CREATE DATABASE “scratch” WITH OWNER “postgres” ENCODING ‘UTF8’ LC_COLLATE = ‘en_US.UTF-8’ LC_CTYPE = ‘en_US.UTF-8’; Also I recommend to read the following pages about locales and collations in PostgreSQL: http://www.postgresql.org/docs/current/interactive/locale.html http://www.postgresql.org/docs/current/interactive/collation.html
You can set LC_COLLATE to traditional sort order just for your command: env LC_COLLATE=C sort tmp This won’t change the current environment just the one in which the sort command executes. You should have the same behaviour with this.
This happens because the collations on #tempdb.temp_po.OD1 and STR_IndentDetail.D1 are different (and specifically, note that #tempdb is a different, system database, which is generally why it will have a default opinion for collation, unlike your own databases and tables where you may have provided more specific opinions). Since you have control over the creation of … Read more
Here is a more complete answer: https://www.olcot.co.uk/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as/ The key difference between these collations is in how they apply character expansion rules. Certain Latin characters may be expanded into multiple characters. The SQL_xxxx collations may ignore these character expansions when working with non-unicode text, but apply them for unicode text. As a result: joins, sorts, and … Read more
Yes, this is standard behaviour in the non-language-specific unicode collations. 9.1.13.1. Unicode Character Sets To further illustrate, the following equalities hold in both utf8_general_ci and utf8_unicode_ci (for the effect this has in comparisons or when doing searches, see Section 9.1.7.7, “Examples of the Effect of Collation”): Ä = A Ö = O Ü = U … Read more
Building on your test data, but this works with arbitrary data. This works with any number of elements in the string. Register a composite type made up of one text and one integer value once per database. I call it ai: CREATE TYPE ai AS (a text, i int); The trick is to form an … Read more
SELECT CAST(‘abc’ AS varchar(5)) COLLATE French_CS_AS
Yes, you can be more specific. For example: CREATE DATABASE “scratch” WITH OWNER “postgres” ENCODING ‘UTF8’ LC_COLLATE = ‘en_US.UTF-8’ LC_CTYPE = ‘en_US.UTF-8’; Also I recommend to read the following pages about locales and collations in PostgreSQL: http://www.postgresql.org/docs/current/interactive/locale.html http://www.postgresql.org/docs/current/interactive/collation.html