pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: G. Anthony Reina <reina@nsi.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Can I CONSTRAIN a particular value to be UNIQUE?
Date: Thu, 29 Nov 2001 13:05:48 -0800
Message-ID: <3C06A32C.18856CA6@nsi.edu> (raw)
References: <f40d3195.0111291101.3b2a5074@posting.google.com>
	<23371.1007066300@sss.pgh.pa.us>

Tom Lane wrote:

> 2. In PG 7.2, partial indexes work again, so you could do
>
>         CREATE UNIQUE INDEX ... (subject, target, trial) WHERE success = 1;
>
> Which of these is better depends on how many of each sort of row
> you expect to have, and whether you'll be doing any queries wherein
> you could use an index on subject/target/trial for the non-success
> rows.
>

Oooooo! Option #2 seems very nice for what I'm trying to do. Would there be
any problem with me using

CREATE UNIQUE INDEX pk1 ON table1 (subject, target, trial) WHERE success = 1;
CREATE UNIQUE INDEX pk0 ON table1 (subject, target, trial) WHERE success = 0;

i.e. 2 partial indicies on the same table?


I know PG7.2 is still in beta, but it might be worthwhile for me to make the
jump from 7.1.3 for this feature. If I upgrade to the beta, will I have to do
a dump/restore again when the official 7.2 release comes out? I know I'll have
to do one for the beta anyway, but I'd hate to have to do it twice (especially
since the 7.2 official release will probably come out soon).

Thanks Tom.
-Tony





view thread (7+ messages)  latest in thread

Message-ID: <3C06A32C.18856CA6@nsi.edu>
Permalink:  ../3C06A32C.18856CA6@nsi.edu/
Also on:    postgresql.org/message-id/3C06A32C.18856CA6@nsi.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: reina@nsi.edu, tgl@sss.pgh.pa.us
  Subject: Re: Can I CONSTRAIN a particular value to be UNIQUE?
  In-Reply-To: <3C06A32C.18856CA6@nsi.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox