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> (raw)
In-Reply-To: <200209161608.27501.dev@archonet.com>
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
>
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: ries@jongert.nl
Subject: Re: Dublicates pairs in a table.
In-Reply-To: <000201c25e14$921a9e10$6500000a@IT000>
* 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