Here is a simple solution:
Create two tables -- One with only validated email the other not
validated.
"Thomas T. Thai" wrote:
>
> On Sat, 2 Nov 2002, Tino Wildenhain wrote:
>
> > Hi Thomas,
> >
> > --On Samstag, 2. November 2002 00:58 -0600 "Thomas T. Thai"
> > <tom@minnesota.com> wrote:
> >
> > > I have two columns in a table:
> > >
> > > email varchar(64)
> > > verified boolean
> > >
> > > How do I make a check for unique email that is verified while allowing for
> > > non-verified emails to be not unique?
> >
> > Before thinking of a solution for this in PG, I dont
> > see why you need this requirement in the first place:
> > whay should the very same e-mail be both verified and
> > unveryfied? And even more - why should be more then one
> > row telling you this very same e-mail is unverified?
> >
> > Is this only an example which does not serve very well
> > or is there a bigger picture?
>
> It's for a user authentication system. User registers, but I want to
> verify their email address before allowing them access. There are more
> fields in that table than what I showed (like userid, etc.).
>
> If I don't verify their email address, then anyone can sign up and use
> someone else's email address, there by preventing the ligitimate owner of
> that email address to register in the system. Once the email address is
> verified, I don't want other users trying to use that email address again.
> I'm currently doing a SELECT to check the conditions, but I wanted a
> backup solutions so it's more transaction safe.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
