Postgresql Exclusion constraints, want something weird?

Question

PostgreSQL

Good afternoon colleagues.
There is a table:

CREATE TABLE test_good
(
    id BIGINT PRIMARY KEY NOT NULL,
    good_id UUID DEFAULT uuid_generate_v4() NOT NULL,
    by_default BOOLEAN DEFAULT false NOT NULL,
);

It is required, when inserting and updating data, to limit the value of the by_default field to only one true within one good_id. Those. we receive a flag "record by default".
Those. if among the 5 available entries for good_id N there is an entry in which the by_default column is set to true, all the rest must be guaranteed to be false. Setting a flag to another field should result in the flag of the existing one being inverted.
Something like this ..
I want to do it using the database, something tells me that Exclusion constraints may come up, but I can’t figure out how exactly.

Answer 1

Somehow this is not very in the spirit of relational databases. You can for example create a defaults table with group_id(unique) and default_item_id if one or no item can be selected for the group.

Answer 2

CREATE UNIQUE INDEX test_good_default_goods ON test_good (good_id)
WHERE by_default = true

It's an integrity constraint so you don't shoot yourself in the foot.
Update:
Instead
and some background magic, update like this:
If necessary, uuid can be pulled out by a subquery on the same :new_default_id.
However, this update may stumble on a unique index, I don’t remember exactly when uniqueness is checked - for the entire expression or for each row. Well, it will be possible to rewrite the request. After all, we still have CTE.