Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table latvian_alphabet(letter char);
insert into latvian_alphabet values 
  ('a'),('ā'),('b'),('c'),('č'),('d'),('e'),('ē'),
  ('f'),('g'),('ģ'),('h'),('i'),('ī'),('j'),('k'),
  ('ķ'),('l'),('ļ'),('m'),('n'),('ņ'),('o'),('p'),
  ('r'),('s'),('š'),('t'),('u'),('ū'),('v'),('z'),('ž');
insert into latvian_alphabet select upper(letter) from latvian_alphabet;
select 'default' as "collation", string_agg(letter,'' order by letter collate "default") as whole_alphabet from latvian_alphabet
union all
select 'lv-x-icu',string_agg(letter,'' order by letter collate "lv-x-icu") from latvian_alphabet
union all
select 'lv-LV-x-icu',string_agg(letter,'' order by letter collate "lv-LV-x-icu") from latvian_alphabet
--union all
--select 'unicode',string_agg(letter,'' order by letter collate "unicode") from latvian_alphabet
;

CREATE TABLE



INSERT 0 33



INSERT 0 33




collation
whole_alphabet


default
ABCDEFGHIJKLMNOPRSTUVZabcdefghijklmnoprstuvzĀāČčĒēĢģĪīĶķĻļŅņŠšŪūŽž

lv-x-icu
aAāĀbBcCčČdDeEēĒfFgGģĢhHiIīĪjJkKķĶlLļĻmMnNņŅoOpPrRsSšŠtTuUūŪvVzZžŽ

lv-LV-x-icu
aAāĀbBcCčČdDeEēĒfFgGģĢhHiIīĪjJkKķĶlLļĻmMnNņŅoOpPrRsSšŠtTuUūŪvVzZžŽ



SELECT 3


CREATE COLLATION latvian_upper_first (
   PROVIDER = icu,
   LOCALE = 'lv@colCaseFirst=upper' );
CREATE COLLATION latvian_lower_first (
   PROVIDER = icu,
   LOCALE = 'lv@colCaseFirst=lower' );

CREATE COLLATION



CREATE COLLATION


CREATE COLLATION latvian_lower_first_custom (
   PROVIDER = icu,
   LOCALE = 'lv@colCaseFirst=lower',
   RULES = '& a < ā & A < Ā & c < č & C < Č & e < ē & E < Ē & g < ģ & G < Ģ & k < ķ & K < Ķ & l < ļ & L < Ļ & n < ņ & N < Ņ & s < š & S < Š & u < ū & U < Ū & z < ž & Z < Ž'
);
CREATE COLLATION latvian_upper_first_custom (
   PROVIDER = icu,
   LOCALE = 'lv@colCaseFirst=upper',
   RULES = '& a < ā & A < Ā & c < č & C < Č & e < ē & E < Ē & g < ģ & G < Ģ & k < ķ & K < Ķ & l < ļ & L < Ļ & n < ņ & N < Ņ & s < š & S < Š & u < ū & U < Ū & z < ž & Z < Ž'
);
CREATE COLLATION "latUvian_custom" (
   PROVIDER = icu,
   LOCALE = 'lv',
   RULES = '& a < ā & A < Ā & c < č & C < Č & e < ē & E < Ē & g < ģ & G < Ģ & k < ķ & K < Ķ & l < ļ & L < Ļ & n < ņ & N < Ņ & s < š & S < Š & u < ū & U < Ū & z < ž & Z < Ž'
);
CREATE COLLATION latvian_custom (PROVIDER=icu,LOCALE='lv',
   RULES='&a<Ā=ā &c<Č=č &e<Ē=ē &g<Ģ=ģ &i<Ī=ī &k<Ķ=ķ 
          &l<Ļ=ļ &n<Ņ=ņ &s<Š=š &u<Ū=ū &z<Ž=ž'
);

CREATE COLLATION



CREATE COLLATION



CREATE COLLATION



CREATE COLLATION


create table latvian_letter_pairs0 as
select a.letter||b.letter as letter_pair
from (values ('ā'),('a'),('A'),('Ā'),('z'),('Z')) a(letter), 
  (values ('ā'),('a'),('A'),('Ā'),('z'),('Z'),('')) b(letter);
