Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dDXJB-0005MB-20 for pgadmin-hackers@arkaria.postgresql.org; Wed, 24 May 2017 14:30:41 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dDXJA-0000tu-Kn for pgadmin-hackers@arkaria.postgresql.org; Wed, 24 May 2017 14:30:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dDXIu-0007Yp-Pf for pgadmin-hackers@postgresql.org; Wed, 24 May 2017 14:30:24 +0000 Received: from mail-vk0-x22f.google.com ([2607:f8b0:400c:c05::22f]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dDXIp-0001yJ-TR for pgadmin-hackers@postgresql.org; Wed, 24 May 2017 14:30:24 +0000 Received: by mail-vk0-x22f.google.com with SMTP id y190so76469283vkc.1 for ; Wed, 24 May 2017 07:30:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pivotal-io.20150623.gappssmtp.com; s=20150623; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=G5EOvtNJJEtgUut130j1lTNjnMB6/9KBeMHidvzCJog=; b=H9VIHpbOAfTh6KJ7iPB+d+DnHkoPO0eaqotw9o6GokIo15vmt3oPJH2YYjZffSxEbn HG2MkUmXY8Rh1MFJ2abeELoUakLnh9xK3pgkXreEz0u2/FaJ2lqBrXYFoim/SUk80Xkd 8Ai4oP0gmlhw5y4h1cW9mwDRy8TTTUTc3idXeMt26oSh0FUxUVV54ISZRm8eR3Ygp+We qOzMsJfT5q+I3MYpTrAKK6AlRi4SKRuAvMMAz0wpd1yFuXC9jTxM7Sd+x4zRxtQksHxW szwEJ2r6XAfw4jwl+nfFwv4nECOe2nFQrmuMeJkuX+spCGjo2wmr6RtLpks5wDdrhUR4 yjiQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=G5EOvtNJJEtgUut130j1lTNjnMB6/9KBeMHidvzCJog=; b=SI/W1wXf0uy4zKv2rK1d1kDdobPb4dqkKYrcpFG8EunydWCkoJ1wDnN1hgtJdWcRo3 shVVavx/Rf8S6eCfsOBRGcyNmyNP7XV/g1WLglawfGTWIGFsIF6RvXGWcSoSXsU/4iIk PtmndeXtiSW8fcGxdqngpWChrLbI0Q1psWe5BGmd2QdSgiLRA9HD2YNutegMi8r2LSaR 7ZQQrF9uEbNFLIII8BWD1pAP/nIryScSKgx5ES5LU/2Ruzr1GyMIc2TZnvX9kQT0ngFU g+wTLdw96BXKnjsQhdFKpKjfHyhgYbzbuxHhJxcm83BlRBhv3JcLX4mLFfQQznxuNhWO V9BQ== X-Gm-Message-State: AODbwcAehQQphwlB3oIbwQNG5L0NfrJMUU6SR5qwMQ1n4EeNnGzXbzQJ dw3xQtxGc2PrrHwZyGjCNcTpaROhP6+Y X-Received: by 10.31.106.5 with SMTP id f5mr14793197vkc.69.1495636217376; Wed, 24 May 2017 07:30:17 -0700 (PDT) MIME-Version: 1.0 Received: by 10.176.0.243 with HTTP; Wed, 24 May 2017 07:30:16 -0700 (PDT) In-Reply-To: References: From: Robert Eckhardt Date: Wed, 24 May 2017 10:30:16 -0400 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Akshay Joshi Cc: Shirley Wang , pgadmin-hackers Content-Type: multipart/alternative; boundary="94eb2c093de4734a2f055045f110" X-Pg-Spam-Score: -2.6 (--) 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 --94eb2c093de4734a2f055045f110 Content-Type: text/plain; charset="UTF-8" On Wed, May 24, 2017 at 3:35 AM, Akshay Joshi 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 --94eb2c093de4734a2f055045f110 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Wed, May 24, 2017 at 3:35 AM, Akshay Joshi <akshay.josh= i@enterprisedb.com> wrote:
=

=C2=A0 =C2=A0Taking average of two = columns is just an example/representation of expression, there is no use ca= se 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 nul=
l,
        password       text,
        created_on     times=
tamptz 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,<=
/span> primary=
 key (id), unique (username=
))
        for =
values from ('g=
9;) to (unbounded);=
  • =C2=A0Partition based on country's s= ale for each month of an year.
CREATE TABLE public.sales
(
=C2=A0 =C2=A0 count= ry text NOT NULL,
=C2=A0 =C2=A0 sales bigint NOT NULL,
=C2=A0 =C2=A0 saledate date
) PARTITION BY RANGE (country, (ex= tract (YEAR FROM saledate)= ), (extract(MONTH FROM saledate)))

CREATE TABLE public.sale_usa_2017_jan= PARTITION OF sales
<= div class=3D"gmail_quote">
=C2=A0 =C2=A0 FOR VALUES FROM ('usa'= , 2017, 01) TO ('usa', 2017, 02);
CREATE TABLE public.sale_india_2017_jan PARTITION OF sales
=C2=A0 =C2=A0 FOR VA= LUES FROM ('india', 2017, 01) TO ('india', 2017, 02);
=
CR= EATE TABLE public.sale_uk_= 2017_jan PARTITION OF sale= s
=
=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, = 9;2017-1-08');
INSERT INTO sales VALUES ('usa', 30000, '= 2017-1-10');

= =C2=A0 =C2=A0Apart from above there may be N number of use cases that depen= ds on specific requirement of user.=C2=A0

=
Thank you for the example, you are absolutely correct and we were conf= used.=C2=A0

Given our new found understanding do y= ou mind if we iterate a bit on the UI/UX?=C2=A0 What we were suggesting wit= h the daily/monthly/yearly drop down was a specific example of an expressio= n. 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 chi= ld partitions and the creation of the partitions would be optimal.=C2=A0

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

-- Rob
--94eb2c093de4734a2f055045f110--