6

I have a table in PostgreSQL 9.4 that I partitioned so that all inserts to the parent actually go into one of the child partitions. The problem is that I already have hundreds of millions of rows of data in the parent table from before I created partitions.

The parent and children exist in different schemas:

parent: public.history_uint
children: partitions.history_uint_p2015_mm_dd

Here is how I created the partitions.

How to TRUNCATE the parent but not the children?

Improve this question

1 Answer 1

Reset to default
12

Use the ONLY key word:

TRUNCATE ONLY public.history_uint;

The manual:

If ONLY is specified before the table name, only that table is truncated. If ONLY is not specified, the table and all its descendant tables (if any) are truncated. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included.

The schemas where parent and children live are only relevant as far as user privileges on the schema are concerned. Otherwise it's all the same to Postgres.

Improve this answer
4
  • I can't believe I missed that. I guess next time I'll RTFM instead of SkimmingTFM.
    – sheepdog
    Jul 2, 2015 at 21:13
  • 1
    @sheepdog: SkimmingTFM is still better than the widespread SkippingTFM or the occasional AvoidingTFM. So you're good. :)
    – Erwin Brandstetter
    Jul 2, 2015 at 21:15
  • @ErwinBrandstetter Here child is the table that has foreign key referred to parent table? if so, how can the child table have records when there is no entry in parent table? pls correct me if I'm wrong.
    – Mano
    Mar 29, 2022 at 18:26
  • @Mano: This is not about FK constraints, but about inheritance which has been falling out of favor since the advent of declarative partitioning.
    – Erwin Brandstetter
    Mar 29, 2022 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.