Combine Partitioning & Foreign Data Mapping
- Let's create physical partition table on box2
-- on box2 server
CREATE TABLE temperatures_2016 (
at date,
city text,
mintemp integer,
maxtemp integer
);
- And then create the partition on your server, as a foreign table:
-- default OR your server
CREATE FOREIGN TABLE temperatures_2016
PARTITION OF temperatures
FOR VALUES FROM ('2016-01-01') TO ('2017-01-01')
SERVER box2; -- This creates foreign table mapping
- You can now insert and query from your own server:
temp=# INSERT INTO temperatures (at, city, mintemp, maxtemp)
temp-# VALUES ('2016-08-03', 'London', 63, 73);
INSERT 0 1
temp=# SELECT * FROM temperatures ORDER BY at;
at | city | mintemp | maxtemp
------------+--------+---------+---------
2016-08-03 | London | 63 | 73
2017-08-03 | London | 59 | 70
2018-08-03 | London | 63 | 90
(3 rows)
temp=# SELECT * FROM temperatures_2016;
at | city | mintemp | maxtemp
------------+--------+---------+---------
2016-08-03 | London | 63 | 73
(1 row)