General Bug Report: SQL Insert result code inconsistent with JDBC

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name        : Thomas Reinke
Your email address    : reinke@e-softinc.com

Category        : unknown
Severity        : non-critical

Summary: SQL Insert result code inconsistent with JDBC

System Configuration
- --------------------
  Operating System   : Linux 2.0.30 RedHat

  PostgreSQL version : 6.3

  Compiler used      : gcc 2.7.1

Hardware:
- ---------
Pentium, 128 Meg
Linux www4 2.0.30 #1 Tue Apr 22 10:49:45 EDT 1997 i686 unknown

Versions of other tools:
- ------------------------
gmake

- --------------------------------------------------------------------------

Problem Description:
- --------------------
An attempt to add a row into a table with a unique index,
for which the primary key already exists in the table,
results in an ERROR. Realistically, this is not an
application error - it is a legitimate condition that
occurs in the real world. How does an application
distinguish (in any realistic way) between unable to
insert due to duplicate key, and unable to insert
due to (you pick the error - out of memory, etc.)

JDBC's documentation indicates that INSERT, UPDATE and
DELETE statements should return a count on the number
of row's affected, and throw an SQL exception on errors.
Would this particular scenario not be a case where
the INSERT count would be returned as zero?

- --------------------------------------------------------------------------

Test Case:
- ----------
create table test (fld1 varchar(10));
insert unique index test_pkey on test(fld1);
insert into test values('line1');
- --> INSERT TUPLEID 1
insert into test values ('line1');
ERROR:  Cannot insert a duplicate key into a unique index



- --------------------------------------------------------------------------

Solution:
- ---------
Apply the same heuristic to INSERTs as exists for
update's, delete's.
  E.g. Update, Delete with invalid table name returns
       error, so insert with invalid table name should
       provide error.
  E.g. Update, Delete with valid table name but no rows
       affected returns count of zero, so insert that
       cannot be done due to db rules should return
       count of zero.

- --------------------------------------------------------------------------