The following bug has been logged on the website:
Bug reference: 17525
Logged by: Allen Sutton
Email address: allen.sutton@valocityglobal.com
PostgreSQL version: 14.4
Operating system: Ubuntu 22.04LTS
Description:
I have a table that I have partitioned by a field est_date (date).
CREATE TABLE IF NOT EXISTS avm.avm_history_part
(
model_type character varying(4) COLLATE pg_catalog."default",
model_id integer,
est_date date NOT NULL,
address_id integer,
est integer,
conf numeric(8,4),
comps_used bigint,
last smallint DEFAULT 0,
est_id integer NOT NULL DEFAULT
nextval('avm.avm_history_part_est_id_seq1'::regclass),
est_smoothed integer,
dipid character varying(20) COLLATE pg_catalog."default",
fsd numeric(4,1)
) PARTITION BY RANGE (est_date);
The partitions of this table are created with a range of a month.
e.g. CREATE TABLE avm.avm_history_201704 PARTITION OF avm.avm_history_part
FOR VALUES FROM ('2017-04-01') TO ('2017-04-30');
This seems to be causing issues in two ways: -
1. When I prepare a table and then add it as a partition with a date range
and est_date in rows equal to the upper bound of the range, Postgres gives
me an error that it can't find a partition for the date
2. When I try update a row and set the value of est_date to the upper range,
I get an update error such as
ERROR: new row for relation "avm_history_201704" violates partition
constraint
DETAIL: Failing row contains (A, 11, 2017-04-30, 46846, 1270000, 0.2647, 7,
0, 26563523, 1246000, NZ82047105, 39.9).
SQL state: 23514
I need assistance to work out what I have done wrong if anything and how to
resolve this issue
