Re: is it possible to create partitioned tables using tables fromdifferent schemas
От | Adrian Klaver |
---|---|
Тема | Re: is it possible to create partitioned tables using tables fromdifferent schemas |
Дата | |
Msg-id | c6682c21-46d2-421d-7250-6ded30d729d3@aklaver.com обсуждение исходный текст |
Ответ на | is it possible to create partitioned tables using tables fromdifferent schemas (pabloa98 <pabloa98@gmail.com>) |
Ответы |
Re: is it possible to create partitioned tables using tables fromdifferent schemas
(pabloa98 <pabloa98@gmail.com>)
Re: is it possible to create partitioned tables using tables fromdifferent schemas (David Rowley <david.rowley@2ndquadrant.com>) |
Список | pgsql-general |
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.klaver@aklaver.com
В списке pgsql-general по дате отправления:
Предыдущее
От: "David G. Johnston"Дата:
Сообщение: Re: Method to pass data between queries in a multi-statement transaction
Следующее
От: Adrian KlaverДата:
Сообщение: Re: Method to pass data between queries in a multi-statementtransaction