Consider a table of providers, for which one is the default. For example, payment providers:
CREATE TABLE payment_via ( id uuid PRIMARY KEY, provider text NOT NULL, keys hstore NOT NULL DEFAULT ''
);
Here we store together the name of the provider — medici, paypal — with access tokens needed to use a certain payment account. How shall we store which one is the default? Ideally, we’d be able to ensure there is but one default.
CREATE TABLE payment_via ( id uuid PRIMARY KEY, provider text NOT NULL, keys hstore NOT NULL DEFAULT '', is_default boolean NOT NULL DEFAULT FALSE
);
How shall we state the constraint? The obvious thing would seem to be:
CREATE TABLE payment_via ( id uuid PRIMARY KEY, provider text NOT NULL, keys hstore NOT NULL DEFAULT '', is_default boolean NOT NULL DEFAULT FALSE, EXCLUDE (is_default USING AND)
);
However, this is a syntax error. There is always:
CREATE TABLE payment_via ( id uuid PRIMARY KEY, provider text NOT NULL, keys hstore NOT NULL DEFAULT '', is_default boolean NOT NULL DEFAULT FALSE, EXCLUDE (is_default USING =) WHERE (is_default)
);
but this seems awkward and I was hoping there was some way to use AND as an operator.