Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fATLBBm28646 for ; Thu, 29 Nov 2001 16:11:11 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fATLB9k23666; Thu, 29 Nov 2001 16:11:09 -0500 (EST) To: "G. Anthony Reina" cc: pgsql-sql@postgresql.org Subject: Re: Can I CONSTRAIN a particular value to be UNIQUE? In-reply-to: <3C06A32C.18856CA6@nsi.edu> References: <23371.1007066300@sss.pgh.pa.us> <3C06A32C.18856CA6@nsi.edu> Comments: In-reply-to "G. Anthony Reina" message dated "Thu, 29 Nov 2001 13:05:48 -0800" Date: Thu, 29 Nov 2001 16:11:09 -0500 Message-ID: <23663.1007068269@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200111/421 X-Sequence-Number: 5803 "G. Anthony Reina" writes: > 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? Not at all, but if those are the only two possible values of "success" then it would seem this is the same as one non-partial unique index. If you're thinking of allowing other, unconstrained success values then this could make sense. > 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? We try to avoid forcing initdb after we start the beta cycle, but we make no guarantees until we go final. FWIW, at this point I doubt we'll need to do that. regards, tom lane