pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDublicates pairs in a table.
10+ messages / 5 participants
[nested] [flat]
* Dublicates pairs in a table.
@ 2002-09-16 14:51 Ries van Twisk <ries@jongert.nl>
2002-09-16 15:08 ` Re: Dublicates pairs in a table. Richard Huxton <dev@archonet.com>
2002-09-25 01:10 ` Re: Dublicates pairs in a table. Kevin Houle <kevin@houle.org>
0 siblings, 2 replies; 10+ messages in thread
From: Ries van Twisk @ 2002-09-16 14:51 UTC (permalink / raw)
To: pgsql-sql
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);
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'); -- Allowed
INSET INTO test (c1,c2) VALUES('b', 'b'); -- Allowed
INSET INTO test (c1,c2) VALUES('b', 'c'); -- Allowed
INSET INTO test (c1,c2) VALUES('a', 'a'); -- Not allowed since we already
have a duplicate ('a', 'a') pair
INSET INTO test (c1,c2) VALUES('b', 'c'); -- Not allowed since we already
have a duplicate ('b', 'c') pair
etc. etc. I think you get the idea...
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.
I'm looking for performance technical answers here thus speed vs memory
comparisons. Ofcourse a pointer to a URL will also do..
Best Regards,
Ries van Twisk
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dublicates pairs in a table.
2002-09-16 14:51 Dublicates pairs in a table. Ries van Twisk <ries@jongert.nl>
@ 2002-09-16 15:08 ` Richard Huxton <dev@archonet.com>
2002-09-17 06:36 ` Re: Dublicates pairs in a table. Ries van Twisk <ries@jongert.nl>
1 sibling, 1 reply; 10+ messages in thread
From: Richard Huxton @ 2002-09-16 15:08 UTC (permalink / raw)
To: ries@jongert.nl; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dublicates pairs in a table.
2002-09-16 14:51 Dublicates pairs in a table. Ries van Twisk <ries@jongert.nl>
2002-09-16 15:08 ` Re: Dublicates pairs in a table. Richard Huxton <dev@archonet.com>
@ 2002-09-17 06:36 ` Ries van Twisk <ries@jongert.nl>
2002-09-17 10:07 ` Re: Dublicates pairs in a table. Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ries van Twisk @ 2002-09-17 06:36 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dublicates pairs in a table.
2002-09-16 14:51 Dublicates pairs in a table. Ries van Twisk <ries@jongert.nl>
2002-09-16 15:08 ` Re: Dublicates pairs in a table. Richard Huxton <dev@archonet.com>
2002-09-17 06:36 ` Re: Dublicates pairs in a table. Ries van Twisk <ries@jongert.nl>
@ 2002-09-17 10:07 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 10+ messages in thread
From: Richard Huxton @ 2002-09-17 10:07 UTC (permalink / raw)
To: ries@jongert.nl; pgsql-sql
On Tuesday 17 Sep 2002 7:36 am, Ries van Twisk wrote:
> 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.
I'd go with the UNIQUE INDEX. You're unlikely to be able to write code any
faster than the built-in indexing code. It's also simpler and easier for
someone else to see what's going on.
The only time the index will slow you down is on an insert/delete when the
index needs to be updated as well as the table. However, without the index
you'll need to scan the table to check for duplicates. For thousands of
records that can only be slower.
To put your mind at ease, I'd put together some test data and try it - nothing
like seeing some real evidence.
HTH
- Richard Huxton
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dublicates pairs in a table.
2002-09-16 14:51 Dublicates pairs in a table. Ries van Twisk <ries@jongert.nl>
@ 2002-09-25 01:10 ` Kevin Houle <kevin@houle.org>
2002-09-27 12:54 ` Re: Dublicates pairs in a table. Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 10+ messages in thread
From: Kevin Houle @ 2002-09-25 01:10 UTC (permalink / raw)
To: Ries van Twisk <ries@jongert.nl>
Ries van Twisk wrote:
> 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);
>
> 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'); -- Allowed
> INSET INTO test (c1,c2) VALUES('b', 'b'); -- Allowed
> INSET INTO test (c1,c2) VALUES('b', 'c'); -- Allowed
> INSET INTO test (c1,c2) VALUES('a', 'a'); -- Not allowed since we already
> have a duplicate ('a', 'a') pair
> INSET INTO test (c1,c2) VALUES('b', 'c'); -- Not allowed since we already
> have a duplicate ('b', 'c') pair
>
> etc. etc. I think you get the idea...
I have the same issue with a table that currently holds well
over 600,000 rows. The case you left out is this:
INSERT INTO test (c1,c2) VALUES('a','c');
INSERT INTO test (c1,c2) VALUES('c','a');
I want that to fail, but I haven't been able to get it to fail
using unique indexes. I presume ordering is significant. Instead,
I am doing a SELECT prior to insert to insure the pair doesn't
already exist. If you've been able to get order-independent
pairs restricted to being unique using indexes, I'd like to know
about it. :-)
Kevin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dublicates pairs in a table.
2002-09-16 14:51 Dublicates pairs in a table. Ries van Twisk <ries@jongert.nl>
2002-09-25 01:10 ` Re: Dublicates pairs in a table. Kevin Houle <kevin@houle.org>
@ 2002-09-27 12:54 ` Richard Huxton <dev@archonet.com>
2002-09-27 16:09 ` Re: Dublicates pairs in a table. Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-09-27 16:17 ` Re: Dublicates pairs in a table. Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 2 replies; 10+ messages in thread
From: Richard Huxton @ 2002-09-27 12:54 UTC (permalink / raw)
To: Kevin Houle <kevin@houle.org>; pgsql-sql
On Wednesday 25 Sep 2002 2:10 am, Kevin Houle wrote:
> I have the same issue with a table that currently holds well
> over 600,000 rows. The case you left out is this:
>
> INSERT INTO test (c1,c2) VALUES('a','c');
> INSERT INTO test (c1,c2) VALUES('c','a');
>
> I want that to fail, but I haven't been able to get it to fail
> using unique indexes. I presume ordering is significant. Instead,
> I am doing a SELECT prior to insert to insure the pair doesn't
> already exist. If you've been able to get order-independent
> pairs restricted to being unique using indexes, I'd like to know
> about it. :-)
Functional indexes sir - define a function that puts the columns into a sorted
order.
richardh=> CREATE TABLE foo (a text, b text);
CREATE
richardh=> CREATE UNIQUE INDEX foo_both_uniq ON foo ( ord_fn(a,b) );
ERROR: DefineIndex: index function must be marked iscachable
richardh=> \i ordfn.txt
DROP
CREATE
richardh=> CREATE UNIQUE INDEX foo_both_uniq ON foo ( ord_fn(a,b) );
CREATE
richardh=> insert into foo values ('aa','bb');
INSERT 332596 1
richardh=> insert into foo values ('aa','cc');
INSERT 332597 1
richardh=> insert into foo values ('bb','aa');
ERROR: Cannot insert a duplicate key into unique index foo_both_uniq
richardh=> insert into foo values ('aa','bb');
ERROR: Cannot insert a duplicate key into unique index foo_both_uniq
Function defined as:
CREATE FUNCTION ord_fn (text,text) RETURNS text AS '
SELECT (CASE
WHEN $1 < $2
THEN $1 || $2
ELSE $2 || $1
END) as t;
' LANGUAGE SQL WITH (iscachable);
--
Richard Huxton
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dublicates pairs in a table.
2002-09-16 14:51 Dublicates pairs in a table. Ries van Twisk <ries@jongert.nl>
2002-09-25 01:10 ` Re: Dublicates pairs in a table. Kevin Houle <kevin@houle.org>
2002-09-27 12:54 ` Re: Dublicates pairs in a table. Richard Huxton <dev@archonet.com>
@ 2002-09-27 16:09 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 10+ messages in thread
From: Stephan Szabo @ 2002-09-27 16:09 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Kevin Houle <kevin@houle.org>; pgsql-sql
On Fri, 27 Sep 2002, Richard Huxton wrote:
> On Wednesday 25 Sep 2002 2:10 am, Kevin Houle wrote:
> > I have the same issue with a table that currently holds well
> > over 600,000 rows. The case you left out is this:
> >
> > INSERT INTO test (c1,c2) VALUES('a','c');
> > INSERT INTO test (c1,c2) VALUES('c','a');
> >
> > I want that to fail, but I haven't been able to get it to fail
> > using unique indexes. I presume ordering is significant. Instead,
> > I am doing a SELECT prior to insert to insure the pair doesn't
> > already exist. If you've been able to get order-independent
> > pairs restricted to being unique using indexes, I'd like to know
> > about it. :-)
>
> Function defined as:
> CREATE FUNCTION ord_fn (text,text) RETURNS text AS '
> SELECT (CASE
> WHEN $1 < $2
> THEN $1 || $2
> ELSE $2 || $1
> END) as t;
> ' LANGUAGE SQL WITH (iscachable);
Note, that for a final system, you'll may want to also add a
delimiter that doesn't show up in $1 or $2 if ('a','ab') and
('aa','b') aren't supposed to cause an error.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dublicates pairs in a table.
2002-09-16 14:51 Dublicates pairs in a table. Ries van Twisk <ries@jongert.nl>
2002-09-25 01:10 ` Re: Dublicates pairs in a table. Kevin Houle <kevin@houle.org>
2002-09-27 12:54 ` Re: Dublicates pairs in a table. Richard Huxton <dev@archonet.com>
@ 2002-09-27 16:17 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-09-27 16:37 ` Re: Dublicates pairs in a table. Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 10+ messages in thread
From: Jean-Luc Lachance @ 2002-09-27 16:17 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Kevin Houle <kevin@houle.org>; pgsql-sql
What's wrong with
CREATE UNIQUE INDEX foo_both_uniq ON foo(a,b);
???
Richard Huxton wrote:
>
> On Wednesday 25 Sep 2002 2:10 am, Kevin Houle wrote:
> > I have the same issue with a table that currently holds well
> > over 600,000 rows. The case you left out is this:
> >
> > INSERT INTO test (c1,c2) VALUES('a','c');
> > INSERT INTO test (c1,c2) VALUES('c','a');
> >
> > I want that to fail, but I haven't been able to get it to fail
> > using unique indexes. I presume ordering is significant. Instead,
> > I am doing a SELECT prior to insert to insure the pair doesn't
> > already exist. If you've been able to get order-independent
> > pairs restricted to being unique using indexes, I'd like to know
> > about it. :-)
>
> Functional indexes sir - define a function that puts the columns into a sorted
> order.
>
> richardh=> CREATE TABLE foo (a text, b text);
> CREATE
> richardh=> CREATE UNIQUE INDEX foo_both_uniq ON foo ( ord_fn(a,b) );
> ERROR: DefineIndex: index function must be marked iscachable
> richardh=> \i ordfn.txt
> DROP
> CREATE
> richardh=> CREATE UNIQUE INDEX foo_both_uniq ON foo ( ord_fn(a,b) );
> CREATE
> richardh=> insert into foo values ('aa','bb');
> INSERT 332596 1
> richardh=> insert into foo values ('aa','cc');
> INSERT 332597 1
> richardh=> insert into foo values ('bb','aa');
> ERROR: Cannot insert a duplicate key into unique index foo_both_uniq
> richardh=> insert into foo values ('aa','bb');
> ERROR: Cannot insert a duplicate key into unique index foo_both_uniq
>
> Function defined as:
> CREATE FUNCTION ord_fn (text,text) RETURNS text AS '
> SELECT (CASE
> WHEN $1 < $2
> THEN $1 || $2
> ELSE $2 || $1
> END) as t;
> ' LANGUAGE SQL WITH (iscachable);
>
> --
> Richard Huxton
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dublicates pairs in a table.
2002-09-16 14:51 Dublicates pairs in a table. Ries van Twisk <ries@jongert.nl>
2002-09-25 01:10 ` Re: Dublicates pairs in a table. Kevin Houle <kevin@houle.org>
2002-09-27 12:54 ` Re: Dublicates pairs in a table. Richard Huxton <dev@archonet.com>
2002-09-27 16:17 ` Re: Dublicates pairs in a table. Jean-Luc Lachance <jllachan@nsd.ca>
@ 2002-09-27 16:37 ` Richard Huxton <dev@archonet.com>
2002-09-27 17:23 ` Re: Dublicates pairs in a table. Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 1 reply; 10+ messages in thread
From: Richard Huxton @ 2002-09-27 16:37 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: pgsql-sql
On Friday 27 Sep 2002 5:17 pm, Jean-Luc Lachance wrote:
> What's wrong with
> CREATE UNIQUE INDEX foo_both_uniq ON foo(a,b);
> ???
Because he specifically wanted values of ('a','b') and ('b','a') to be treated
as equivalent (see quote).
> > > INSERT INTO test (c1,c2) VALUES('a','c');
> > > INSERT INTO test (c1,c2) VALUES('c','a');
Note Stephen Szabo's observation that I'd missed the obvious need for some
separator so ('a','ab') is different from ('aa','b') - Doh!
- Richard Huxton
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dublicates pairs in a table.
2002-09-16 14:51 Dublicates pairs in a table. Ries van Twisk <ries@jongert.nl>
2002-09-25 01:10 ` Re: Dublicates pairs in a table. Kevin Houle <kevin@houle.org>
2002-09-27 12:54 ` Re: Dublicates pairs in a table. Richard Huxton <dev@archonet.com>
2002-09-27 16:17 ` Re: Dublicates pairs in a table. Jean-Luc Lachance <jllachan@nsd.ca>
2002-09-27 16:37 ` Re: Dublicates pairs in a table. Richard Huxton <dev@archonet.com>
@ 2002-09-27 17:23 ` Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 0 replies; 10+ messages in thread
From: Jean-Luc Lachance @ 2002-09-27 17:23 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
Oh, sorry I missed that.
Still if C1 and C2 are interchangable, a rule could force C1 <= C2 and
swap them if necessary.
Richard Huxton wrote:
>
> On Friday 27 Sep 2002 5:17 pm, Jean-Luc Lachance wrote:
> > What's wrong with
> > CREATE UNIQUE INDEX foo_both_uniq ON foo(a,b);
> > ???
>
> Because he specifically wanted values of ('a','b') and ('b','a') to be treated
> as equivalent (see quote).
>
> > > > INSERT INTO test (c1,c2) VALUES('a','c');
> > > > INSERT INTO test (c1,c2) VALUES('c','a');
>
> Note Stephen Szabo's observation that I'd missed the obvious need for some
> separator so ('a','ab') is different from ('aa','b') - Doh!
>
> - Richard Huxton
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2002-09-27 17:23 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-16 14:51 Dublicates pairs in a table. Ries van Twisk <ries@jongert.nl>
2002-09-16 15:08 ` Richard Huxton <dev@archonet.com>
2002-09-17 06:36 ` Ries van Twisk <ries@jongert.nl>
2002-09-17 10:07 ` Richard Huxton <dev@archonet.com>
2002-09-25 01:10 ` Kevin Houle <kevin@houle.org>
2002-09-27 12:54 ` Richard Huxton <dev@archonet.com>
2002-09-27 16:09 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-09-27 16:17 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-09-27 16:37 ` Richard Huxton <dev@archonet.com>
2002-09-27 17:23 ` Jean-Luc Lachance <jllachan@nsd.ca>
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