Skip site navigation (1) Skip section navigation (2)

Re: duplicates

From: "Andrei Bintintan" <klodoma(at)ar-sd(dot)net>
To: "Tsirkin Evgeny" <tsurkin(at)mail(dot)jct(dot)ac(dot)il>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: duplicates
Date: 2004-09-06 10:04:07
Message-ID: 00e801c493f8$da7f12f0$0b00a8c0@forge (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
----- Original Message -----
From: "Tsirkin Evgeny" <tsurkin(at)mail(dot)jct(dot)ac(dot)il>
To: "Andrei Bintintan" <klodoma(at)ar-sd(dot)net>
Cc: <pgsql-admin(at)postgresql(dot)org>
Sent: Monday, September 06, 2004 12:19 PM
Subject: Re: [ADMIN] duplicates

> yes i understand that i can create a primary key/unique etc...
> however my question is if i have to understand if and why
> the i got duplicate rows inserted.
> so here is the picture:
> an application is deleting rows and inserting right after that
> new ones some of which are the same as the old one,and i am getting

Maybe you're inserting not only one time that dupplicate pair. In your table
definition nothing stopes this.

> duplicates !Don't transaction should prevent this by not letting
> insert ot do something in case the delete did not succeed?

Only if you check your query's result and you say "rollback" to the
transaction in case of an error.

> Another option is that i have to clients deleting and then inserting the
> same thing into table ,but again should not transaction prevent duplicates
> in that case ?

No, perhaps you're missunderstanding transactions. Transactions don't
prevent anything, only if you say so.

For ex:
Delete query -- if here is error you should run a Rollback and don't run any
If the delete query succedes then you should run the inserts... etc etc.
At the end of the transaction you should  Commit ONLY if everything worked
as you wanted!!!!

The uniqe indexes helps you to prevent double inserts into the database.
For example:
CREATE UNIQUE INDEX table_column_uniqueidx ON table(studentid, groupid);
will assure you that you have only one ROW with this pair of data. By assure
I mean, that if you have the pair (1111, 22) in the table, and you want to
insert another (1111, 22) pair into the table, the insert query will fail.

In the transactions you will have to check the result of every
delete/insert/update query so that you know there was a failuire.

You can also look in the database log file, and see exaclty what happened

Hope this helps.

> > Use something like this:
> >
> >  CREATE TABLE schedule (
> >     id serial PRIMARY KEY,
> >     studentid    decimal(9),
> >     groupid      decimal(10),
> >     maslulsignid decimal(7),
> >     tfusot        varchar(29)
> >  );
> >
> --
> Evgeny.

In response to

pgsql-admin by date

Next:From: Kostis MentzelosDate: 2004-09-06 10:08:30
Subject: Re: duplicates
Previous:From: Kostis MentzelosDate: 2004-09-06 09:40:55
Subject: Re: duplicates

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group