This error occurs when attempting to attach a table as a partition to a partitioned table, but the existing data in that table violates the partition constraints or range bounds you are defining.
This error appears when you try to attach an existing table as a partition to a partitioned table using the ALTER TABLE ... ATTACH PARTITION command, but some rows in that table contain values that fall outside the partition range or list constraints you've specified. Partitioned tables in PostgreSQL organize data across multiple child partitions based on partition keys (range, list, or hash). When attaching an existing table as a partition, PostgreSQL validates that all rows in that table satisfy the partition constraint. If any row contains a key value that doesn't match the partition bounds, the ATTACH PARTITION operation fails with this error. This commonly happens when: - Attaching a table with data that falls outside the partition range (e.g., attaching to range 0-5 but table contains value 5) - Attaching a table to a list partition where rows contain values not in the specified list - The table being attached has CHECK constraints that conflict with or contradict the partition constraint
First, check what constraint you're trying to apply and examine the actual data in the table:
-- Check the data in the table being attached
SELECT * FROM table_to_attach;
-- Look for values that might fall outside the partition range
SELECT column_name, COUNT(*) as count
FROM table_to_attach
GROUP BY column_name
ORDER BY column_name;For example, if attaching with constraint FOR VALUES FROM (0) TO (5), this range includes 0, 1, 2, 3, 4 but NOT 5 or higher. Verify no rows have value >= 5 in the partition key column.
Find which specific rows are causing the violation:
-- For range partitions, find values outside bounds
SELECT * FROM table_to_attach
WHERE partition_key_column NOT BETWEEN 0 AND 4; -- For FROM 0 TO 5
-- For list partitions, find values not in the list
SELECT * FROM table_to_attach
WHERE partition_key_column NOT IN (1, 2, 3);
-- Show a summary of values present
SELECT partition_key_column, COUNT(*) as count
FROM table_to_attach
GROUP BY partition_key_column
ORDER BY partition_key_column;This helps you understand what data needs to be handled before attachment.
Delete or move rows that violate the partition constraint:
-- Delete rows outside the partition range
DELETE FROM table_to_attach
WHERE partition_key_column >= 5; -- Assuming range is 0-4
-- Or move them to a different table first
INSERT INTO other_table SELECT * FROM table_to_attach
WHERE partition_key_column >= 5;
DELETE FROM table_to_attach
WHERE partition_key_column >= 5;After removing violating rows, the table will satisfy the partition constraint.
If the table has CHECK constraints that conflict with the partition constraint, remove them:
-- Find CHECK constraints on the table
SELECT constraint_name, constraint_definition
FROM information_schema.table_constraints
WHERE table_name = 'table_to_attach'
AND constraint_type = 'CHECK';
-- Drop conflicting constraints
ALTER TABLE table_to_attach
DROP CONSTRAINT constraint_name;Partition constraints are automatically enforced, so pre-existing CHECK constraints on the partition key can cause conflicts.
Now that the data matches the partition constraint, attach the table as a partition:
-- For range partitions
ALTER TABLE partitioned_table
ATTACH PARTITION table_to_attach
FOR VALUES FROM (0) TO (5);
-- For list partitions
ALTER TABLE partitioned_table
ATTACH PARTITION table_to_attach
FOR VALUES IN (1, 2, 3);
-- For hash partitions
ALTER TABLE partitioned_table
ATTACH PARTITION table_to_attach
FOR VALUES WITH (MODULUS 4, REMAINDER 0);The ATTACH PARTITION operation should now succeed without validation errors.
If you have data that doesn't fit into predefined partitions, create a default partition to capture it instead of losing data:
-- Create a default partition for overflow data
CREATE TABLE partitioned_table_default
PARTITION OF partitioned_table DEFAULT;
-- Move violating rows into the default partition
INSERT INTO partitioned_table_default
SELECT * FROM table_to_attach
WHERE partition_key_column >= 5;
-- Clean up the original table
DELETE FROM table_to_attach
WHERE partition_key_column >= 5;This approach preserves data while maintaining partition integrity. You can then migrate data from the default partition into proper partitions as needed.
Range Boundaries: Range partitions in PostgreSQL use inclusive lower bounds and exclusive upper bounds. FROM (0) TO (5) means 0 ≤ value < 5. The value 5 belongs in the next partition, not this one. Confusion about this is a common cause of the error.
Attaching vs Inserting: The error only occurs during ATTACH PARTITION. Once a partition is successfully attached, PostgreSQL's automatic constraint checking prevents inserting rows that violate the partition constraint into that partition.
List Partitions: For list partitions, each partition must explicitly list the exact values it handles. A row with a value not in any partition's list will fail to attach. Rows must be distributed across existing partitions or moved to a default partition.
No Partial Attachment: PostgreSQL performs all-or-nothing validation. If even one row violates the constraint, the entire ATTACH operation fails. You cannot selectively attach rows.
Hash Partitions: Hash-based partitioning automatically determines which partition rows belong to. However, when attaching existing tables with hash partitioning, PostgreSQL still validates that rows hash to the correct partition modulus and remainder values.
Performance on Large Tables: Validation of partition constraints happens in-memory and can be slow on very large tables (millions of rows). Consider using ATTACH PARTITION ... (constraint_name) in newer PostgreSQL versions (11+) to use existing constraints as proof of partition correctness and skip validation.
Inheritance Semantics: Declarative partitioning (PARTITION BY) is different from older table inheritance methods. Table inheritance requires manual constraint management and doesn't support ATTACH PARTITION syntax.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL