[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Determine if range list contains specified integer
From:       Sándor Daku <daku.sandor () gmail ! com>
Date:       2022-05-27 17:14:18
Message-ID: CAKyoTgZn++ixz6h-OWRo-i-Q6zt=-eSiatJOifg=BOX-jxBm1w () mail ! gmail ! com
[Download RAW message or body]

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

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div \
dir="ltr"><div dir="ltr">On Fri, 27 May 2022 at 18:19, Andrus &lt;<a \
href="mailto:kobruleht2@hot.ee">kobruleht2@hot.ee</a>&gt; wrote:<br></div><div \
class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">  

    
  
  <div>
    <p>Hi!</p>
    <p><br>
    </p>
    <p>Product type table contains product types. Some ids may missing :<br>
      <br>
             create table artliik (liiginrlki char(3) primary key);<br>
             insert into artliik values(&#39;1&#39;);<br>
             insert into artliik values(&#39;3&#39;);<br>
             insert into artliik values(&#39;4&#39;);<br>
             ...<br>
             insert into artliik values(&#39;999&#39;);<br>
      <br>
      Property table contais comma separated list of types.<br>
      <br>
             create table strings ( id char(100) primary key, kirjeldLku
      chr(200) );<br>
             insert into strings values (&#39;item1&#39;, &#39;1,4-5&#39; );<br>
             insert into strings values (&#39;item2&#39;, \
&#39;1,2,3,6-9,23-44,45&#39; );<br>  <br>
      Type can specified as single integer, e.q 1,2,3 or as range like
      6-9   or 23-44 <br>
      List can contain both of them.<br>
      <br>
      <br>
      How to all properties for given type.<br>
      Query<br>
      <br>
             select id <br>
             from artliik<br>
             join strings on &#39;,&#39;||trim(strings.kirjeldLku)||&#39;,&#39; like
      &#39;%,&#39;||trim(artliik.liiginrlki)||&#39;,%&#39; <br>
      <br>
      returns date for single integer list only.<br>
      How to change join so that type ranges in list like 6-9 are also
      returned?<br>
      Eq. f list contains 6-9, Type 6,7,8 and 9 shoud included in
      report.<br>
      <br>
      Postgres 13 is used.</p>
    <p>Posted also in</p>
    <p><a href="https://stackoverflow.com/questions/72404218/how-determine-if-range-list-contains-specified-integer" \
target="_blank">https://stackoverflow.com/questions/72404218/how-determine-if-range-list-contains-specified-integer</a><br>
  </p>
    <p>Andrus.<br></p></div></blockquote><div><br></div><div>Hello,</div><div><br></div><div>As \
Adrian said the best idea would be to redesign your data model. For example make a \
third &quot;contains&quot; table where every product/type relationship is one record. \
After that your problem would be trivial and your life much \
easier.</div><div>However, this is a first class place. The customer wants the pain \
the customer gets the pain:</div><div><br></div><div>Bad idea which solves the \
immediate problem 1:</div><div>Write a function which unpacks your \
&quot;1,2,3,6-9,4&quot; string into an array \
&#39;1&#39;,&#39;2&#39;,&#39;3&#39;,&#39;6&#39;,&#39;7&#39;,&#39;8&#39;,&#39;9&#39;,&#39;4&#39; \
and use the string=ANY(array_of_strings) syntax.</div><div><br></div><div>Bad idea \
which solves the immediate problem 2:<br></div><div>Write a trigger which unpacks the \
the &quot;1,2,3,6-9,4&quot; form into &quot;1,2,3,6,7,8,9,4&quot; at insert/update \
time and then use the the string=ANY(array_of_strings) \
syntax.</div><div><br></div><div>Regards,</div><div>Sándor</div><div><br></div><div> \
</div></div></div></div></div></div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]