partition_data processes not handling date data type properly

[keithf4]

I have a similar issue when converting from a non partition to a partition table.

CREATE TABLE test (
arch_date date NOT NULL,
alert_key int4 NOT NULL
);

INSERT INTO test (arch_date,alert_key) VALUES
('2024-06-01',40092),
('2024-06-01',70018),
('2024-07-01',70043);

DBD::Pg::db do failed: ERROR: new row for relation "test_p20240601" violates partition constraint
DETAIL: Failing row contains (2024-07-01, 70043.
CONTEXT: SQL statement "WITH partition_data AS (
DELETE FROM ONLY test WHERE arch_date::text >= '2024-06-01 00:00:00+02' AND arch_date::text < '2024-07-01 00:00:00+02' RETURNING *)
INSERT INTO ....

       2024-07-01 -> seems to be smaller then < '2024-07-01 00:00:00+02' 

Originally posted by @clausgeert in #838

[clausgeert]

@keithf4
Small question : Will this be solved with the patch of April ?

[clausgeert]

@keithf4 Will this be solved with the patch of April ?

[achix]

Hi I can verify I run into the same bug, or so I think :

FreeBSD 15, pgsql 18.3, partman from source 5.4.3

dynacom=# \d tagsdata.tagsrawsentence_default
Table "tagsdata.tagsrawsentence_default"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
utcts | timestamp without time zone | | not null |
tagid | integer | | not null |
value | text | | not null |
Partition of: tagsdata.tagsrawsentence DEFAULT
Indexes:
"tagsrawsentence_default_pkey" PRIMARY KEY, btree (utcts, tagid)
"tagsrawsentence_default_tagid" btree (tagid)
"tagsrawsentence_default_utcts" btree (utcts)
Tablespace: "ssd"

ERROR: new row for relation "tagsrawsentence_p20260418" violates partition constraint
DETAIL: Failing row contains (2026-04-19 00:00:00.349, 71683, 70.0,T).
CONTEXT: SQL statement "WITH partition_data AS (
DELETE FROM partman_temp_data_storage RETURNING *)
INSERT INTO tagsdata.tagsrawsentence_p20260418 (utcts,tagid,value) SELECT utcts,tagid,value FROM partition_data"
PL/pgSQL function partition_data_time(text,integer,interval,numeric,text,boolean,text,text[],boolean,boolean) line 335 at EXECUTE
SQL statement "SELECT partman.partition_data_time (p_parent_table := 'tagsdata.tagsrawsentence'
, p_lock_wait := '0'
, p_order := 'ASC'
, p_analyze := false, p_ignore_infinity := 'f')"
PL/pgSQL function partman.partition_data_proc(text,integer,text,integer,integer,integer,text,text,text[],boolean,boolean) line 92 at EXECUTE

The way I solved it :
with foo as (delete from tagsdata.tagsrawsentence_default where utcts = '2026-04-19 00:00:00.349' and tagid = 71683 returning *)
select * into tagsrawsentence_damage from foo;
SELECT 1

with foo as (delete from tagsdata.tagsrawsentence_default where utcts = '2026-04-19 00:00:00.349' returning *)
insert into tagsrawsentence_damage select * from foo;
INSERT 0 8

Then partman.partition_data_proc('tagsdata.tagsrawsentence') went smooth , then finally :
insert into tagsdata.tagsrawsentence select * from tagsrawsentence_damage ;

I must also note that those 9 failing rows, were the only ones among the whole dataset ( 17,546,132 rows ) that were just 349 milliseconds ahead of the start of the day .