Re: Declarative partitioning in pgAdmin4

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