Re: is it possible to create partitioned tables using tables from different schemas

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
>