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
'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.