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:[EMAIL PROTECTED]] > Verzonden: maandag 16 september 2002 17:08 > Aan: [EMAIL PROTECTED]; [EMAIL PROTECTED] > 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 > ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