--insert into latvian_letter_pairs table latvian_alphabet;
select string_agg(letter_pair,' ' order by random()) from latvian_letter_pairs0;

create table latvian_letter_pairs (like latvian_letter_pairs0);
insert into latvian_letter_pairs values ('bā'),('āa'),('ac'),('Āb'),('Ā');
select string_agg(letter_pair,' ' order by random()) from latvian_letter_pairs;

--truncate latvian_letter_pairs;
--insert into latvian_letter_pairs values ('ā'),('a'),('A'),('Ā'),('z'),('Z');
--select string_agg(letter_pair,' ' order by random()) from latvian_letter_pairs;

SELECT 42




string_agg


āA ZĀ āā Ā Āa Zz za aā ZZ zA zĀ A aA ĀZ aZ zā ĀA zz AA Zā āZ Āz ĀĀ āz Āā āĀ z āa zZ az Aa Aā ā aĀ Za AZ Z aa Az ZA a AĀ



SELECT 1



CREATE TABLE



INSERT 0 5




string_agg


bā ac āa Ā Āb



SELECT 1


create table collation_comparisons_on_pairs (
  collation_name text, position int, letter_pair text);
insert into collation_comparisons_on_pairs values 
  ('expected',1,'ac'),
  ('expected',2,'Ā'),
  ('expected',3,'āa'),
  ('expected',4,'Āb'),
  ('expected',5,'bā');

create table collation_comparisons (
  collation_name text, sorted_samples text);--, more_sorted_samples text);
insert into collation_comparisons select 'expected',E'ac\tĀ\tāa\tĀb\tbā';

do $f$
declare current_collation text;
begin
  foreach current_collation in array array['default',
                                 'unicode',
                                 'lv-x-icu',
                                 'lv-LV-x-icu',
                                 'latUvian_custom',
                                 'latvian_custom',
                                 'latvian_upper_first',
                                 'latvian_upper_first_custom',
                                 'latvian_lower_first_custom',
                                 'latvian_lower_first']::text[] loop
      execute format($s$ 
                    insert into collation_comparisons_on_pairs
                    select %1$L,
                           row_number()over(order by letter_pair collate %1$I),
                           letter_pair 
                    from latvian_letter_pairs
                    order by letter_pair collate %1$I;

                    insert into collation_comparisons
                    select %1$L,
                           string_agg(a.letter_pair,E'\t' order by a.letter_pair collate %1$I)
                           
                    from latvian_letter_pairs a;
                    $s$
                    ,current_collation); 
    end loop;
end $f$;

select * from collation_comparisons 
order by collation_name='expected' desc,collation_name;

CREATE TABLE



INSERT 0 5



CREATE TABLE



INSERT 0 1



DO




collation_name
sorted_samples


expected
ac	Ā	āa	Āb	bā

default
ac	bā	Ā	Āb	āa

latUvian_custom
ac	Ā	Āb	āa	bā

latvian_custom
ac	Ā	āa	Āb	bā

latvian_lower_first
Ā	āa	Āb	ac	bā

latvian_lower_first_custom
ac	Ā	Āb	āa	bā

latvian_upper_first
Ā	āa	Āb	ac	bā

latvian_upper_first_custom
ac	Ā	Āb	āa	bā

lv-LV-x-icu
Ā	āa	Āb	ac	bā

lv-x-icu
Ā	āa	Āb	ac	bā

unicode
Ā	āa	Āb	ac	bā



SELECT 11


CREATE EXTENSION IF NOT EXISTS tablefunc;
--select * from collation_comparisons_on_pairs order by position, collation_name;
SELECT *
FROM   crosstab(
   'SELECT position, collation_name, letter_pair
    FROM   collation_comparisons_on_pairs
    ORDER  BY position,collation_name'
   ) AS ct ("position" int, 
            "default" text, 
            "expected" text,
            "latUvian_custom" text,
            "latvian_custom" text,
            "latvian_lower_first" text, 
            "latvian_lower_first_custom" text, 
            "latvian_upper_first" text, 
            "latvian_upper_first_custom"  text,
            "lv-LV-x-icu" text, 
            "lv-x-icu" text,
            "unicode" text
  )
