Re: Duplicate key insert question - Mailing list pgsql-general

> -----Original Message-----
> From: Jean-Christian Imbeault [mailto:jc@mega-bucks.co.jp]
> Sent: Tuesday, July 01, 2003 5:21 PM
> To: Dann Corbit
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Duplicate key insert question
>
>
> Dann Corbit wrote:
> >
> > SQL*Server has a nice feature for this.
>
> Ok ... is there a way to achieve the same effect in postgres?

No.

MySQL is broken, unless they have some extension like SQL*Server.  A
duplicate insertion into a unique index must raise an error.

(c)ISO/IEC ISO/IEC 9075-1:1999 (E)
4.6 SQL-schema objects
4.6.6.3 Table constraints
A table constraint is an integrity constraint associated with a single
base table.
A table constraint is either a unique constraint, a primary key
constraint, a referential constraint, or a check constraint.
A unique constraint specifies one or more columns of the table as unique
columns. A unique constraint is satisfied if and only if no two rows in
a table have the same non-null values in the
unique columns.
A primary key constraint is a unique constraint that specifies PRIMARY
KEY. A primary key constraint is satisfied if and only if no two rows in
a table have the same non-null values in the unique columns and none of
the values in the specified column or columns are the null value.
A referential constraint specifies one or more columns as referencing
columns and corresponding referenced columns in some (not necessarily
distinct) base table, referred to as the referenced table.
Such referenced columns are the unique columns of some unique constraint
of the referenced table.
A referential constraint is always satisfied if, for every row in the
referencing table, the values of the referencing columns are equal to
those of the corresponding referenced columns of some row in the
referenced table. If null values are present, however, satisfaction of
the referential constraint depends on the treatment specified for nulls
(known as the match type).
Referential actions may be specified to determine what changes are to be
made to the referencing table if a change to the referenced table would
otherwise cause the referential constraint to be violated.
A table check constraint specifies a search condition. The constraint is
violated if the result of the search condition is false for any row of
the table (but not if it is unknown).

4.7 Integrity constraints and constraint checking
4.7.1 Constraint checking
There are two kinds of schema object that describe constraints:
assertions and table constraints (including domain constraints of any
domains on which columns of that table may be based), and they are
checked in the same way.
Every constraint is either deferrable or not deferrable.
In every SQL-session, every constraint has a constraint mode that is a
property of that SQL-session.
Each constraint has a (persistent) default constraint mode, with which
the constraint starts each SQL-transaction in each SQL-session.
A constraint mode is either deferred or immediate, and can be set by an
SQL-statement, provided the constraint is deferrable.
When a transaction is initiated, the constraint mode of each constraint
is set to its default.
On completion of execution of every SQL-statement, every constraint is
checked whose constraint mode is immediate.
Before termination of a transaction, every constraint mode is set to
immediate (and therefore checked).