[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] locking/insert into table and transactions
From:       Lincoln Yeoh <lylyeoh () mecomb ! com>
Date:       1999-11-26 9:04:09
[Download RAW message or body]

Hi,

I'd like to prevent duplicate ids from being inserted into a table. I can
let the database enforce it by using UNIQUE or PRIMARY KEY. But assuming I
prefer to catch such things with the application, what would be the best
way of doing it?

The only way I figured to do it was to use:
begin;
lock table accounts;
select count(*) from accounts where id=$number;
 if count=0, insert into accounts (id,etc) values ($number,$etc)
commit;

Is this a good idea? Or is it much better and faster to let the database
catch things?

Is it faster to use "select count(*) from accounts" or "select id from
accounts"? 

Apparently count(*) has some speed optimizations in MySQL. So wondering if
there are similar things in Postgres.

Thanks,

Link.


************

[prev in list] [next in list] [prev in thread] [next in thread]