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...

http://stackoverflow.com/a/16428230/285587 (and other answers). After insert you can just check the number of affected rows. — Your Common Sense, Feb 22 '16 at 16:16
You may use `INSERT ... ON CONFLICT ...` http://www.postgresql.org/docs/current/static/sql-insert.html — Michas, Feb 22 '16 at 16:22
@Michas That's a good tip, unfortunately it seems my version of PostgreSQL (9.4.5) doesn't support ON CONFLICT. — Magnus W, Feb 22 '16 at 16:55

score 2 · Accepted Answer · answered Feb 22 '16 at 16:15

2

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.

answered Feb 22 '16 at 16:15

Richard Huxton

18,412
3
31
43

Sounds like the way to go then, thanks! I just wish the exception info would tell me which table and/or column triggered the exception as well, that'd be nice when having multiple statements inside the same try-block. I mean, it's there in the DETAILS-section, but I guess the formatting could change from one version of PostgreSQL to another, right? – Magnus W Feb 22 '16 at 20:13
Although it could, it's unlikely to (but bear in mind locale settings could change the language of the error message). However, even if PostgreSQL provided separate fields to identify table, sequence etc then PDO probably wouldn't support it, since it would be different from other RDBMS. – Richard Huxton Feb 22 '16 at 21:26

Handle failed UNIQUE constraint using PHP PDO?

1 Answers1