pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedfilter partitions
5+ messages / 5 participants
[nested] [flat]
* filter partitions
@ 2018-02-13 15:07 Olivier Leprêtre <o.lepretre@gmail.com>
0 siblings, 4 replies; 5+ messages in thread
From: Olivier Leprêtre @ 2018-02-13 15:07 UTC (permalink / raw)
To: pgsql-admin
Hi,
I wonder how could it be possible to extract partitions that contains a
peculiar item
Consider 4 partitions 1, 2, 3, 4 with different items A, B, C... inside
P item
1 A
1 B
1 C
2 B
2 D
2 G
2 H
3 B
3 C
4 X
4 D
4 Z
How could a select return partitions 2,4 because they both contains D item ?
As a result, I would get :
2 B
2 D
2 G
2 H
4 X
4 D
4 Z
I can filter partitions with lag, lead, nth_value... but how could I write
something like "select P,any (item=D) over (partition by P) from..."
Thanks for any help,
---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: filter partitions
@ 2018-02-14 17:58 Samed YILDIRIM <samed@reddoc.net>
parent: Olivier Leprêtre <o.lepretre@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Samed YILDIRIM @ 2018-02-14 17:58 UTC (permalink / raw)
To: Olivier Leprêtre <o.lepretre@gmail.com>; pgsql-admin
<div>Hi Oliver,</div><div> </div><div>You can use tableoid system column of table for filtering.</div><div> </div><div>select tableoid::regclass,* from parent_table where tableoid::regclass in ('p2','p4');</div><div> </div><div><a href="https://www.postgresql.org/docs/10/static/ddl-system-columns.html">https://www.postgresql.org... regards.</div><div><br /></div><div><br /></div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div><br /></div><div><br /></div><div><br /></div><div>13.02.2018, 18:08, "Olivier Leprêtre" <o.lepretre@gmail.com>:</div><blockquote type="cite"><div lang="FR" link="blue" vlink="purple"><div><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">Hi,</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;"> </span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">I wonder how could it be possible to extract partitions that contains a peculiar item </span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;"> </span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">Consider 4 partitions 1, 2, 3, 4 with different items A, B, C... inside</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;"> </span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">P item</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">1 A</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">1 B</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">1 C</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">2 B</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><b><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">2 D</span></b></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">2 G</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">2 H</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">3 B</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">3 C</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">4 X</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><b><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">4 D</span></b></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">4 Z</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;"> </span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;"> </span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">How could a select return partitions 2,4 because they both contains D item ?</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;"> </span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">As a result, I would get :</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;"> </span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">2 B</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">2 D</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">2 G</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">2 H</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">4 X</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">4 D</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">4 Z</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;"> </span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">I can filter partitions with lag, lead, nth_value... but how could I write something like "select P,any (item=D) over (partition by P) from..."</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;"> </span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;">Thanks for any help,</span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US" style="font-size:10pt;font-family:Verdana,sans-serif;"> </span></p><p style="margin:0cm;margin-bottom:0.0001pt;font-size:9pt;font-family:Calibri,sans-serif;color:#1F497D;"><span lang="EN-US"> </span></p></div><div id="DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF26dcb07c6911c6ceecdf6b44df9567f5d"><br /> <table style="border-top:1px solid #D3D4DE;">
<tbody><tr>
<td style="width:55px;padding-top:18px;"><a href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-em...; target="_blank"><img src="https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif"; alt="" width="46" height="29" style="width:46px;height:29px;" /></a></td>
<td style="width:470px;padding-top:17px;color:#41424e;font-size:13px;font-family:Arial,Helvetica,sans-serif;line-height:18px;">Garanti sans virus. <a href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-em...; target="_blank" style="color:#4453ea;">www.avast.com</a> </td>
</tr>
</tbody></table>
<a href="#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF26dcb07c6911c6ceecdf6b44df9567f5d" width="1" height="1"> </a></div></div></blockquote>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: filter partitions
@ 2018-02-15 10:58 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Olivier Leprêtre <o.lepretre@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Shreeyansh Dba @ 2018-02-15 10:58 UTC (permalink / raw)
To: Olivier Leprêtre <o.lepretre@gmail.com>; +Cc: pgsql-admin
Hi Oliver,
You can use the below query to get output for filtering table partition 2,4.
select p,item from parent_table where item='D' and p in (2,4) group by
p,item;
Hope this helps.
<http://www.shreeyansh.com;
On Tue, Feb 13, 2018 at 8:37 PM, Olivier Leprêtre <o.lepretre@gmail.com>
wrote:
> Hi,
>
>
>
> I wonder how could it be possible to extract partitions that contains a
> peculiar item
>
>
>
> Consider 4 partitions 1, 2, 3, 4 with different items A, B, C... inside
>
>
>
> P item
>
> 1 A
>
> 1 B
>
> 1 C
>
> 2 B
>
> *2 D*
>
> 2 G
>
> 2 H
>
> 3 B
>
> 3 C
>
> 4 X
>
> *4 D*
>
> 4 Z
>
>
>
>
>
> How could a select return partitions 2,4 because they both contains D item
> ?
>
>
>
> As a result, I would get :
>
>
>
> 2 B
>
> 2 D
>
> 2 G
>
> 2 H
>
> 4 X
>
> 4 D
>
> 4 Z
>
>
>
> I can filter partitions with lag, lead, nth_value... but how could I write
> something like "select P,any (item=D) over (partition by P) from..."
>
>
>
> Thanks for any help,
>
>
>
>
>
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...; Garanti
> sans virus. www.avast.com
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
> <#m_-6275419886238405028_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: filter partitions
@ 2018-02-16 05:54 Pradeep Kumar <pradeep.kumar.pc@gmail.com>
parent: Olivier Leprêtre <o.lepretre@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Pradeep Kumar @ 2018-02-16 05:54 UTC (permalink / raw)
To: Olivier Leprêtre <o.lepretre@gmail.com>; +Cc: pgsql-admin
select p, max(item) over (partition by p) from item where item='D'
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
On Tue, Feb 13, 2018 at 8:37 PM, Olivier Leprêtre <o.lepretre@gmail.com>
wrote:
> Hi,
>
>
>
> I wonder how could it be possible to extract partitions that contains a
> peculiar item
>
>
>
> Consider 4 partitions 1, 2, 3, 4 with different items A, B, C... inside
>
>
>
> P item
>
> 1 A
>
> 1 B
>
> 1 C
>
> 2 B
>
> *2 D*
>
> 2 G
>
> 2 H
>
> 3 B
>
> 3 C
>
> 4 X
>
> *4 D*
>
> 4 Z
>
>
>
>
>
> How could a select return partitions 2,4 because they both contains D item
> ?
>
>
>
> As a result, I would get :
>
>
>
> 2 B
>
> 2 D
>
> 2 G
>
> 2 H
>
> 4 X
>
> 4 D
>
> 4 Z
>
>
>
> I can filter partitions with lag, lead, nth_value... but how could I write
> something like "select P,any (item=D) over (partition by P) from..."
>
>
>
> Thanks for any help,
>
>
>
>
>
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...; Garanti
> sans virus. www.avast.com
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
> <#m_7873526533910504703_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
--
Pradeep
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: filter partitions
@ 2018-02-16 05:59 pradeep <pradeep.kumar.pc@gmail.com>
parent: Olivier Leprêtre <o.lepretre@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: pradeep @ 2018-02-16 05:59 UTC (permalink / raw)
To: pgsql-admin
use
select p, max(item) over (partition by p) from item where item='D'
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-02-16 05:59 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-02-13 15:07 filter partitions Olivier Leprêtre <o.lepretre@gmail.com>
2018-02-14 17:58 ` Samed YILDIRIM <samed@reddoc.net>
2018-02-15 10:58 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-02-16 05:54 ` Pradeep Kumar <pradeep.kumar.pc@gmail.com>
2018-02-16 05:59 ` pradeep <pradeep.kumar.pc@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox