Wednesday, January 9, 2019

How to avoid ERROR: no partition of relation ... found for row in PostgreSQL



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