Re: Is a right behaviour on inserting a duplicate key? - Mailing list pgsql-jdbc

> It seems to me that inserting a duplicate user would result an invalide
retured value. Instead, the action leads to a SQL
> expection as
>
> ERROR:  Cannot insert a duplicate key into unique index pk_signon
>
> Can some clarify the 7.3.1 JDBC driver specification or implement in this
regards?

I think throwing an exception is the correct thing to do and is consistent
with Oracle.  The only downside is that detecting a duplicate key, something
that seems so ordinary in the database world that one has to wonder why a
special exception was not devised by JDBC to ensure a portable way to detect
it.  We use some hacked code like the following to do so in our connection
pool code (obviously, only the public method is invoked by our mainline
code):

    protected boolean isPostgresqlDuplicateKey(SQLException e)
    {
        String msg = e.getMessage();
        if ( msg == null )
            return false;
        return msg.indexOf("duplicate key") > 0;
    }

    protected boolean isOracleDuplicateKey(SQLException e)
    {
        return e.getErrorCode() == 1;
    }

    public boolean isDuplicateKey(SQLException e)
    {
        if ( e == null )
            return false;
        if ( isOracle() )
            return isOracleDuplicateKey(e);
        return isPostgresqlDuplicateKey(e);
    }