PostgreSQL add column to Partition (for Table Inheritance)

Question

Why isn't it possible to add a column to a partition?

From my point of view, the partitioning feature introduced in PostgreSQL is much more useful than table inheritance.

Table inheritance shoots at you from every corner.

You can't reference parent table in the foreign key (though it declares ID, yet since the values are inserted only into concrete tables, you aren't able to reference abstract table's id in the Foreign Key).

No unique constraint (not even primary key) is propagated to the child tables. You have to do it all manually.

Neither do foreign keys defined on the abstract tablet propagate to the children.

Yet what's good with it is that it allows to have separate schema for child tables so you can define columns not present in the parent table.

On the other hand, with partitioning it's possible to define everything (primary keys, constraints, foreign keys), and reference a partitioned table in the foreign Key. This is all you'd primarily want from table inheritance.

Yet it doesn't allow deviation from partition schemas.

ALTER TABLE user_login_events
ADD COLUMN is_special boolean DEFAULT false;
[42809] ERROR: cannot add column to a partition

What I want to do:

CREATE TABLE user_events
(
    id        UUID         NOT NULL,
    user_id   UUID         NOT NULL
        CONSTRAINT fk_36d54c77a76ed395
            REFERENCES users,
    timestamp TIMESTAMP(6) NOT NULL,
    type      VARCHAR(255) NOT NULL,
    PRIMARY KEY (id, type)
) PARTITION BY LIST (type);

CREATE TABLE user_registered_events PARTITION OF user_events
    FOR VALUES IN ('register');

ALTER TABLE user_registered_events
    ADD COLUMN email VARCHAR NOT NULL;

CREATE TABLE user_login_events PARTITION OF user_events
    FOR VALUES IN ('login');

ALTER TABLE user_login_events
    ADD COLUMN session_id UUID NOT NULL;

INSERT INTO user_registered_events (id, user_id, timestamp, type, email)
VALUES ('3fb6ca2a-ca12-7e44-b7a3-6ff6b25ce48e', '6ba48d49-83a8-7542-bf6d-c3590b870ef8', '2025-10-01 12:00:00', 'register', '[email protected]');

INSERT INTO user_logged_in_events (id, user_id, timestamp, type, session_id)
VALUES ('f498a758-b6a9-7185-87b9-ca4e106cf4b1', '6ba48d49-83a8-7542-bf6d-c3590b870ef8', '2025-10-01 12:01:00', 'login', 'ae1313cf-0307-739b-b7ea-40377a064247');

-- Should return both events as user_events --
SELECT * FROM user_events;

What does table inheritance or any other feature have to do with details of the functionality of partitions? Why are you mentioning it? — philipxy
– philipxy, Commented Jun 20 at 23:03
Is asking "why" on language specifications still considered as "primarily opinion-based" if it can have official answers? Should "why language feature designed particular way" be closed/moved? — philipxy
– philipxy, Commented Jun 20 at 23:16
Why are you mentioning it? Because it's used this way: postgresql.org/docs/current/… — rela589n
– rela589n, Commented Jun 24 at 8:45
Clarify via edits, not comments; delete & flag obsolete comments. PS Your comment doesn't answer my question. — philipxy
– philipxy, Commented Jun 24 at 10:13

Laurenz Albe · Accepted Answer · 2025-06-20 08:51:40Z

1

You cannot do that with partitioning, and you never will. As a consolation, consider that you wouldn't be able to get the primary key that you want (id) with partitioning.

Implementing polymorphism in a relational database is difficult. Here are some ideas how you could do it. And here is a collection of useful Stackoverflow links around that problem.

answered Jun 20 at 8:51

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL add column to Partition (for Table Inheritance)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related