title: How to migrate an existing Postgres Table to partitioned table as transparently as possible?
body: <p>I have an existing table in a postgres-DB. For the sake of demonstration, this is how it looks like:</p>
  
  <pre><code>create table myTable(
      forDate date not null,
      key2 int not null,
      value int not null,
      primary key (forDate, key2)
  );
  
  insert into myTable (forDate, key2, value) values
      ('2000-01-01', 1, 1),
      ('2000-01-01', 2, 1),
      ('2000-01-15', 1, 3),
      ('2000-03-02', 1, 19),
      ('2000-03-30', 15, 8),
      ('2011-12-15', 1, 11);
  </code></pre>
  
  <p>However in contrast to these few values, <code>myTable</code> is actually HUGE and it is growing continuously. I am generating various reports from this table, but currently 98% of my reports work with a single month and the remaining queries work with an even shorter timeframe. Oftentimes my queries cause Postgres to do table scans over this huge table and I am looking for ways to reduce the problem. <a href="https://www.postgresql.org/docs/10/ddl-partitioning.html" rel="noreferrer">Table partitioning</a> seems to fit my problem perfectly. I could just partition my table into months. But how do I turn my existing table into a partitioned table? The manual explicitly states:</p>
  
  <blockquote>
    <p>It is not possible to turn a regular table into a partitioned table or vice versa</p>
  </blockquote>
  
  <p>So I need to develop my own migration script, which will analyze the current table and migrate it. The needs are as follows:</p>
  
  <ul>
  <li>At design time the time frame which <code>myTable</code> covers is unknown.</li>
  <li>Each partition should cover one month from the first day of that month to the last day of that month.</li>
  <li>The table will grow indefinitely, so I have no sane "stop value" for how many tables to generate</li>
  <li>The result should be as transparent as possible, meaning that I want to touch as little as possible of my existing code. In best case this feels like a normal table which I can insert to and select from without any specials.</li>
  <li>A database downtime for migration is acceptable</li>
  <li>Getting along with pure Postgres without any plugins or other things that need to be installed on the server is highly preferred.</li>
  <li>Database is PostgreSQL 10, upgrading to a newer version will happen sooner or later anyway, so this is an option if it helps</li>
  </ul>
  
  <p><strong>How can I migrate my table to be partitioned?</strong></p>