Re: There can be only one

Create a partial unique index on is_default.

Am 11. Oktober 2015 09:41:08 MESZ, schrieb Jason Dusek <jason(dot)dusek(at)gmail(dot)com>:
>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.
>​

--
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.