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

From: pabloa98 <pabloa98(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: is it possible to create partitioned tables using tables from different schemas
Date: 2019-04-17 21:21:03
Message-ID: CAEjudX4JPVFmiJG3BqWb4+P=XyaapOBNJyA4pj=eimN8AKU0gQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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);

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Souvik Bhattacherjee 2019-04-17 22:04:01 Method to pass data between queries in a multi-statement transaction
Previous Message Bruce Momjian 2019-04-17 19:43:48 Re: Forcing index usage