Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


create collation testcoll_numeric(
    provider = icu,
    locale  = '@colNumeric=yes'
);
create type textrange as range ( subtype= text,
multirange_type_name=mulitrange_of_text,
collation = testcoll_numeric);
begin;
create temp table artliik (liiginrlki char(3) primary key);
insert into artliik values('1');
insert into artliik values('3');
insert into artliik values('4');
insert into artliik values('05');
insert into artliik values('7.9');
insert into artliik values('1.9');
insert into artliik values('-.9');
insert into artliik values('999');
insert into artliik values('a 9');
create  temp table strings ( id text primary key, kirjeldLku mulitrange_of_text );
insert into strings values ('item1', mulitrange_of_text(textrange('1'::text,'1'::text,'[]'), textrange('4'::text,'5'::text,'[]')));
insert into strings values ('item3', mulitrange_of_text(textrange('11'::text,'29.9'::text), textrange('14'::text,'16'::text)));
insert into strings values ('item2',
    mulitrange_of_text(textrange('1'::text,'3'::text,'[]'),
        textrange('6'::text,'9'::text,'[]'),
        textrange('23'::text,'45'::text,'[]')
        ));
commit ;
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

table strings order by id;


id
kirjeldlku


item1
{[1,1],[4,5]}

item2
{[1,3],[6,9],[23,45]}

item3
{[11,29.9)}


select strings.id , liiginrlki, kirjeldlku from artliik , strings where strings.kirjeldLku @> liiginrlki ::text
order by 1;


id
liiginrlki
kirjeldlku


item1
1
{[1,1],[4,5]}

item1
4
{[1,1],[4,5]}

item1
05
{[1,1],[4,5]}

item2
1
{[1,3],[6,9],[23,45]}

item2
3
{[1,3],[6,9],[23,45]}

item2
7.9
{[1,3],[6,9],[23,45]}

item2
1.9
{[1,3],[6,9],[23,45]}


select distinct  strings.id from artliik , strings where strings.kirjeldLku @> liiginrlki ::text;


id


item1

item2
id	kirjeldlku
item1	{[1,1],[4,5]}
item2	{[1,3],[6,9],[23,45]}
item3	{[11,29.9)}