Building on my previous post, I would now like to insert the following rows into my LIST partitioned table:
INSERT INTO ORDERS values (1,'NO',100,'01.02.2018',1000); INSERT INTO ORDERS values (2,'SE',101,'01.03.2018',1200);
So one rows that I expect to go into the partition for Norway, and another row for the partition for Sweden.
I execute the following:
INSERT INTO ORDERS values (1,'NO',100,'01.02.2018',1000);
which results in an error:
Error: ERROR: no partition of relation "orders" found for row Detail: Partition key of the failing row contains (country_code) = (NO).
Contrary to Oracle, with PostgreSQL you need to explicitly create your partition first, like this:
CREATE TABLE orders_NO PARTITION OF orders FOR VALUES IN ('NO');
After this is done, PostgreSQL will accept your INSERT-statement.
You can also enforce contraints at the same time, if desirable:
CREATE TABLE orders_NO PARTITION OF orders ( CONSTRAINT orders_id_nn CHECK (id is not null) ) FOR VALUES IN ('NO');
No comments:
Post a Comment