There are two columns, one of them is a certain identifier (NOT a primary key - it can be duplicated), the other is a boolean, which indicates which of the same identifiers stores actual data in its row.

That is something like this:

1|124|true 2|124|false 3|124|false 4|541|true 5|138|true

How to describe in the table schema, that for any number of equal identifiers, there can be only one value true (actual), and the rest - only false (irrelevant)?

    1 answer 1

    For this task, a partial index is needed, which in postgresql can be unique and thus guarantee the uniqueness of the values ​​only for part of the table:

     create unique index on tablename (fieldname) where some_immutable_condition

    Assuming that your boolean field is called is_active , and the key_id identifier, then:

     create unique index on tablename (key_id) where is_active

    Another possibility to do strange things with restrictions on the rows in the table is Exclusion Constraints (it is up to them, for example, to check that the hotel booking dates do not overlap with each other). But check constraints do not give such a focus, they can only check their own string.