This error occurs when attempting to use TRUNCATE ONLY on a partitioned table, which is not allowed because partitioned tables are logical containers that hold no data themselves—all data resides in their child partitions.
This error appears when you try to execute a TRUNCATE ONLY command on a partitioned table in PostgreSQL. Partitioned tables in PostgreSQL are logical parent tables that organize data across multiple child partitions. The parent table itself does not store any actual rows—all data resides in the individual partition tables. The TRUNCATE ONLY syntax is designed to truncate only the specified table without affecting any descendant tables. However, since a partitioned table contains no data of its own (it's just a logical container), attempting to truncate "only" the parent table is a meaningless operation. PostgreSQL therefore raises this error to prevent confusion and ensure you explicitly specify whether you want to truncate all partitions or specific ones. This error is commonly encountered in database migration tools, backup/restore operations, or scripts that were written for non-partitioned tables and haven't been updated to handle partitioned table semantics properly.
If you want to remove all data from the partitioned table and all its child partitions, simply remove the ONLY keyword from your TRUNCATE command:
-- Instead of this (causes error):
TRUNCATE ONLY parent_table;
-- Use this (truncates parent and all partitions):
TRUNCATE parent_table;This will truncate the parent table and automatically cascade to all child partitions, removing all data from the entire partitioned table structure.
If you only want to clear data from specific partitions, use TRUNCATE ONLY directly on the individual partition tables:
-- List all partitions first
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public'
AND tablename LIKE 'parent_table_%';
-- Truncate specific partition(s)
TRUNCATE ONLY parent_table_2023;
TRUNCATE ONLY parent_table_2024;This approach gives you fine-grained control over which partitions to clear without affecting others.
If your partitioned table has foreign key references from other tables, you may need to use the CASCADE option:
TRUNCATE parent_table CASCADE;Be careful with CASCADE as it will also truncate any tables that have foreign key references to your partitioned table. Review dependent tables first:
-- Check foreign key dependencies
SELECT
tc.table_name,
kcu.column_name,
ccu.table_name AS foreign_table_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND ccu.table_name = 'parent_table';If you're using database migration tools or custom scripts, update them to detect partitioned tables and adjust the TRUNCATE syntax accordingly:
-- Check if a table is partitioned
SELECT
tablename,
CASE
WHEN EXISTS (
SELECT 1
FROM pg_inherits
WHERE inhparent = (tablename::regclass)::oid
) THEN 'partitioned'
ELSE 'regular'
END AS table_type
FROM pg_tables
WHERE tablename = 'your_table_name';Then conditionally use TRUNCATE (without ONLY) for partitioned tables, or TRUNCATE ONLY for regular tables as needed.
Partitioning Types: PostgreSQL supports multiple partitioning strategies (range, list, hash). This error applies to all partitioning types since the fundamental architecture—parent table as logical container, child tables storing data—is consistent across all methods.
Performance Considerations: TRUNCATE is generally much faster than DELETE for removing all rows because it doesn't scan the table or generate row-level WAL records. When truncating partitioned tables, PostgreSQL truncates each partition individually, which is still very efficient even with hundreds of partitions.
Inheritance vs Declarative Partitioning: Older PostgreSQL versions (pre-10) used table inheritance for partitioning. The same TRUNCATE ONLY restriction applies, though declarative partitioning (introduced in PostgreSQL 10) provides better performance and more automatic partition management.
Partition Pruning: When you truncate the parent table (without ONLY), PostgreSQL automatically identifies all child partitions and truncates them. You don't need to manually enumerate partitions in your TRUNCATE command.
Transaction Safety: TRUNCATE operations are transaction-safe. If you truncate within a transaction and then roll back, the data will be restored. This is particularly useful when testing partition maintenance scripts.
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