creating new table partition with values already existing in the default partition

I have the following table:

CREATE TABLE orders
(
  info_date  date,
  country_code  VARCHAR,
  order_total   int,
  CONSTRAINT orders_pk PRIMARY KEY (info_date, country_code)
) PARTITION BY LIST (country_code);

CREATE TABLE orders_def PARTITION OF orders DEFAULT;

I insert some rows with country_code 'foo' and they end up in the default partition.

After some time i decide that 'foo' deserves it's own partition, how would i do it?

As according to the doc:


If a DEFAULT partition exists, a new partition cannot be added if
there are any rows in DEFAULT partition such that they would otherwise
fit in the new partition being added.

this my attempt:

begin;
CREATE TEMP TABLE temp_table ON COMMIT DROP AS 
SELECT * FROM orders where country_code = 'foo';
DELETE FROM orders where country_code = 'foo';
CREATE TABLE orders_foo PARTITION OF orders FOR VALUES IN ('foo');
INSERT INTO orders_foo select * from temp_table;
commit;

however it seems like a hack.

The non-hack would be to not get into this situation in the first place, by not using a default partition when you are not really sure that that is permanently what you want. When we fail to correctly foresee the future, sometimes we have to resort to hacks.

You could make it slightly less hack by populating a permanent table, then attaching it as the new partition, rather than juggling the data out and in with a temp table.

begin;
CREATE TABLE orders_foo (like orders); 
INSERT into orders_foo SELECT * FROM orders where country_code = 'foo';
DELETE FROM orders where country_code = 'foo';
ALTER TABLE orders ATTACH PARTITION orders_foo FOR VALUES IN ('foo');
commit;

You could also combine the INSERT and DELETE into a single statement, but I don't know that that really gets you anything worthwhile, other than avoiding one opportunity to misspell 'foo'.

with t as (delete from orders where country_code='foo' returning *) 
   insert into  orders_bar select * from t;

StackExchange Database Administrators Q#264463, answer score: 5