3

All examples show:

CREATE TABLE ... PARTITION BY ...

Which is kind of ridiculous, because the only time you would use partitioning is when a dataset has become too large, which by definition is not going to be a new table. If someone is making a new table with partitioning, i think almost anyone would criticize that as a premature optimization.

quinn
  • 5,508
  • 10
  • 34
  • 54

1 Answers1

3

Just create a partitioned table and attach the existing table as a partition:

create table test (a int);

insert into test select generate_series(1,10);

alter table test_parent attach partition test DEFAULT;

select * from test_parent;
 a
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

You could also rename the table. However, if you do this, you will need to re-define any views that point at the original table.

Jeremy
  • 6,313
  • 17
  • 20
  • I'm concerned about renaming a table with millions of rows? or is that fine – quinn Sep 11 '19 at 20:04
  • The number of rows doesn't matter, it's just an update to the system tables. It does require an exclusive lock to do it, but the actual operation is very fast. Updating any dependencies in views or functions is far more problematic. – Jeremy Sep 11 '19 at 23:31