{"id":12485,"date":"2019-06-06T06:00:57","date_gmt":"2019-06-06T04:00:57","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-6-attaching-and-detaching-partitions\/"},"modified":"2019-06-06T06:00:57","modified_gmt":"2019-06-06T04:00:57","slug":"postgresql-partitioning-6-attaching-and-detaching-partitions","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-6-attaching-and-detaching-partitions\/","title":{"rendered":"PostgreSQL partitioning (6): Attaching and detaching partitions"},"content":{"rendered":"

Having talked about partitioning strategies and partition pruning this time we will have a look on how you can attach and detach partitions to and from an existing partitioned table. If you missed the last posts about partitioning in PostgreSQL here they are:<\/p>\n

    \n
  1. PostgreSQL partitioning (1): Preparing the data set<\/a><\/li>\n
  2. PostgreSQL partitioning (2): Range partitioning<\/a><\/li>\n
  3. PostgreSQL partitioning (3): List partitioning<\/a><\/li>\n
  4. PostgreSQL partitioning (4) : Hash partitioning<\/a><\/li>\n
  5. PostgreSQL partitioning (5): Partition pruning<\/a><\/li>\n<\/ol>\n

    <\/p>\n

    From time to time it might be required that you attach a partition to an existing partitioned table. A reason for that might be that you partitioned by year and suddenly you get data for the next year because your data is based on imported data from an external source. Of course you could just create a new partition for that and then load the data but there is a more elegant way of doing that.<\/p>\n

    Our range partitioned table currently has partitions for the years 2012 to 2020:<\/p>\n

    \npostgres=# d+ traffic_violations_p\n                                      Partitioned table \"public.traffic_violations_p\"\n         Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description \n-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------\n seqid                   | text                   |           |          |         | extended |              | \n date_of_stop            | date                   |           |          |         | plain    |              | \n time_of_stop            | time without time zone |           |          |         | plain    |              | \n agency                  | text                   |           |          |         | extended |              | \n subagency               | text                   |           |          |         | extended |              | \n description             | text                   |           |          |         | extended |              | \n location                | text                   |           |          |         | extended |              | \n latitude                | numeric                |           |          |         | main     |              | \n longitude               | numeric                |           |          |         | main     |              | \n accident                | text                   |           |          |         | extended |              | \n belts                   | boolean                |           |          |         | plain    |              | \n personal_injury         | boolean                |           |          |         | plain    |              | \n property_damage         | boolean                |           |          |         | plain    |              | \n fatal                   | boolean                |           |          |         | plain    |              | \n commercial_license      | boolean                |           |          |         | plain    |              | \n hazmat                  | boolean                |           |          |         | plain    |              | \n commercial_vehicle      | boolean                |           |          |         | plain    |              | \n alcohol                 | boolean                |           |          |         | plain    |              | \n workzone                | boolean                |           |          |         | plain    |              | \n state                   | text                   |           |          |         | extended |              | \n vehicletype             | text                   |           |          |         | extended |              | \n year                    | smallint               |           |          |         | plain    |              | \n make                    | text                   |           |          |         | extended |              | \n model                   | text                   |           |          |         | extended |              | \n color                   | text                   |           |          |         | extended |              | \n violation_type          | text                   |           |          |         | extended |              | \n charge                  | text                   |           |          |         | extended |              | \n article                 | text                   |           |          |         | extended |              | \n contributed_to_accident | boolean                |           |          |         | plain    |              | \n race                    | text                   |           |          |         | extended |              | \n gender                  | text                   |           |          |         | extended |              | \n driver_city             | text                   |           |          |         | extended |              | \n driver_state            | text                   |           |          |         | extended |              | \n dl_state                | text                   |           |          |         | extended |              | \n arrest_type             | text                   |           |          |         | extended |              | \n geolocation             | point                  |           |          |         | plain    |              | \n council_districts       | smallint               |           |          |         | plain    |              | \n councils                | smallint               |           |          |         | plain    |              | \n communities             | smallint               |           |          |         | plain    |              | \n zip_codes               | smallint               |           |          |         | plain    |              | \n municipalities          | smallint               |           |          |         | plain    |              | \nPartition key: RANGE (date_of_stop)\nPartitions: traffic_violations_p_2012 FOR VALUES FROM ('2012-01-01') TO ('2013-01-01'),\n            traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),\n            traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),\n            traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),\n            traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),\n            traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),\n            traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),\n            traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),\n            traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),\n            traffic_violations_p_default DEFAULT\n<\/pre>\n
    Lets assume we suddenly get data for the year 2021 and we want that data to be part of the range partitioned table. If we keep the partitioned table as it is, new data would go to the default partition and this is not what we want. What we can do is to create a regular table with exactly the same structure as the partitioned table. With PostgreSQL’s create table<\/a> command you have the “like_option” which is very handy for this use case:<\/p>\n
    \npostgres=# create table traffic_violations_p_2021 ( like traffic_violations_p including all );\nCREATE TABLE\npostgres=# d traffic_violations_p_2021\n                     Table \"public.traffic_violations_p_2021\"\n         Column          |          Type          | Collation | Nullable | Default \n-------------------------+------------------------+-----------+----------+---------\n seqid                   | text                   |           |          | \n date_of_stop            | date                   |           |          | \n time_of_stop            | time without time zone |           |          | \n agency                  | text                   |           |          | \n subagency               | text                   |           |          | \n description             | text                   |           |          | \n location                | text                   |           |          | \n latitude                | numeric                |           |          | \n longitude               | numeric                |           |          | \n accident                | text                   |           |          | \n belts                   | boolean                |           |          | \n personal_injury         | boolean                |           |          | \n property_damage         | boolean                |           |          | \n fatal                   | boolean                |           |          | \n commercial_license      | boolean                |           |          | \n hazmat                  | boolean                |           |          | \n commercial_vehicle      | boolean                |           |          | \n alcohol                 | boolean                |           |          | \n workzone                | boolean                |           |          | \n state                   | text                   |           |          | \n vehicletype             | text                   |           |          | \n year                    | smallint               |           |          | \n make                    | text                   |           |          | \n model                   | text                   |           |          | \n color                   | text                   |           |          | \n violation_type          | text                   |           |          | \n charge                  | text                   |           |          | \n article                 | text                   |           |          | \n contributed_to_accident | boolean                |           |          | \n race                    | text                   |           |          | \n gender                  | text                   |           |          | \n driver_city             | text                   |           |          | \n driver_state            | text                   |           |          | \n dl_state                | text                   |           |          | \n arrest_type             | text                   |           |          | \n geolocation             | point                  |           |          | \n council_districts       | smallint               |           |          | \n councils                | smallint               |           |          | \n communities             | smallint               |           |          | \n zip_codes               | smallint               |           |          | \n municipalities          | smallint               |           |          | \n<\/pre>\n
    For now this table has no relational at all to the partitioned table, it just has the very same structure, that’s it. Lets populate the new table with some data for 2021:<\/p>\n
    \ninsert into traffic_violations_p_2021 (date_of_stop)\n       select * from generate_series ( date('01.01.2021')\n                                     , date('12.31.2021')\n                                     , interval '1 day');\n<\/pre>\n
    Now that we have 365 rows in the new table we can attach it to the partitioned table (of course you could already attach it before loading the data as well):<\/p>\n
    \nalter table traffic_violations_p\n      attach partition traffic_violations_p_2021\n      for values from ('2021-01-01') to ('2022-01-01');\n<\/pre>\n
    So you can prepare the whole new data without touching the partitioned table. Once you are happy with it a simple attach command is enough and the partitioned table contains the new partition holding the data:<\/p>\n
    \npostgres=# d+ traffic_violations_p\n                                      Partitioned table \"public.traffic_violations_p\"\n         Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description \n-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------\n seqid                   | text                   |           |          |         | extended |              | \n date_of_stop            | date                   |           |          |         | plain    |              | \n time_of_stop            | time without time zone |           |          |         | plain    |              | \n agency                  | text                   |           |          |         | extended |              | \n subagency               | text                   |           |          |         | extended |              | \n description             | text                   |           |          |         | extended |              | \n location                | text                   |           |          |         | extended |              | \n latitude                | numeric                |           |          |         | main     |              | \n longitude               | numeric                |           |          |         | main     |              | \n accident                | text                   |           |          |         | extended |              | \n belts                   | boolean                |           |          |         | plain    |              | \n personal_injury         | boolean                |           |          |         | plain    |              | \n property_damage         | boolean                |           |          |         | plain    |              | \n fatal                   | boolean                |           |          |         | plain    |              | \n commercial_license      | boolean                |           |          |         | plain    |              | \n hazmat                  | boolean                |           |          |         | plain    |              | \n commercial_vehicle      | boolean                |           |          |         | plain    |              | \n alcohol                 | boolean                |           |          |         | plain    |              | \n workzone                | boolean                |           |          |         | plain    |              | \n state                   | text                   |           |          |         | extended |              | \n vehicletype             | text                   |           |          |         | extended |              | \n year                    | smallint               |           |          |         | plain    |              | \n make                    | text                   |           |          |         | extended |              | \n model                   | text                   |           |          |         | extended |              | \n color                   | text                   |           |          |         | extended |              | \n violation_type          | text                   |           |          |         | extended |              | \n charge                  | text                   |           |          |         | extended |              | \n article                 | text                   |           |          |         | extended |              | \n contributed_to_accident | boolean                |           |          |         | plain    |              | \n race                    | text                   |           |          |         | extended |              | \n gender                  | text                   |           |          |         | extended |              | \n driver_city             | text                   |           |          |         | extended |              | \n driver_state            | text                   |           |          |         | extended |              | \n dl_state                | text                   |           |          |         | extended |              | \n arrest_type             | text                   |           |          |         | extended |              | \n geolocation             | point                  |           |          |         | plain    |              | \n council_districts       | smallint               |           |          |         | plain    |              | \n councils                | smallint               |           |          |         | plain    |              | \n communities             | smallint               |           |          |         | plain    |              | \n zip_codes               | smallint               |           |          |         | plain    |              | \n municipalities          | smallint               |           |          |         | plain    |              | \nPartition key: RANGE (date_of_stop)\nPartitions: traffic_violations_p_2012 FOR VALUES FROM ('2012-01-01') TO ('2013-01-01'),\n            traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),\n            traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),\n            traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),\n            traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),\n            traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),\n            traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),\n            traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),\n            traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),\n            traffic_violations_p_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'),\n            traffic_violations_p_default DEFAULT\n\n<\/pre>\n
    Having the data for 2021 it might not be required anymore to have the data for 2012. Without partitioning you would need to delete from the table for getting rid of those rows. With partitioning you can detach the partition from the partitioned table:<\/p>\n
    \npostgres=# alter table traffic_violations_p detach partition traffic_violations_p_2012;\nALTER TABLE\npostgres=# d+ traffic_violations_p\n                                      Partitioned table \"public.traffic_violations_p\"\n         Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description \n-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------\n seqid                   | text                   |           |          |         | extended |              | \n date_of_stop            | date                   |           |          |         | plain    |              | \n time_of_stop            | time without time zone |           |          |         | plain    |              | \n agency                  | text                   |           |          |         | extended |              | \n subagency               | text                   |           |          |         | extended |              | \n description             | text                   |           |          |         | extended |              | \n location                | text                   |           |          |         | extended |              | \n latitude                | numeric                |           |          |         | main     |              | \n longitude               | numeric                |           |          |         | main     |              | \n accident                | text                   |           |          |         | extended |              | \n belts                   | boolean                |           |          |         | plain    |              | \n personal_injury         | boolean                |           |          |         | plain    |              | \n property_damage         | boolean                |           |          |         | plain    |              | \n fatal                   | boolean                |           |          |         | plain    |              | \n commercial_license      | boolean                |           |          |         | plain    |              | \n hazmat                  | boolean                |           |          |         | plain    |              | \n commercial_vehicle      | boolean                |           |          |         | plain    |              | \n alcohol                 | boolean                |           |          |         | plain    |              | \n workzone                | boolean                |           |          |         | plain    |              | \n state                   | text                   |           |          |         | extended |              | \n vehicletype             | text                   |           |          |         | extended |              | \n year                    | smallint               |           |          |         | plain    |              | \n make                    | text                   |           |          |         | extended |              | \n model                   | text                   |           |          |         | extended |              | \n color                   | text                   |           |          |         | extended |              | \n violation_type          | text                   |           |          |         | extended |              | \n charge                  | text                   |           |          |         | extended |              | \n article                 | text                   |           |          |         | extended |              | \n contributed_to_accident | boolean                |           |          |         | plain    |              | \n race                    | text                   |           |          |         | extended |              | \n gender                  | text                   |           |          |         | extended |              | \n driver_city             | text                   |           |          |         | extended |              | \n driver_state            | text                   |           |          |         | extended |              | \n dl_state                | text                   |           |          |         | extended |              | \n arrest_type             | text                   |           |          |         | extended |              | \n geolocation             | point                  |           |          |         | plain    |              | \n council_districts       | smallint               |           |          |         | plain    |              | \n councils                | smallint               |           |          |         | plain    |              | \n communities             | smallint               |           |          |         | plain    |              | \n zip_codes               | smallint               |           |          |         | plain    |              | \n municipalities          | smallint               |           |          |         | plain    |              | \nPartition key: RANGE (date_of_stop)\nPartitions: traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),\n            traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),\n            traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),\n            traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),\n            traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),\n            traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),\n            traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),\n            traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),\n            traffic_violations_p_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'),\n            traffic_violations_p_default DEFAULT\n<\/pre>\n
    Whatever you want to do with the data from now on: The old partition became a regular table and you can either drop, truncate or move it to low cost storage for archiving.
    \nThis was attaching and detaching of partitions. In the next post we’ll look at indexing and constraints when it comes to partitioned tables.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Having talked about partitioning strategies and partition pruning this time we will have a look on how you can attach and detach partitions to and from an existing partitioned table. If you missed the last posts about partitioning in PostgreSQL here they are: PostgreSQL partitioning (1): Preparing the data set PostgreSQL partitioning (2): Range partitioning […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-12485","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nPostgreSQL partitioning (6): Attaching and detaching partitions - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-6-attaching-and-detaching-partitions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL partitioning (6): Attaching and detaching partitions\" \/>\n<meta property=\"og:description\" content=\"Having talked about partitioning strategies and partition pruning this time we will have a look on how you can attach and detach partitions to and from an existing partitioned table. If you missed the last posts about partitioning in PostgreSQL here they are: PostgreSQL partitioning (1): Preparing the data set PostgreSQL partitioning (2): Range partitioning […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-6-attaching-and-detaching-partitions\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-06-06T04:00:57+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-6-attaching-and-detaching-partitions\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-6-attaching-and-detaching-partitions\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL partitioning (6): Attaching and detaching partitions\",\"datePublished\":\"2019-06-06T04:00:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-6-attaching-and-detaching-partitions\\\/\"},\"wordCount\":439,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-6-attaching-and-detaching-partitions\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-6-attaching-and-detaching-partitions\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-6-attaching-and-detaching-partitions\\\/\",\"name\":\"PostgreSQL partitioning (6): Attaching and detaching partitions - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2019-06-06T04:00:57+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-6-attaching-and-detaching-partitions\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-6-attaching-and-detaching-partitions\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-6-attaching-and-detaching-partitions\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL partitioning (6): Attaching and detaching partitions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\\\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/westermanndanie\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-westermann\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL partitioning (6): Attaching and detaching partitions - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-6-attaching-and-detaching-partitions\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL partitioning (6): Attaching and detaching partitions","og_description":"Having talked about partitioning strategies and partition pruning this time we will have a look on how you can attach and detach partitions to and from an existing partitioned table. If you missed the last posts about partitioning in PostgreSQL here they are: PostgreSQL partitioning (1): Preparing the data set PostgreSQL partitioning (2): Range partitioning […]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-6-attaching-and-detaching-partitions\/","og_site_name":"dbi Blog","article_published_time":"2019-06-06T04:00:57+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-6-attaching-and-detaching-partitions\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-6-attaching-and-detaching-partitions\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL partitioning (6): Attaching and detaching partitions","datePublished":"2019-06-06T04:00:57+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-6-attaching-and-detaching-partitions\/"},"wordCount":439,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-6-attaching-and-detaching-partitions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-6-attaching-and-detaching-partitions\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-6-attaching-and-detaching-partitions\/","name":"PostgreSQL partitioning (6): Attaching and detaching partitions - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-06-06T04:00:57+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-6-attaching-and-detaching-partitions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-6-attaching-and-detaching-partitions\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-6-attaching-and-detaching-partitions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL partitioning (6): Attaching and detaching partitions"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12485","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=12485"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12485\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12485"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12485"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12485"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12485"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}