Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dKpd2-0005kK-1N for pgadmin-hackers@arkaria.postgresql.org; Tue, 13 Jun 2017 17:29:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dKpd1-00082Q-K9 for pgadmin-hackers@arkaria.postgresql.org; Tue, 13 Jun 2017 17:29:19 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dKpd0-0007ss-3q for pgadmin-hackers@postgresql.org; Tue, 13 Jun 2017 17:29:18 +0000 Received: from mail-ot0-x233.google.com ([2607:f8b0:4003:c0f::233]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dKpcs-0008U1-Ia for pgadmin-hackers@postgresql.org; Tue, 13 Jun 2017 17:29:13 +0000 Received: by mail-ot0-x233.google.com with SMTP id t31so93145374ota.1 for ; Tue, 13 Jun 2017 10:29:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pivotal-io.20150623.gappssmtp.com; s=20150623; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=D0mTH3IqF1X3QsItqOgmzwDy3y9BQKPHLoBpCU7GpbA=; b=nGfZJX5Y2ziiTXW7vgc8Ar2XEOJOql0RzA7vGhVF+c2G8Ttn/MUYJ/B7n8U561UvN7 P/t0EVsYS4xvrp4vds90WxweBZY8HFz14pTZtfCWUmS+eb2DfKiDysSCievHOlmd92rH EDkJh7ri8o3hq1GkMJV4MBnmcW0OgYbxqc7lQStMAMsM1LhHAQ0S2NkhNjAvtFBmz+TQ llLKizEWJ17tOiU5lZXAxTa3gfoEZwt/9KjgwXhfzCBLXQErlF44kXWbaCPxB4NPBqA+ 6c6BoNmuZHjMNoPDS68D0TbqMagWUXxkah8etJiQWkK6KY3LfLiso3UFGZpxgPjfpqjm 7udA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=D0mTH3IqF1X3QsItqOgmzwDy3y9BQKPHLoBpCU7GpbA=; b=ti+7emvJCX3M9N2+N1LX5DRqbHv91uBfqEmjxuv/YhHUriuZf21BCe+Iv7uWKtNxKe CRWR66kfj8G1rYL9x7WE1rabTvJIwyI9Hg8DLDBpoMa1jB3L2KbWiHsgq8VjaK74ZSy5 fkczL6wXvporW0yFWK1C1Ec/E9jY8WTI6Nj83+LbkTeVwqsKrkjaFrHQIDdLWiCiLOfX xBN4ktfjn+kLM+1OiBW3PAQx4UgZwQ99vjA0rwTL6nFlzszNrIS9PoZLyVLmNgJ8vuAR zNrfhrUj/zXy596VWhKFRSXRyohpGy7xPgohaYFTiQtiCB+VaFfX3RYOai+omkyHWbVe QduQ== X-Gm-Message-State: AKS2vOyvySxn9L5Y6SLWVLmZh/o4WuPJcpevAMyswXJhkESr8cOMSkb4 zKghz19vRJx//i+5UxyvI+BITgkZv6ZA X-Received: by 10.157.13.130 with SMTP id 2mr872404ots.51.1497374948411; Tue, 13 Jun 2017 10:29:08 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Shirley Wang Date: Tue, 13 Jun 2017 17:28:57 +0000 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Robert Eckhardt , Akshay Joshi Cc: pgadmin-hackers , Dave Page Content-Type: multipart/alternative; boundary="94eb2c04fb1ae55fba0551dac55f" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgadmin-hackers Precedence: bulk Sender: pgadmin-hackers-owner@postgresql.org --94eb2c04fb1ae55fba0551dac55f Content-Type: text/plain; charset="UTF-8" To add some interview context - from talking to DBAs, partitioning is the most frequent use case we should account for first. Breaking down child partitions into smaller pieces wasn't something we heard as a need. This is probably not the highest priority work needed now. We should think about when it would be good to release partitioning for some feedback. Did you already have this planned out? On Tue, Jun 13, 2017 at 9:46 AM Robert Eckhardt wrote: > Akshay, > > Have you determined the minimum feature set you are shooting for before > you commit this? The reason I ask is that we were thinking that some level > of simple automation would probably be nice to make this super useful. > > Basically if you consider a simple example of partitioning 90 days of data > by day the manual process of creating the names and to - from fields > becomes rather painful. If you couple that with potentially wanting to do > list subpartitioning if just multiplies the work. > > If we could get something committed then we could more easily work to > define where simple automation makes sense and where it doesn't. > > -- Rob > > On Tue, Jun 13, 2017 at 6:59 AM, Akshay Joshi < > akshay.joshi@enterprisedb.com> wrote: > >> Hi All >> >> For further implementation following task needs to be work upon: >> >> - How to parse and show partitions keys. For example user has created >> below partitioned table >> >> CREATE TABLE public.sales >> ( >> country character varying COLLATE pg_catalog."default" NOT NULL, >> sales bigint, >> saledate date >> ) PARTITION BY RANGE (*country, date_part('year'::text, sale date)*) >> >> When user open the properties dialog I am not able to figure out how to >> parse keys(displayed in bold in above example) and show them in our control >> that we used. For the time being I have hide that control in 'Edit' mode >> (Refer Attach Partition.png) >> >> >> - *Support of sub partitioning*: To implement sub-partitioning, >> specify the PARTITION BY clause in the commands used to create individual >> partitions, for example: >> - >> >> CREATE TABLE measurement_y2006 PARTITION OF measurement >> FOR VALUES FROM ('2006-02-01') TO ('2006-03-01') >> PARTITION BY RANGE (peaktemp); >> >> >> To achieve above I have made some changes in GUI (Refer Sub >> Partition.png). >> *Complex and challenging part here is "measurement_y2006" is >> partition of "measurement" and parent table for other partitions too which >> user can create later. How we will going to show this in browser tree? * >> One option could be >> Tables >> ->measurement(table) >> ->Partitions >> ->measurement_y2006(Partition of measurement and parent >> of p1) >> ->Partitions >> ->p1 >> >> - *Attach Partitions*: To implement attach N partitions I have made >> some changes in GUI( Refer Attach Partition.png). Attach Partitions >> control will only be visible in "Edit" mode. >> >> I have only modified the UI changes, there are lots of work needs to be >> done to complete that. >> Please review the design. Suggestions/Comments are welcome. >> >> >> On Tue, Jun 6, 2017 at 4:30 PM, Robert Eckhardt >> wrote: >> >>> >>> >>> On Tue, Jun 6, 2017 at 4:32 AM, Dave Page wrote: >>> >>>> >>>> For roll up this pattern seems obvious, identify the n partitions you >>>>> need/want to combine and then run a job to combine them. >>>>> >>>> >>>> You're thinking Greenplum :-). There is no roll up in PostgreSQL, >>>> unless you're thinking we should create such a feature in pgAdmin. >>>> >>>> Of course, I have no objection to extending what we do in PG to add GP >>>> feature support, but let's start with PG. >>>> >>> >>> No not at all. That was a very specific and consistent pattern described >>> by users leveraging time based range partitions in Postgres. I'm not sure >>> if that same use case will be supported with partitioning as implemented in >>> Postgres 10 but it is a Postgres pattern. >>> >>> -- Rob >>> >>> >>>> >>>> >>>>> >>>>> For other patterns such as creating indexes and such it requires a bit >>>>> more thought. Generally users described wanting to treat all of the >>>>> children like a single table (just like Oracle), however, other users >>>>> described potentially modifying chunks of partitions differently depending >>>>> on some criterion. This means that users will need to identify the subset >>>>> they want to optimize and then ideally be able to act on them all at once. >>>>> >>>> >>>> Right. >>>> >>>> >>>>> >>>>> -- Rob >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>>> >>>>>> So... it sounds like we're on the right lines :-) >>>>>> >>>>>> >>>>>>> >>>>>>> For the former, this can be addressed by enabling users to modify >>>>>>> one or more child partitions at the same time. For the latter, that is a >>>>>>> workflow that might be addressed outside of the create table with partition >>>>>>> workflow we're working on currently. >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> On Mon, Jun 5, 2017 at 5:21 AM Dave Page wrote: >>>>>>> >>>>>>>> On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi < >>>>>>>> akshay.joshi@enterprisedb.com> wrote: >>>>>>>> >>>>>>>>> Hi All >>>>>>>>> >>>>>>>>> Following are the further implementation updates to support >>>>>>>>> Declarative Partitioning: >>>>>>>>> >>>>>>>>> - Show all the existing partitions of the parent table in >>>>>>>>> Partitions tab (Refer Existing_Partitions.png) >>>>>>>>> - Ability to create N partitions and detach existing >>>>>>>>> partitions. Refer (Create_Detach_Partition.png), in this >>>>>>>>> example I have detach two existing partition and create two new partitions. >>>>>>>>> - Added "Detach Partition" menu to partitions node only and >>>>>>>>> user will be able to detach from there as well. Refer (Detach. >>>>>>>>> png) >>>>>>>>> >>>>>>>>> That's looking good to me :-) >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> On Wed, May 24, 2017 at 8:00 PM, Robert Eckhardt < >>>>>>>>> reckhardt@pivotal.io> wrote: >>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> On Wed, May 24, 2017 at 3:35 AM, Akshay Joshi < >>>>>>>>>> akshay.joshi@enterprisedb.com> wrote: >>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Taking average of two columns is just an >>>>>>>>>>> example/representation of expression, there is no use case of that. As I am >>>>>>>>>>> also in learning phase. Below are some use case that I can think of: >>>>>>>>>>> >>>>>>>>>>> - >>>>>>>>>>> >>>>>>>>>>> Partitions based on first letter of their username >>>>>>>>>>> >>>>>>>>>>> CREATE TABLE users ( >>>>>>>>>>> id serial not null, >>>>>>>>>>> username text not null, >>>>>>>>>>> password text, >>>>>>>>>>> created_on timestamptz not null, >>>>>>>>>>> last_logged_on timestamptz not null >>>>>>>>>>> )PARTITION BY RANGE ( lower( left( username, 1 ) ) ); >>>>>>>>>>> CREATE TABLE users_0 >>>>>>>>>>> partition of users (id, primary key (id), unique (username)) >>>>>>>>>>> for values from ('a') to ('g'); >>>>>>>>>>> CREATE TABLE users_1 >>>>>>>>>>> partition of users (id, primary key (id), unique (username)) >>>>>>>>>>> for values from ('g') to (unbounded); >>>>>>>>>>> >>>>>>>>>>> - Partition based on country's sale for each month of an >>>>>>>>>>> year. >>>>>>>>>>> >>>>>>>>>>> CREATE TABLE public.sales >>>>>>>>>>> >>>>>>>>>>> ( >>>>>>>>>>> >>>>>>>>>>> country text NOT NULL, >>>>>>>>>>> >>>>>>>>>>> sales bigint NOT NULL, >>>>>>>>>>> >>>>>>>>>>> saledate date >>>>>>>>>>> >>>>>>>>>>> ) PARTITION BY RANGE (country, (extract (YEAR FROM saledate)), >>>>>>>>>>> (extract(MONTH FROM saledate))) >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> CREATE TABLE public.sale_usa_2017_jan PARTITION OF sales >>>>>>>>>>> >>>>>>>>>>> FOR VALUES FROM ('usa', 2017, 01) TO ('usa', 2017, 02); >>>>>>>>>>> >>>>>>>>>>> CREATE TABLE public.sale_india_2017_jan PARTITION OF sales >>>>>>>>>>> >>>>>>>>>>> FOR VALUES FROM ('india', 2017, 01) TO ('india', 2017, 02); >>>>>>>>>>> >>>>>>>>>>> CREATE TABLE public.sale_uk_2017_jan PARTITION OF sales >>>>>>>>>>> >>>>>>>>>>> FOR VALUES FROM ('uk', 2017, 01) TO ('uk', 2017, 02); >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> INSERT INTO sales VALUES ('india', 10000, '2017-1-15'); >>>>>>>>>>> >>>>>>>>>>> INSERT INTO sales VALUES ('uk', 20000, '2017-1-08'); >>>>>>>>>>> >>>>>>>>>>> INSERT INTO sales VALUES ('usa', 30000, '2017-1-10'); >>>>>>>>>>> >>>>>>>>>>> Apart from above there may be N number of use cases that >>>>>>>>>>> depends on specific requirement of user. >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Thank you for the example, you are absolutely correct and we were >>>>>>>>>> confused. >>>>>>>>>> >>>>>>>>>> Given our new found understanding do you mind if we iterate a bit >>>>>>>>>> on the UI/UX? What we were suggesting with the daily/monthly/yearly drop >>>>>>>>>> down was a specific example of an expression. Given that fact that doesn't >>>>>>>>>> seem to be required in an MVP, however, I do think a more interactive >>>>>>>>>> experience between the definition of the child partitions and the creation >>>>>>>>>> of the partitions would be optimal. >>>>>>>>>> >>>>>>>>>> I'm not sure where you are with respect to implementing the UI >>>>>>>>>> but I'd love to float some ideas and mock ups past you. >>>>>>>>>> >>>>>>>>>> -- Rob >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> -- >>>>>>>>> *Akshay Joshi* >>>>>>>>> *Principal Software Engineer * >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91 >>>>>>>>> 976-788-8246 <+91%2097678%2088246>* >>>>>>>>> >>>>>>>>> >>>>>>>>> -- >>>>>>>>> Sent via pgadmin-hackers mailing list ( >>>>>>>>> pgadmin-hackers@postgresql.org) >>>>>>>>> To make changes to your subscription: >>>>>>>>> http://www.postgresql.org/mailpref/pgadmin-hackers >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> Dave Page >>>>>>>> >>>>>>>> Blog: http://pgsnake.blogspot.com >>>>>>>> Twitter: @pgsnake >>>>>>>> >>>>>>>> EnterpriseDB UK: http://www.enterprisedb.com >>>>>>>> The Enterprise PostgreSQL Company >>>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Dave Page >>>>>> Blog: http://pgsnake.blogspot.com >>>>>> Twitter: @pgsnake >>>>>> >>>>>> EnterpriseDB UK: http://www.enterprisedb.com >>>>>> The Enterprise PostgreSQL Company >>>>>> >>>>> >>>>> >>>> >>>> >>>> -- >>>> Dave Page >>>> Blog: http://pgsnake.blogspot.com >>>> Twitter: @pgsnake >>>> >>>> EnterpriseDB UK: http://www.enterprisedb.com >>>> The Enterprise PostgreSQL Company >>>> >>> >>> >> >> >> -- >> *Akshay Joshi* >> *Principal Software Engineer * >> >> >> >> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91 976-788-8246 >> <+91%2097678%2088246>* >> > > --94eb2c04fb1ae55fba0551dac55f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
To add some interview context - from talking to DBAs, part= itioning is the most frequent use case we should account for first. Breakin= g down child partitions into smaller pieces wasn't something we heard a= s a need. This is probably not the highest priority work needed now.
We should think about when it would be good to release partiti= oning for some feedback. Did you already have this planned out?

On Tue, Jun 13, 2017 at 9:46 AM Ro= bert Eckhardt <reckhardt@pivotal= .io> wrote:
Akshay,

Have you determined the minimum feature set you= are shooting for before you commit this? The reason I ask is that we were = thinking that some level of simple automation would probably be nice to mak= e this super useful.=C2=A0

Basically if you co= nsider a simple example of partitioning 90 days of data by day the manual p= rocess of creating the names and to - from fields becomes rather painful. I= f you couple that with potentially wanting to do list subpartitioning if ju= st multiplies the work.=C2=A0

If we could get some= thing committed then we could more easily work to define where simple autom= ation makes sense and where it doesn't.=C2=A0

= -- Rob

On Tue, Jun 13, 2017 at 6:59 AM, Akshay Joshi <akshay.joshi@e= nterprisedb.com> wrote:
Hi All=C2=A0

For further implementation fo= llowing task needs to be work upon:
  • How to parse and show= partitions keys. For example user has created below partitioned table=C2= =A0
CREATE TABLE public.sales
(
=C2=A0 = =C2=A0 country character varying COLLATE pg_catalog."default" NOT= NULL,
=C2=A0 =C2=A0 sales bigint,
=C2=A0 =C2=A0 saledate date
) PARTITION BY RANGE (country, date_part('year'::text, s= ale date))

When user open the properties dialog = I am not able to figure out how to parse keys(displayed in bold in above ex= ample) and show them in our control that we used. For the time being I have= hide that control in 'Edit' mode (Refer Attach Partition.png)
  • Support of sub partitioning:=C2=A0To implement sub-= partitioning, specify the PARTITION BY clause in the commands used to creat= e individual partitions, for example:
  • CRE=
ATE TABLE measurement_y2006 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
    PARTITION BY RANGE (peaktemp);
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0To achieve above I have made some changes in GUI (Refer Sub Pa= rtition.png<= /span>).=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Comple= x and challenging part here is "measurement_y2006" is partition o= f "measurement" and parent table for other partitions too which u= ser can create later. How we will going to show this in browser tree?=C2=A0=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0One option c= ould be
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Tables
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->measurement(table)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->Partiti= ons
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0->measurement_y2006(Partition of measurement and parent of p1)=C2=A0<= /div>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0->Partitions
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->p1=C2=A0
  • Attach= Partitions: To implement attach N partitions I have made some changes = in GUI( Refer=C2=A0Attach Partition.png). Attach Partitions control will only be= visible in "Edit" mode.
I have only modified the <= span id=3D"m_1184815686352672070m_5716494732641866491:3dt.35">UI cha= nges, there are lots of work needs to be done to complete that. =C2=A0Please review the design. Suggestions/Comments are welcome.
=C2=A0=C2=A0

On Tue, Jun 6, 2017 at 4:30 PM, Ro= bert Eckhard= t <reckhardt@pivotal.io> wrote:


On Tue, Jun 6, 2017 at 4:32 AM, Dave Page <dpage@pgadmin.org> wrote:

For rol= l up this pattern seems obvious, identify the n partitions you need/want to= combine and then run a job to combine them.=C2=A0
<= /blockquote>

You're thinking Greenplum :-). T= here is no roll up in PostgreSQL, unless you're thinking we should crea= te such a feature in pgAdmin.

Of course, I have no= objection to extending what we do in PG to add GP feature support, but let= 's start with PG.

No not at all. That was a very specific and consistent pattern de= scribed by users leveraging time based range partitions in Postgres. I'= m not sure if that same use case will be supported with partitioning as imp= lemented in Postgres 10 but it is a Postgres pattern.=C2=A0

<= /div>
-- Rob
=C2=A0
=C2=A0
<= div class=3D"gmail_extra">

Fo= r other patterns such as creating indexes and such it requires a bit more t= hought. Generally users described wanting to treat all of the children like= a single table (just like Oracle), however, other users described potentia= lly modifying chunks of partitions differently depending on some criterion.= This means that users will need to identify the subset they want to optimi= ze and then ideally be able to act on them all at once.=C2=A0

Right.
=C2=A0

-- Rob




=C2=A0

So... it sounds like we're on the right lines :-)
=C2=A0

For the former, this can be addressed by en= abling users to modify one or more child partitions at the same time. For t= he latter, that is a workflow that might be addressed outside of the create= table with partition workflow we're working on currently.=C2=A0
<= div>




On Mon, Jun 5, 2017 at 5:21 AM Dave Page <dpage@pgadmin.org>= ; wrote:
On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi= <akshay.joshi@enterprisedb.com> wrote:
Hi All=C2=A0

Following are = the further implementation updates to support Declarative Partitioning:
  • Show all the existing partitions of the parent table in Part= itions tab (Refer Existing_Partitions.png)
  • Ability to create N partitions and= detach existing partitions. Refer (Create_Detach_Partition.png), in this example I h= ave detach two existing partition and create two new partitions.
  • Ad= ded "Detach Partition" menu to partitions node only and user will= be able to detach from there as well. Refer (Detach.png) =C2=A0=C2=A0
That's looking good to me :-)

=C2=A0


On Wed, May 24, 2017 at 8:00 PM, Robert Eckhardt = <reckhardt@piv= otal.io> wrote:


On Wed, May 24, 2017 at 3:35 AM, Ak= shay Joshi <akshay.joshi@enterprisedb.com> wrote= :

=C2=A0 =C2=A0Taking aver= age of two columns is just an example/representation of expression, there i= s no use case of that. As I am also in learning phase. Below are some use c= ase that I can think of:
  • =
Partitions based on first letter of their username
    CREATE TABL=
E users (
        id             serial not null,
    =
    username       text not null=
,
        password=
       text,
        created_on     timestamptz not null<=
/span>,
        last_logged_on timestamptz not null
    <=
/span>    )PARTITION BY RANGE (<=
/span> lower( left=
( username, 1 ) ) );
    CREATE TABLE users_0
        partition =
of users (id, primary key (id), uniqu=
e (username))
        for values<=
/span> from ('a') to ('g&#=
39;);
    CREATE TABLE users_1
        partition of users (id, primary key (id<=
span style=3D"color:rgb(102,204,102)">), un=
ique (username))
        for val=
ues from ('g') =
to (unbounded);
  • =C2=A0Partition based = on country's sale for each month of an year.
CREATE TABLE public.sales=
(
=C2=A0 =C2=A0 country text NOT NULL,
=C2=A0 = =C2=A0 sales bigint NOT NULL,
<= /div>
=C2= =A0 =C2=A0 saledate date
=
) PARTIT= ION BY RANGE (country, (extract (YEAR FROM saleda= te)), (extract(MONTH FROM saledate)= ))

CREATE TABLE public.sale_usa_2017_jan PARTITION OF sales
=C2=A0 =C2=A0 FOR VALUES FROM ('usa', 2017, 01) TO ('us= a', 2017, 02);
CREATE TABLE public.sale_india_2017_jan PARTITION OF sal= es
=C2=A0 =C2=A0 FOR VALUES FROM ('india', 2017, 01) TO (&= #39;india', 2017, 02);
CREATE TABLE public.sale_uk_2017_jan PARTITION = OF sales
=C2=A0 =C2=A0 FOR VALUES FROM ('uk', 2017, 01) TO= ('uk', 2017, 02);

INSERT INTO sales VALUES= ('india', 10000, '2017-1-15');
INSERT INTO sales = VALUES ('uk', 20000, '2017-1-08');
INSERT INTO sal= es VALUES ('usa', 30000, '2017-1-10');

=
=C2=A0 =C2=A0Apart from above there m= ay be N number of use cases that depends on specific requirement of user.= =C2=A0

Thank you for the = example, you are absolutely correct and we were confused.=C2=A0
<= br>
Given our new found understanding do you mind if we iterate a= bit on the UI/UX?=C2=A0 What we were suggesting with the daily/monthly/yea= rly drop down was a specific example of an expression. Given that fact that= doesn't seem to be required in an MVP, however, I do think a more inte= ractive experience between the definition of the child partitions and the c= reation of the partitions would be optimal.=C2=A0

= I'm not sure where you are with respect to implementing the UI but I= 9;d love to float some ideas and mock ups past you.=C2=A0

-- Rob



-= -
=
Akshay Joshi
Principal Software Engineer=C2=A0

<= img src=3D"http://www.enterprisedb.com/sites/default/files/EDB-logo-4c.png"= width=3D"96" height=3D"54">



--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers=




--
Dave Page
Enterpris= eDB UK: http://ww= w.enterprisedb.com
The Enterprise PostgreSQL Company



--
Dave Pag= e
Blog: http:/= /pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprise= db.com
The Enterprise PostgreSQL Company




--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB= UK: http://www.e= nterprisedb.com
The Enterprise PostgreSQL Company




--
Akshay Joshi
Principal Software Enginee= r=C2=A0

=

+91 20-3058-9517
Mobile: +91 976-788-8246

--94eb2c04fb1ae55fba0551dac55f--