1

I have a composite UNIQUE set of columns in my table. Therefore if I insert into the table where the unique key is violated, Postgresql returns and error and my PHP script can read this error.

When inserting, instead of doing this:

SELECT id FROM table WHERE col1='x' and col2='y'
(if no rows)
INSERT INTO table...
(else if rows are found)
UPDATE table SET...

I prefer to use:

INSERT INTO table...
(if error occurred then attempt to UPDATE)
UPDATE table SET...

The kind of error returned from the above would be "ERROR: duplicate key value violates unique constraint "xxxxxxxx_key"

However, there is no point doing an UPDATE if the INSERT failed for some other reason, such as invalid data. Is there a way of "knowing" (from PHP/Postgres) if the error actually failed from this duplicate key issue, rather than invalid data? I'm just curious. Performing an UPDATE also would return an error anyway if the data were invalid, but what would you say is best practice?

Many thanks!

rishijd
  • 1,294
  • 4
  • 15
  • 32
  • 1
    As an alternative you might consider applying some of the strategies for doing an "UPSERT": http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql/8702291 –  May 09 '12 at 14:22

2 Answers2

3

Just check the error message to see what kind of error you have. pg_result_error_field() shows it all. Check the PGSQL_DIAG_SQLSTATE and the PostgreSQL manual for the details.

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
3

You might want to look into this example in the official documentation.

You're free to add more WHEN EXCEPTION ... THEN handlers, list of available errors can also be found in the documentation.

Although in the example above the function will cause an exception in case any other error appears, except the unique_violation one, which is treated specially.

vyegorov
  • 21,787
  • 7
  • 59
  • 73