Hi I wonder if someone can help me I am getting a bit confused about an error I am getting.
I have a partitioned table called sensor_values which is partitioned on a timestamp entry.
The parent and some of the child tables are defined like so
(The child tables are yearly quarters and in my actual code they span 15 years)
CREATE TABLE sensor_values (
id SERIAL PRIMARY KEY,
timestamp timestamp with time zone NOT NULL,
value real NOT NULL DEFAULT 'NaN',
sensor_id integer NOT NULL,
FOREIGN KEY (sensor_id) REFERENCES sensors(id)
);
CREATE TABLE sensor_values_2011q2 (CHECK ( timestamp >= TIMESTAMP WITH TIME ZONE '2011-04-01 00:00:00.000000+00:00' AND timestamp < TIMESTAMP WITH TIME ZONE '2011-07-01 00:00:00.000000+00:00' )) INHERITS (sensor_values);
CREATE TABLE sensor_values_2011q3 (CHECK ( timestamp >= TIMESTAMP WITH TIME ZONE '2011-07-01 00:00:00.000000+00:00' AND timestamp < TIMESTAMP WITH TIME ZONE '2011-10-01 00:00:00.000000+00:00' )) INHERITS (sensor_values);
I have a trigger to determine which table the insert will occur on.
Ie
IF ( NEW.timestamp >= TIMESTAMP WITH TIME ZONE '2011-04-01 00:00:00.000000+00:00' AND NEW.timestamp < TIMESTAMP WITH TIME ZONE '2011-07-01 00:00:00.000000+00:00' )
THEN INSERT INTO sensor_values_2011q2 VALUES (NEW.*);
ELSIF ( NEW.timestamp >= TIMESTAMP WITH TIME ZONE '2011-07-01 00:00:00.000000+00:00' AND NEW.timestamp < TIMESTAMP WITH TIME ZONE '2011-10-01 00:00:00.000000+00:00' )
THEN INSERT INTO sensor_values_2011q3 VALUES (NEW.*);
The trouble is I have a python script that inserts some values and I am getting the following error on one timestamp
The error I get is
new row for relation "sensor_values_2011q3" violates check constraint "sensor_values_2011q3_timestamp_check"<br />CONTEXT: SQL statement "INSERT INTO sensor_values_2011q3 VALUES (NEW.*)"<br />PL/pgSQL function "sensor_values_timestamp_sensor_func_insert_trigger" line 32 at SQL statement<br /><br />
I have printed the query that causes this error and it is
INSERT INTO sensor_values (timestamp, value, sensor_id) VALUES ('2011-06-30 23:00:00.001000+00:00', '0', '2103')
So the trigger has chosen the wrong child table to insert into ?
The funny thing is from psql this insert query works fine. ?
I am not sure why 2011-06-30 23:00:00.001000+00:00 is selected to go into sensor_values_2011q3 and not sensor_values_2011q2
I suspect its due to UTC / BST as that date time is on the border of the contraint. Either way I not sure why I get an error and
why does PSQL work ?
Any suggestions / help would be great
Thanks
