Handle failed UNIQUE constraint using PHP PDO?

Question

I'm building a simple web app using PostgreSQL and PHP (using PDO). I've got a UNIQUE constraint on one column, and now I'm trying to come up with how best to handle an attempt to insert a duplicate into that database table.

Do I have to explicitly run a SELECT statement first and check if the value that I'm about to insert already exists, or can I handle it using PDO exceptions?

Right now I'm doing

    try{
        $stmt = $pdo->prepare("INSERT INTO table (column) VALUES (?) RETURNING id;");
        $stmt->execute( array('duplicate') );
    }
    catch( PDOException $e ){
        print $e;
    } 

which gives me

exception 'PDOException' with message 'SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "column"
DETAIL:  Key (column)=(duplicate) already exists.'

I'm thinking it would be cleaner to just be able to pick up the error when it occurs, rather than first checking if the value already exists (which sort of defeats the benefit of having the UNIQUE constraint in the first place).

In the end, I'd like to present a user friendly error message to the user, something like "The username already exists".

I suppose a hackish way would be to check the exception details and generate my friendly error messages based on that, but can I trust that the details (like SQLSTATE[23505] or code=7) won't change in the future?

Can I build a better SQL statement that returns something useful on duplicate constraint failure (and the id on success, like it is now)?

EDIT

Creating a stored procedure might be one solution:

CREATE OR REPLACE FUNCTION my_insert(a_value text)
RETURNS text AS $$
DECLARE
 retval text;
BEGIN
    INSERT INTO table (column) VALUES (a_value) RETURNING id INTO retval;
    RETURN retval;

    EXCEPTION
        WHEN unique_violation THEN
            retval = 'duplicate';
            RETURN retval;
END;
$$ LANGUAGE plpgsql;

, then checking if the return value is 'duplicate' or an id.

While it doesn't let me utilize PHP exceptions to determine what went wrong and generate a friendly error message, it does eliminate the need for a separate SELECT-statement. Still not fully satisfied with this solution, so if you know of something even more elegant...

Answer 1

The SQLSTATE codes are defined in the SQL standard and are unlikely to change. They might not always provide enough information by themselves (hence the "DETAIL" section), but can be considered reliable.