PostgreSQL SELECT 结果具有不同的 id，它更喜欢来自另一个表的特定值

我有一个some_dictionary表和some_dictionary_language表，其中包含多种语言的数据(以及对some_dictionary的引用).

我需要在一些字典上找到唯一的值.我更喜欢给定语言的结果，如果不是默认值.

给定sql:

create table some_dictionary(
    id bigserial primary key,
    some_text varchar(5),
    some_array integer[]
);

create table some_dictionary_language(
    id bigserial primary key,
    some_dictionary_id bigint not null REFERENCES some_dictionary(id),
    language varchar(64) not null,
    name varchar(128) not null
);

insert into some_dictionary (some_text, some_array)
values
    ('text2', '{1, 32, 2}'),
    ('text1', '{5, 9, 1}'),
    ('text4', '{1, 97, 4}'),
    ('text3', '{616, 1, 55}'),
    ('text5', '{8, 1}'),
    ('text6', '{1}');

insert into some_dictionary_language (some_dictionary_id, language, name)
values
    (2, 'POLISH', 'nazwa2'),
    (1, 'ENGLISH', 'name1'),
    (3, 'ENGLISH', 'name3'),
    (2, 'ENGLISH', 'name2'),
    (1, 'POLISH', 'nazwa1'),
    (1, 'SPANISH', 'nombre1'),
    (4, 'SPANISH', 'nombre1'),
    (5, 'ENGLISH', '5name'),
    (6, 'ENGLISH', '6name'),
    (6, 'POLISH', 'nazwa5'),
    (5, 'POLISH', 'nazwa6');

给定条件参数:

langugage = 'POLISH' or if not, default = 'ENGLISH'
phrase in some_text or name = 'na'
element in some_array = 1
page = 1 size = 10

我的select语句没有明确说明:

select d.id, d.some_text, d.some_array, dl.name, dl.language  
from some_dictionary d 
join some_dictionary_language dl on d.id = dl.some_dictionary_id
where dl."language" in ('POLISH', 'ENGLISH')
and (d.some_text ilike '%na%' or dl.name ilike '%na%')
and 1 = ANY(d.some_array)

Select 结果:

d.id    d.some_text d.some_array    dl.name     dl.lanugage
2   text1       {5,9,1}     nazwa2          POLISH
1   text2       {1,32,2}    name1       ENGLISH
3   text4       {1,97,4}    name3       ENGLISH
2   text1       {5,9,1}     name2       ENGLISH
1   text2       {1,32,2}    nazwa1          POLISH
5   text5       {8,1}       5name       ENGLISH
6   text6       {1}     6name       ENGLISH
6   text6       {1}     nazwa5          POLISH
5   text5       {8,1}       nazwa6          POLISH

预期的 Select 结果，d.id和首选语言为distinct on d.id，其他语言为默认英语:

d.id    d.some_text d.some_array    dl.name     dl.lanugage
1   text2       {1,32,2}    nazwa1          POLISH
2   text1       {5,9,1}     name2       POLISH
3   text4       {1,97,4}    name3       ENGLISH (default!)
5   text5       {8,1}       nazwa6          POLISH
6   text6       {1}     6name       POLISH

我试着这样做:

select distinct on (id) * from (
    select d.id, d.some_text, d.some_array, dl.name, dl.language  
    from some_dictionary d 
    join some_dictionary_language dl on d.id = dl.some_dictionary_id
    where dl."language" in ('POLISH', 'ENGLISH')
    and (d.some_text ilike '%na%' or dl.name ilike '%na%')
    and 1 = ANY(d.some_array)
    order by case when dl."language" = 'POLISH' then 1 end
) sub offset 0 row fetch next 10 rows only;

但它没有正常工作:

d.id    d.some_text d.some_array    dl.name     dl.lanugage
1   text2       {1,32,2}    nazwa1          POLISH
2   text1       {5,9,1}     name2       ENGLISH
3   text4       {1,97,4}    name3       ENGLISH
5   text5       {8,1}       nazwa6          POLISH
6   text6       {1}     6name       ENGLISH