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.kla...@aklaver.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.kla...@aklaver.com >
