pg.ddx.io pgsql-admin@postgresql.org mailing list archive help / color / mirror / Atom feed From: Samed YILDIRIM <samed@reddoc.net>
To: Olivier Leprêtre <o.lepretre@gmail.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: filter partitions
Date: Wed, 14 Feb 2018 20:58:37 +0300
Message-ID: <439441518631117@web7j.yandex.ru> (raw )
In-Reply-To: <5a82ff51.8f0c1c0a.ffaa0.df34@mx.google.com >
References: <5a82ff51.8f0c1c0a.ffaa0.df34@mx.google.com >
<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>
view thread (5+ messages) latest in thread
Message-ID: <439441518631117@web7j.yandex.ru>
Permalink: ../439441518631117@web7j.yandex.ru/
Also on: postgresql.org/message-id/439441518631117@web7j.yandex.ru
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: samed@reddoc.net, o.lepretre@gmail.com
Subject: Re: filter partitions
In-Reply-To: <439441518631117@web7j.yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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