Search Postgresql Archives

Partitioning rule not behaving as expected

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I created a master table and three partition tables as per Postgres
documentation. I also created three rules that match the check
constraints. When I insert the values into the master table, the rules
seem to be functional since the data is routed to the appropriate
partition tables. However, data is also inserted into the master table
which is not what I want. Is there a reason DO INSTEAD would not be
evaluated as such?

Here's the setup:

CREATE TABLE zone_data
(
  zone_id int4 NOT NULL,
  measurement_start timestamptz NOT NULL
)
WITHOUT OIDS TABLESPACE tss_tbs;

CREATE TABLE zone_data_01_01
(
   CONSTRAINT zone_data_01_01_measurement_start_check CHECK
(measurement_start <= '2001-01-31 23:59:59-05'::timestamp with time
zone)
) INHERITS (zone_data);

CREATE TABLE zone_data_02_01
(
    CONSTRAINT zone_data_02_01_measurement_start_check CHECK
(measurement_start >= '2001-02-01 00:00:00-05'::timestamp with time
zone AND measurement_start <= '2001-02-28 23:59:59-05'::timestamp with
time zone)
) INHERITS (zone_data);

CREATE TABLE zone_data_03_01
(
    CONSTRAINT zone_data_03_01_measurement_start_check CHECK
(measurement_start >= '2001-03-01 00:00:00-05'::timestamp with time
zone AND measurement_start <= '2001-03-31 23:59:59-05'::timestamp with
time zone)
) INHERITS (zone_data);

CREATE OR REPLACE RULE zone_data_01_01_insert AS
    ON INSERT TO zone_data
   WHERE new.measurement_start <= '2001-01-31 23:59:59-05'::timestamp
with time zone DO INSTEAD  INSERT INTO zone_data_01_01 (zone_id,
measurement_start)
  VALUES (new.zone_id, new.measurement_start);

CREATE OR REPLACE RULE zone_data_02_01_insert AS
    ON INSERT TO zone_data
   WHERE new.measurement_start >= '2001-02-01 00:00:00-05'::timestamp
with time zone AND new.measurement_start <= '2001-02-28
23:59:59-05'::timestamp with time zone DO INSTEAD  INSERT INTO
zone_data_02_01 (zone_id, measurement_start)
  VALUES (new.zone_id, new.measurement_start);

CREATE OR REPLACE RULE zone_data_03_01_insert AS
    ON INSERT TO zone_data
   WHERE new.measurement_start >= '2001-03-01 00:00:00-05'::timestamp
with time zone AND new.measurement_start <= '2001-03-31
23:59:59-05'::timestamp with time zone DO INSTEAD  INSERT INTO
zone_data_03_01 (zone_id, measurement_start)
  VALUES (new.zone_id, new.measurement_start);

Here is the result:

INSERT INTO zone_data (zone_id, measurement_start)
VALUES (81, '2001-01-13 00:00:09-04');

INSERT INTO zone_data (zone_id, measurement_start)
VALUES (81, '2001-02-13 00:00:09-04');

INSERT INTO zone_data (zone_id, measurement_start)
VALUES (81, '2001-03-13 00:00:09-04');

Query returned successfully: 0 rows affected, 32 ms execution time.

SELECT * FROM zone_data;

zone_id      measurement_start
--------     ------------------
81           2001-01-13 00:00:09-04
81           2001-02-13 00:00:09-04
81           2001-03-13 00:00:09-04


SELECT * FROM zone_data_01_01;

zone_id      measurement_start
--------     ------------------
81           2001-01-13 00:00:09-04


SELECT * FROM zone_data_02_01;

zone_id      measurement_start
--------     ------------------
81           2001-02-13 00:00:09-04


SELECT * FROM zone_data_03_01;

zone_id     measurement_start
--------     ------------------
81            2001-03-13 00:00:09-04



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux