Re: Declarative partitioning in pgAdmin4

On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi <akshay(dot)joshi(at)enterprisedb(dot)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(at)pivotal(dot)io>
> wrote:
>
>>
>>
>> On Wed, May 24, 2017 at 3:35 AM, Akshay Joshi <
>> akshay(dot)joshi(at)enterprisedb(dot)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(at)postgresql(dot)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