Determine if range list contains specified integer

On 5/27/22 03:51, Andrus wrote:

Hi!

How to all properties for given type.
Query

    select id
    from artliik
    join strings on ','||trim(strings.kirjeldLku)||',' like
'%,'||trim(artliik.liiginrlki)||',%'

returns date for single integer list only.
How to change join so that type ranges in list like 6-9 are also returned?
Eq. f list contains 6-9, Type 6,7,8 and 9 shoud included in report.

Start over with a more rational data model.

Andrus.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, May 27, 2022 at 9:19 AM Andrus <kobruleht2@hot.ee> wrote:

Product type table contains product types. Some ids may missing :

create table artliik (liiginrlki char(3) primary key);

As an aside, don't use the "character" data type:

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_char.28n.29

create table strings ( id char(100) primary key, kirjeldLku chr(200) );
insert into strings values ('item1', '1,4-5' );
insert into strings values ('item2', '1,2,3,6-9,23-44,45' );

Those are ranges. PostgreSQL has actual range types. Using them instead
of using text should make life considerably easier.

If you can go with PostgreSQL v14 you get access to multirange types.

Absent that you probably can use PostgreSQL array of ranges to accomplish a
similar goal.

David J.

On Fri, 27 May 2022 at 18:19, Andrus <kobruleht2@hot.ee> wrote:

Hi!

Product type table contains product types. Some ids may missing :

create 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('999');

Property table contais comma separated list of types.

create table strings ( id char(100) primary key, kirjeldLku chr(200) );
insert into strings values ('item1', '1,4-5' );
insert into strings values ('item2', '1,2,3,6-9,23-44,45' );

Type can specified as single integer, e.q 1,2,3 or as range like 6-9 or
23-44
List can contain both of them.

How to all properties for given type.
Query

select id
from artliik
join strings on ','||trim(strings.kirjeldLku)||',' like
'%,'||trim(artliik.liiginrlki)||',%'

returns date for single integer list only.
How to change join so that type ranges in list like 6-9 are also returned?
Eq. f list contains 6-9, Type 6,7,8 and 9 shoud included in report.

Postgres 13 is used.

Posted also in

https://stackoverflow.com/questions/72404218/how-determine-if-range-list-contains-specified-integer

Andrus.

Hello,

As Adrian said the best idea would be to redesign your data model. For
example make a third "contains" table where every product/type relationship
is one record. After that your problem would be trivial and your life much
easier.
However, this is a first class place. The customer wants the pain the
customer gets the pain:

Bad idea which solves the immediate problem 1:
Write a function which unpacks your "1,2,3,6-9,4" string into an array
'1','2','3','6','7','8','9','4' and use the string=ANY(array_of_strings)
syntax.

Bad idea which solves the immediate problem 2:
Write a trigger which unpacks the the "1,2,3,6-9,4" form into
"1,2,3,6,7,8,9,4" at insert/update time and then use the the
string=ANY(array_of_strings) syntax.

Regards,
Sándor