From: maitredede (@maitredede) <noreply+maitredede@github.com>
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
Subject: [pgpartman/pg_partman] issue #868: partition_data_proc(): `date` column with partition by "month" put 1st day data in wrong partition, new row for relation ... violates partition constraint
Date: Tue, 09 Jun 2026 03:58:07 +0000
Message-ID: <gh-pgpartman-pg_partman-868@github.com> (raw)
Hello,
Tested with postgresql 18 (docker debian), partman 5.4.3 (apt package) :
Partition on column type `date` fails when value is first day of month.
A workaround would be to precreate partitions before inserting data.
Here is an example script :
```sql
-- ============================================================================
-- pg_partman partition_data_proc — off-by-one on month boundaries
--
-- Self-contained reproduction. Uses a generic `events` table only (no external
-- data). Run against a database where the `pg_partman` extension is installed:
--
-- psql "<dsn>" -f repro.sql
--
-- ON_ERROR_STOP is OFF on purpose: we want the script to keep going after the
-- bug raises its error, so the WORKAROUND section below can also run.
-- ============================================================================
\set ON_ERROR_STOP off
\echo ''
\echo '==================== pg_partman version ===================='
SELECT extversion FROM pg_extension WHERE extname = 'pg_partman';
-- ----------------------------------------------------------------------------
-- 1. THE BUG
-- ----------------------------------------------------------------------------
\echo ''
\echo '#################### 1. BUG ####################'
DROP SCHEMA IF EXISTS repro CASCADE;
CREATE SCHEMA repro;
-- A plain monthly RANGE-partitioned table.
CREATE TABLE repro.events (
id bigint NOT NULL,
event_date date NOT NULL,
payload text,
PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);
SELECT partman.create_parent(
p_parent_table := 'repro.events',
p_control := 'event_date',
p_interval := '1 month',
p_type := 'range',
p_jobmon := false
);
-- Historical rows (far from now) fall into the DEFAULT partition, because
-- create_parent only pre-makes partitions around the current date.
--
-- The KEY detail: the lowest value (2002-06-15) is NOT aligned to a month
-- boundary. partition_data_time builds its batch window from that raw minimum,
-- i.e. [2002-06-15, 2002-07-15), which spans TWO months — yet it routes the
-- whole batch into the single partition of the window start (June 2002).
-- The 2002-07-01 row therefore lands in the June partition and is rejected.
INSERT INTO repro.events (id, event_date, payload) VALUES
(1, DATE '2002-06-15', 'mid-June'),
(2, DATE '2002-07-01', 'boundary: 1st of July'),
(3, DATE '2002-07-15', 'mid-July');
\echo '--- before: all rows sit in the default partition ---'
SELECT tableoid::regclass AS partition, count(*) FROM repro.events GROUP BY 1;
\echo '--- calling partition_data_proc (EXPECT: "violates partition constraint") ---'
CALL partman.partition_data_proc('repro.events');
\echo '--- after: rows are STILL in default because the move above errored ---'
SELECT tableoid::regclass AS partition, id, event_date FROM repro.events ORDER BY id;
\echo '--- rows still in default (bug => 3, fixed => 0) ---'
SELECT count(*) AS rows_in_default FROM repro.events_default;
-- ----------------------------------------------------------------------------
-- 2. The p_date_trunc_interval parameter is NOT the cause
-- Same setup but with p_date_trunc_interval := 'month' — fails identically.
-- ----------------------------------------------------------------------------
\echo ''
\echo '#################### 2. p_date_trunc_interval := month (same failure) ####################'
DROP SCHEMA IF EXISTS repro_trunc CASCADE;
CREATE SCHEMA repro_trunc;
CREATE TABLE repro_trunc.events (
id bigint NOT NULL, event_date date NOT NULL, payload text,
PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);
SELECT partman.create_parent(
p_parent_table := 'repro_trunc.events',
p_control := 'event_date',
p_interval := '1 month',
p_type := 'range',
p_date_trunc_interval := 'month',
p_jobmon := false
);
INSERT INTO repro_trunc.events (id, event_date, payload) VALUES
(1, DATE '2002-06-15', 'mid-June'),
(2, DATE '2002-07-01', 'boundary: 1st of July'),
(3, DATE '2002-07-15', 'mid-July');
\echo '--- calling partition_data_proc (EXPECT: same error) ---'
CALL partman.partition_data_proc('repro_trunc.events');
SELECT count(*) AS rows_in_default FROM repro_trunc.events_default;
-- ----------------------------------------------------------------------------
-- 3. WORKAROUND: pre-create the partitions, then INSERT directly.
-- PostgreSQL's native row routing has no such off-by-one, so every row —
-- including the month boundaries — lands in the correct partition.
-- ----------------------------------------------------------------------------
\echo ''
\echo '#################### 3. WORKAROUND (native routing) ####################'
DROP SCHEMA IF EXISTS fixed CASCADE;
CREATE SCHEMA fixed;
CREATE TABLE fixed.events (
id bigint NOT NULL, event_date date NOT NULL, payload text,
PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);
SELECT partman.create_parent(
p_parent_table := 'fixed.events',
p_control := 'event_date',
p_interval := '1 month',
p_type := 'range',
p_jobmon := false
);
-- Pre-create exactly the months we are about to load (partman-managed partitions,
-- correct names). In real use, derive these from the data:
-- ARRAY(SELECT DISTINCT date_trunc('month', <date_col>)::timestamptz FROM <staging>)
SELECT partman.create_partition_time(
'fixed.events',
ARRAY['2002-06-01', '2002-07-01']::timestamptz[]
);
INSERT INTO fixed.events (id, event_date, payload) VALUES
(1, DATE '2002-06-15', 'mid-June'),
(2, DATE '2002-07-01', 'boundary: 1st of July'),
(3, DATE '2002-07-15', 'mid-July');
\echo '--- distribution (each row in its own month) ---'
SELECT tableoid::regclass AS partition, id, event_date FROM fixed.events ORDER BY id;
\echo '--- rows in default (EXPECT 0) ---'
SELECT count(*) AS rows_in_default FROM fixed.events_default;
\echo ''
\echo '==================== done ===================='
```
Problem seams to come from a text comparison https://github.com/pgpartman/pg_partman/blob/development/sql/functions/partition_data_time.sql#L311-... :
When I have a date value, its text representation is `2002-07-01` (date without time). When compared in code, it is casted as timestamptz, then text, `2002-07-01 00:00:00+00`. So the string comparison is `'2002-07-01' < '2002-07-01 00:00:00+00'` => `true` : "a prefix is placed before".
A fix would be to make comparison as timestamptz instead of text.
Old code (https://github.com/pgpartman/pg_partman/blob/development/sql/functions/partition_data_time.sql#L311-...):
```sql
IF v_time_decoder IS NULL THEN
IF v_control_exact_type = 'timestamp' THEN
-- Conversion to text of timestamp without timezone cannot be directly compared to timestamptz
-- that is returned by other functions and used below (See Github Issue #838)
v_decoded_col := format('%s::timestamptz::text', v_partition_expression);
ELSE
v_decoded_col := format('%s::text', v_partition_expression);
END IF;
ELSE
v_decoded_col := format('%s(%s::text)', v_time_decoder, v_partition_expression);
END IF;
```
New code :
```sql
IF v_time_decoder IS NULL THEN
v_decoded_col := format('%s::timestamptz', v_partition_expression);
ELSE
v_decoded_col := format('%s(%s::text)', v_time_decoder, v_partition_expression);
END IF;
```
I have tested it locally, and it does not break #838.