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?

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.

2
  • I can't believe I missed that. I guess next time I'll RTFM instead of SkimmingTFM. – sheepdog Jul 2 '15 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 '15 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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