From: | pabloa98 <pabloa98(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: is it possible to create partitioned tables using tables from different schemas |
Date: | 2019-04-17 22:49:27 |
Message-ID: | CAEjudX4eVhkyqCSL-QLGJd+2J2qjhYZXsMnxMNeoYDNGXO2hsQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thank you! This is exactly was I was looking for.
The range thing is good enough for me.
Pablo
On Wed, Apr 17, 2019 at 3:19 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
> On 4/17/19 2:21 PM, pabloa98 wrote:
> > I have a schema with a generated table with information coming from
> > batch processes.
> >
> > I would like to store in that table manually generated information.
> > Since those rows are inserted by hand, they will be lost when the table
> > will be reimported.
> >
> > So I was thinking of creating a partitioned table with a column "origin"
> > to select if the data is batch inserted or inserted by hand. Current
> > generated by batch sub-table will live in its schema.
> >
> > I want the other sub-table containing manually inserted information
> > living in another schema.
> >
> > Is this possible? Do I have to do something else (create some trigger,
> > or something)?
> >
> > Something like:
> >
> > CREATE TABLE*automatic.*measurement (
> > city_id int not null,
> > logdate date not null,
> > peaktemp int,
> > unitsales int
> > origin int
> > ) PARTITION BY RANGE (origin);
> >
> >
> > CREATE TABLE*automatic.*measurement_automatic PARTITION
> OF*automatic.*measurement
> > FOR VALUES FROM (1) TO (1)
> > PARTITION BY RANGE (origin);
> >
> > CREATE TABLE*manual.*measurement_manual PARTITION
> OF*automatic.*measurement
> > FOR VALUES FROM (2) TO (2)
> > PARTITION BY RANGE (origin);
> >
> >
>
> It would seem so(with a caveat):
>
> CREATE TABLE automatic.measurement (
> city_id int not null,
>
>
>
> logdate date not null,
>
>
>
> peaktemp int,
> unitsales int,
> origin int
> ) PARTITION BY RANGE (origin);
> CREATE TABLE
>
> CREATE TABLE automatic.measurement_automatic PARTITION OF
> automatic.measurement
> test-# FOR VALUES FROM (1) TO (1)
> test-# PARTITION BY RANGE (origin);
> ERROR: empty range bound specified for partition "measurement_automatic"
> DETAIL: Specified lower bound (1) is greater than or equal to upper
> bound (1).
>
> OOPS, so lets cheat:
>
> <NOTE> Removed PARTITION BY RANGE (origin) as it is redundant and causes
> problems later.
>
> CREATE TABLE automatic.measurement_automatic PARTITION OF
> automatic.measurement
> FOR VALUES FROM (1) TO (2)
> CREATE TABLE
>
> CREATE TABLE manual.measurement_manual PARTITION OF automatic.measurement
> FOR VALUES FROM (3) TO (4)
> CREATE TABLE
>
> The above assumes that manual origin will be in (1,2) and automatic in
> (3,4)
>
> insert into automatic.measurement values(1, '04/16/19', 25, 100, 1);
> INSERT 0 1
>
> insert into automatic.measurement values(1, '04/16/19', 25, 100, 3);
> INSERT 0 1
>
> select * from automatic.measurement_automatic ;
> city_id | logdate | peaktemp | unitsales | origin
> ---------+------------+----------+-----------+--------
> 1 | 2019-04-16 | 25 | 100 | 1
> (1 row)
>
> select * from manual.measurement_manual;
> city_id | logdate | peaktemp | unitsales | origin
> ---------+------------+----------+-----------+--------
> 1 | 2019-04-16 | 25 | 100 | 3
> (1 row)
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2019-04-17 22:56:48 | Re: is it possible to create partitioned tables using tables from different schemas |
Previous Message | Adrian Klaver | 2019-04-17 22:20:30 | Re: Method to pass data between queries in a multi-statement transaction |