From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: allen.sutton@valocityglobal.com
Subject: BUG #17525: Range partition by date won't allow the use of a date that is the upper bound
Date: Tue, 21 Jun 2022 23:11:53 +0000
Message-ID: <17525-ffa3a5fe34f8c6f4@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, allen.sutton@valocityglobal.com
Subject: Re: BUG #17525: Range partition by date won't allow the use of a date that is the upper bound
In-Reply-To: <17525-ffa3a5fe34f8c6f4@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox