Обсуждение: Unique cosntraint based on contents of a field?

Is there a way to create a unique constraint based on the content of a
field? For instance, say you have an integer field where you only want
one record with the number 1 in that field but there can be many records
with say the number 2 or any other single number in that field.

--
Thanks,

Warren Bell

On Wed, Apr 7, 2010 at 10:30 PM, Warren Bell <warrenbell2@gmail.com> wrote:
> Is there a way to create a unique constraint based on the content of a
> field? For instance, say you have an integer field where you only want one
> record with the number 1 in that field but there can be many records with
> say the number 2 or any other single number in that field.
>

create unique index unique_field_idx
on table1(field)
where field = 1;

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova wrote:
> On Wed, Apr 7, 2010 at 10:30 PM, Warren Bell <warrenbell2@gmail.com> wrote:
> > Is there a way to create a unique constraint based on the content of a
> > field? For instance, say you have an integer field where you only want one
> > record with the number 1 in that field but there can be many records with
> > say the number 2 or any other single number in that field.
> >
>
> create unique index unique_field_idx
> on table1(field)
> where field = 1;

Wow, great idea.  I never thought of partial indexes as a method for
unique qualification.  I am glad to see our documentation mentions this
usage:

    http://www.postgresql.org/docs/8.4/static/sql-createindex.html

    Another possible application is to use WHERE with UNIQUE to enforce
    uniqueness over a subset of a table.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com