Re: SQL Programming Question

  On 09/11/10 12:26 PM, Merlin Moncure wrote:
> On Fri, Sep 10, 2010 at 11:07 PM,<tony@exquisiteimages.com>  wrote:
>> Using PostgreSQL I can't open a table and do seeks against an index. I
>> could do a select against the database and see if 0 records are returned,
>> but that seems to take more time than doing a seek on an index. Is there a
>> more SQL friendly way of handling this task?
> I come a foxpro background too, and I can tell you for sure postgres
> indexes should give you similar performance to what you're used to.
> Do you have an index on the table?
>
>

again, the table should have either a primary key on this field, or a
unique constraint (a primary key is by definition unique)

BEGIN transaction
     INSERT row
     If Constraint Violation,
         ROLLBACK transaction
         INSERT row into exceptions table
     ELSE
     COMMIT transaction

(thats pseudocode)

if you want to do it faster, you batch multiple inserts in a single
transaction, using savepoints before each insert and rollback the
savepoint on the exception.