Menu
To find the name of a constraint in PostgreSQL, use the view pg_constraint in the pg_catalog schema. Join the view pg_catalog. pg_constraint with the view pg_class ( JOIN pg_class t ON t. oid = c.
A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries for only those table rows that satisfy the predicate. Partial indexes are great when you need to filter a specific subset of data.
EXCLUSION Constraint − The EXCLUDE constraint ensures that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s), not all of these comparisons will return TRUE.
PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.
PostgreSQL doesn't define a partial (i.e. conditional) UNIQUE constraint - however, you can create a partial unique index.
PostgreSQL uses unique indexes to implement unique constraints, so the effect is the same, with an important caveat: you can't perform upserts (ON CONFLICT DO UPDATE) against a unique index like you would against a unique constraint.
Also, you won't see the constraint listed in information_schema.
CREATE UNIQUE INDEX stop_myc ON stop (col_a) WHERE (col_b is NOT null);
See partial indexes.
it has already been said that PG doesn't define a partial (ie conditional) UNIQUE constraint. Also documentation says that the preferred way to add a unique constraint to a table is ADD CONSTRAINT Unique Indexes
The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.
There is a way to implement it using Exclusion Constraints, (thank @dukelion for this solution)
In your case it will look like
ALTER TABLE stop ADD CONSTRAINT myc EXCLUDE (col_a WITH =) WHERE (col_b IS null);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With