Partition a table by related column value from another table in PostgreSQL

I have a simple data model of customers(unpartitioned), products(unpartitioned) and orders(partitioned) table.

CREATE TABLE customers
(
    customer_id SERIAL PRIMARY KEY,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    email       VARCHAR(100),
    phone       VARCHAR(15),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products
(
    product_id   SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    category     VARCHAR(50),
    price        NUMERIC(10, 2),
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders
(
    order_id     SERIAL NOT NULL,
    customer_id  INT    NOT NULL REFERENCES customers (customer_id),
    order_date   DATE   NOT NULL,
    total_amount NUMERIC(10, 2),
    PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

I want to create a partitioned order_details table which should ideally be partitioned by product category which is not working in prostgres.

I want to achieve something like this:

-- create and partition the order_details table by product_name
CREATE TABLE order_details
(
    order_detail_id SERIAL PRIMARY KEY,
    order_id        INT    NOT NULL REFERENCES orders (order_id),
    product_id      INT    NOT NULL REFERENCES products (product_id),
    quantity        INT,
    price           NUMERIC(10, 2),
    discount        NUMERIC(5, 2) DEFAULT 0.00
) PARTITION BY list (select category from products p where p.product_id = product_id);

Is there any way to achieve this?

Edit:

To clear some confusion, I will add an explicit partition list after this, and don't expect postgres to create partitions automatically. example:

CREATE TABLE order_details_electronics PARTITION OF order_details
    FOR VALUES IN ('Electronics');

But since I cannot run the CREATE TABLE order_details... query I cannot even get to that stage

Solution

No, you cannot use “reference partitioning” like that in PostgreSQL.

You would have to add a redundant copy of the category column from products to order_details. To guarantee data integrity, the foreign key from order_details to products should contain both product_id and category. This in turn requires a redundant unique constraint on these both columns in products.

This is not a pretty solution, but it works.