limit 24;

CREATE EXTENSION




position
default
expected
latUvian_custom
latvian_custom
latvian_lower_first
latvian_lower_first_custom
latvian_upper_first
latvian_upper_first_custom
lv-LV-x-icu
lv-x-icu
unicode


1
ac
ac
ac
ac
Ā
ac
Ā
ac
Ā
Ā
Ā

2
bā
Ā
Ā
Ā
āa
Ā
āa
Ā
āa
āa
āa

3
Ā
āa
Āb
āa
Āb
Āb
Āb
Āb
Āb
Āb
Āb

4
Āb
Āb
āa
Āb
ac
āa
ac
āa
ac
ac
ac

5
āa
bā
bā
bā
bā
bā
bā
bā
bā
bā
bā



SELECT 5

collation	whole_alphabet
default	ABCDEFGHIJKLMNOPRSTUVZabcdefghijklmnoprstuvzĀāČčĒēĢģĪīĶķĻļŅņŠšŪūŽž
lv-x-icu	aAāĀbBcCčČdDeEēĒfFgGģĢhHiIīĪjJkKķĶlLļĻmMnNņŅoOpPrRsSšŠtTuUūŪvVzZžŽ
lv-LV-x-icu	aAāĀbBcCčČdDeEēĒfFgGģĢhHiIīĪjJkKķĶlLļĻmMnNņŅoOpPrRsSšŠtTuUūŪvVzZžŽ

collation_name	sorted_samples
expected	ac Ā āa Āb bā
default	ac bā Ā Āb āa
latUvian_custom	ac Ā Āb āa bā
latvian_custom	ac Ā āa Āb bā
latvian_lower_first	Ā āa Āb ac bā
latvian_lower_first_custom	ac Ā Āb āa bā
latvian_upper_first	Ā āa Āb ac bā
latvian_upper_first_custom	ac Ā Āb āa bā
lv-LV-x-icu	Ā āa Āb ac bā
lv-x-icu	Ā āa Āb ac bā
unicode	Ā āa Āb ac bā

position	default	expected	latUvian_custom	latvian_custom	latvian_lower_first	latvian_lower_first_custom	latvian_upper_first	latvian_upper_first_custom	lv-LV-x-icu	lv-x-icu	unicode
1	ac	ac	ac	ac	Ā	ac	Ā	ac	Ā	Ā	Ā
2	bā	Ā	Ā	Ā	āa	Ā	āa	Ā	āa	āa	āa
3	Ā	āa	Āb	āa	Āb	Āb	Āb	Āb	Āb	Āb	Āb
4	Āb	Āb	āa	Āb	ac	āa	ac	āa	ac	ac	ac
5	āa	bā	bā	bā	bā	bā	bā	bā	bā	bā	bā

position	default	expected	latUvian_custom	latvian_custom	latvian_lower_first	latvian_lower_first_custom	latvian_upper_first	latvian_upper_first_custom	lv-LV-x-icu	lv-x-icu	unicode
1	ac	ac	ac	ac	Ā	ac	Ā	ac	Ā	Ā	Ā
2	bā	Ā	Ā	Ā	āa	Ā	āa	Ā	āa	āa	āa
3	Ā	āa	Āb	āa	Āb	Āb	Āb	Āb	Āb	Āb	Āb
4	Āb	Āb	āa	Āb	ac	āa	ac	āa	ac	ac	ac
5	āa	bā	bā	bā	bā	bā	bā	bā	bā	bā	bā

position	default	expected	latUvian_custom	latvian_custom	latvian_lower_first	latvian_lower_first_custom	latvian_upper_first	latvian_upper_first_custom	lv-LV-x-icu	lv-x-icu	unicode
1	ac	ac	ac	ac	Ā	ac	Ā	ac	Ā	Ā	Ā
2	bā	Ā	Ā	Ā	āa	Ā	āa	Ā	āa	āa	āa
3	Ā	āa	Āb	āa	Āb	Āb	Āb	Āb	Āb	Āb	Āb
4	Āb	Āb	āa	Āb	ac	āa	ac	āa	ac	ac	ac
5	āa	bā	bā	bā	bā	bā	bā	bā	bā	bā	bā