Search Postgresql Archives

Re: There can be only one

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Create a partial unique index on is_default.

Am 11. Oktober 2015 09:41:08 MESZ, schrieb Jason Dusek <jason.dusek@xxxxxxxxx>:

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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux