Re: questions about query design

Alban Hertroys <alban(at)magproductions(dot)nl> writes:
> Ottavio Campana wrote:
>> What I want to do is a function inserting a new item into the table
>> ensuring that there is only one record in the table having a particular
>> description and at the same time the active field set to true (it might
>> seem stupid, but the application requires it).

> Assuming active can't be NULL, what's wrong with a UNIQUE constraint on
> (description, active)?

I think he does not want descriptions to be unique among non-active
entries. If so, the right thing is a partial unique index:

CREATE UNIQUE INDEX ... on (description) WHERE active;

regards, tom lane