Date: Thu, 24 Nov 2016 10:22:49 +0100 (CET)
From: Jan Ostrochovsky <jan.ostrochovsky@mobiletech.sk>
To: Metatrader EA <metatraderea@gmail.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Message-ID: <1685623271.4216209.1479979369717.JavaMail.zimbra@mobiletech.sk>
In-Reply-To: 
 <CAJNXAk2G_UBPXdji3bwRyDSPGJHms10wBC-jZ80PSm30tWGaZg@mail.gmail.com>
References: 
 <CAJNXAk2G_UBPXdji3bwRyDSPGJHms10wBC-jZ80PSm30tWGaZg@mail.gmail.com>
Subject: Re: Dynamic queries for Partitions
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_4216208_921123488.1479979369716"
Thread-Topic: Dynamic queries for Partitions
Thread-Index: GIViy8k10R5gb0ObnAyrG2ltTnv9uw==
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

------=_Part_4216208_921123488.1479979369716
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

Hello Bill, 

if I understand correctly, you need to have constraint exclusion turned on and be careful about types - types in WHERE condition should be the same as those with CHECK constraint in partitioned table. Then you won't need 2 steps, but just one - one SELECT from master table fits all. Details are in the PostgreSQL documentation (Partitioning). 

Jano 

> From: "Metatrader EA" <metatraderea@gmail.com>
> To: "pgsql-sql" <pgsql-sql@postgresql.org>
> Sent: Wednesday, 23 November, 2016 15:54:03
> Subject: [SQL] Dynamic queries for Partitions

> Hi,

> When I run one query on one partitioned table.

> Select * from customer where opendate = (date_trunc('month', current_date)+'11
> days'::interval)::date ;

> This query will do that postgres will check all my partitions.

> How can I do ?

> Step 1 generate one sql that will be like "Select * from customer where opendate
> = '2016-11-01' ;

> Step 2 :: Then run code from step1.

> Any advice?

> //Bill

------=_Part_4216208_921123488.1479979369716
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><body><div style=3D"font-family: arial, helvetica, sans-serif; font-s=
ize: 12pt; color: #000000"><div><div>Hello Bill,</div><div><br></div><div>i=
f I understand correctly, you need to have constraint exclusion turned on a=
nd be careful about types - types in WHERE condition should be the same as =
those with CHECK constraint in partitioned table. Then you won't need 2 ste=
ps, but just one - one SELECT from master table fits all. Details are in th=
e PostgreSQL documentation (Partitioning).</div><div><br></div><div>Jano</d=
iv></div><div><br></div><hr id=3D"zwchr" data-marker=3D"__DIVIDER__"><div d=
ata-marker=3D"__HEADERS__"><blockquote style=3D"border-left:2px solid #1010=
FF;margin-left:5px;padding-left:5px;color:#000;font-weight:normal;font-styl=
e:normal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font-s=
ize:12pt;"><b>From: </b>"Metatrader EA" &lt;metatraderea@gmail.com&gt;<br><=
b>To: </b>"pgsql-sql" &lt;pgsql-sql@postgresql.org&gt;<br><b>Sent: </b>Wedn=
esday, 23 November, 2016 15:54:03<br><b>Subject: </b>[SQL] Dynamic queries =
for Partitions<br></blockquote></div><div data-marker=3D"__QUOTED_TEXT__"><=
blockquote style=3D"border-left:2px solid #1010FF;margin-left:5px;padding-l=
eft:5px;color:#000;font-weight:normal;font-style:normal;text-decoration:non=
e;font-family:Helvetica,Arial,sans-serif;font-size:12pt;"><div dir=3D"ltr">=
<div>Hi,<br><br></div><div>When I run one query on one partitioned table. <=
br><br></div><div>Select * from customer where opendate =3D (date_trunc('mo=
nth', current_date)+'11 days'::interval)::date ;<br><br></div><div>This que=
ry will do that postgres will check all my partitions.<br><br></div><div>Ho=
w can I do ?<br><br></div><div>Step 1 generate one sql that will be like "S=
elect * from customer where opendate =3D '2016-11-01' ;<br><br></div><div>S=
tep 2 :: Then run code from step1.<br></div><br><div>Any advice?<br><br></d=
iv><div>//Bill</div></div><br></blockquote></div></div></body></html>
------=_Part_4216208_921123488.1479979369716--