Postgres Pro
Downloads
Home   >   mailing lists

Thread: Determine if range list contains specified integer

Determine if range list contains specified integer

From
Andrus
Date:

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.

Re: Determine if range list contains specified integer

From
Adrian Klaver
Date:
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

Re: Determine if range list contains specified integer

From
"David G. Johnston"
Date:
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.

Re: Determine if range list contains specified integer

From
Sándor Daku
Date:
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