Reply-To: <ries@jongert.nl>
From: "Ries van Twisk" <ries@jongert.nl>
To: <pgsql-sql@postgresql.org>
Subject: Re: Dublicates pairs in a table.
Date: Tue, 17 Sep 2002 08:36:36 +0200
Message-ID: <000201c25e14$921a9e10$6500000a@IT000>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
In-reply-to: <200209161608.27501.dev@archonet.com>
Importance: Normal

Richard,

do you suggest using a stored procedure to handle this? I do expect that the
table will be large (for me large is a around 10000-20000 records, the table
as more columns but I only need the restriction on c1 & c2) but I don’t
expect lots of inserts and deletes. Data entry will be done using a regular
user but of course a update needs to be done within a acceptable time so the
user does not get annoyed.

best regards,
Ries van Twisk


> -----Oorspronkelijk bericht-----
> Van: Richard Huxton [mailto:dev@archonet.com]
> Verzonden: maandag 16 september 2002 17:08
> Aan: ries@jongert.nl; pgsql-sql@postgresql.org
> Onderwerp: Re: [SQL] Dublicates pairs in a table.
>
>
> 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 table:
> > 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 use
> the index, otherwise you'll have to scan the table and check
> for another copy
> anyway.
>
> HTH
>
> - Richard Huxton
>