How to fix cannot TRUNCATE ONLY on partitioned table in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 22, 2025· 126 views

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.

Quick fix

Fixes cannot TRUNCATE ONLY on partitioned table

-- Instead of this (causes error):
TRUNCATE ONLY parent_table;

-- Use this (truncates parent and all partitions):
TRUNCATE parent_table;
  1. Remove the ONLY keyword to truncate all partitions
  2. Truncate specific partitions directly
  3. Use CASCADE if you have foreign key dependencies
  4. Update migration scripts to handle partitioned tables
postgresql — error
-- Instead of this (causes error):
TRUNCATE ONLY parent_table;
-- Use this (truncates parent and all partitions):
TRUNCATE parent_table;
cannot TRUNCATE ONLY on partitioned table

What this error means

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.

Typical symptoms

  • TRUNCATE ONLY command fails with error message "cannot TRUNCATE ONLY on partitioned table"
  • Database migration or cloning tools report truncation errors on partitioned tables
  • Scripts that worked on regular tables fail when run against partitioned tables
  • PostgreSQL provides a HINT suggesting to not specify ONLY or use TRUNCATE ONLY on partitions directly

Common causes

  1. Using TRUNCATE ONLY syntax on a partitioned parent table instead of its child partitions
  2. Migration tools or scripts designed for non-partitioned tables being used with partitioned tables
  3. Attempting to truncate a parent table without understanding partitioned table architecture
  4. Copy-pasting TRUNCATE commands from documentation or examples meant for regular tables

How to fix "cannot TRUNCATE ONLY on partitioned table"

1Remove the ONLY keyword to truncate all partitions

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:

sql
-- 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.

2Truncate specific partitions directly

If you only want to clear data from specific partitions, use TRUNCATE ONLY directly on the individual partition tables:

sql
-- 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.

3Use CASCADE if you have foreign key dependencies

If your partitioned table has foreign key references from other tables, you may need to use the CASCADE option:

sql
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:

sql
-- 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';
4Update migration scripts to handle partitioned tables

If you're using database migration tools or custom scripts, update them to detect partitioned tables and adjust the TRUNCATE syntax accordingly:

sql
-- 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.

Advanced notes

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.

Frequently asked questions

What does "cannot TRUNCATE ONLY on partitioned table" mean?
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.
How do I fix "cannot TRUNCATE ONLY on partitioned table"?
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.
What causes "cannot TRUNCATE ONLY on partitioned table"?
Using TRUNCATE ONLY syntax on a partitioned parent table instead of its child partitions Migration tools or scripts designed for non-partitioned tables being used with partitioned tables Attempting to truncate a parent table without understanding partitioned table architecture
Is the "cannot TRUNCATE ONLY on partitioned table" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading