Re: duplicates

----- Original Message -----
From: "Tsirkin Evgeny" <tsurkin@mail.jct.ac.il>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Cc: <pgsql-admin@postgresql.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:
Begin
Delete query -- if here is error you should run a Rollback and don't run any
insert.
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
there.


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.