Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: <ries@jongert.nl>, <pgsql-sql@postgresql.org>
Subject: Re: Dublicates pairs in a table.
Date: Mon, 16 Sep 2002 16:08:27 +0100
User-Agent: KMail/1.4.1
References: <000201c25d90$87230c10$6500000a@IT000>
In-Reply-To: <000201c25d90$87230c10$6500000a@IT000>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200209161608.27501.dev@archonet.com>

On Monday 16 Sep 2002 3:51 pm, Ries van Twisk wrote:
> Dear guys/girls,
>
> I have a small question which I could not clearly find in the postgreSQL
> manual.
>
> if I create this table and index
> CRAEATE TABLE test (
> 	id	SERIAL,
> 	c1	VARCHAR(32),
> 	c2	VARCHAR(32),
> 	c3	VARCHAR(32)
> );
>
> CREATE UNIQUE INDEX test_idx ON test(id, c1,c2);

Close, try

CREATE UNIQUE INDEX test_idx ON test (c1,c2)

> what I try to archive here is that I don't want duplicate pais in my tabl=
e:
> example
>
> INSET INTO test (c1,c2) VALUES('a', 'a');	-- Not allowed since we already
> have a duplicate ('a', 'a') pair

> What I want to know is that if this is smart do do, or is there a other
> better way to make sure I don't insert duplicate pairs in my database.
> I'm not sure if a stored procedure is better in my case since I don't
> really need the index on columns c1 or c2.

Unless test is a very small table with lots of inserts/deletions I'd just u=
se=20
the index, otherwise you'll have to scan the table and check for another co=
py=20
anyway.

HTH

- Richard Huxton