3

On Appendix A: PostgreSQL error codes, it says:

For some types of errors, the server reports the name of a database object (a table, table column, data type, or constraint) associated with the error; for example, the name of the unique constraint that caused a unique_violation error. Such names are supplied in separate fields of the error report message so that applications need not try to extract them from the possibly-localized human-readable text of the message.

Bold emphasis mine. I have the following table:

CREATE TABLE recipes (
    id SERIAL,
    title TEXT UNIQUE NOT NULL,
    description TEXT NOT NULL,
    instructions TEXT NOT NULL,
    image BYTEA,
    CONSTRAINT recipes_pk PRIMARY KEY(id),
    CONSTRAINT title_unique UNIQUE(title)
);

When I try to insert a new row with a duplicate title, I get the following error message in pgAdmin3:

ERROR:  duplicate key value violates unique constraint "title_unique"
DETAIL:  Key (title)=(mytitle) already exists.

Or, using PHP:

["errorInfo"]=>
  array(3) {
    [0]=>
    string(5) "23505"
    [1]=>
    int(7)
    [2]=>
    string(117) "ERROR:  duplicate key value violates unique constraint "title_unique"
                 DETAIL:  Key (title)=(mytitle) already exists."
  }

According to the paragraph from the PostgreSQL documentation, shouldn't the constraint name title_unique be found in a separate field of the error info?

I'm using PostgreSQL 9.4.5.

  • 1
    What you see as an error message is an example of a 'from the possibly-localized human-readable text of the message'. See postgresql.org/docs/current/static/… – dezso Feb 22 '16 at 23:06
  • Yes I know... but shouldn't I see the separate fields of the error report message somewhere as well? – Magnus W Feb 22 '16 at 23:15
  • No, unless you consume them as shown in the linked page. This error message is produced by libpq, I believe. – dezso Feb 22 '16 at 23:41
  • 1
    The server is passing that data back to the client in a separate field. This can be seen by using "strace" on the client process and observing the raw response from the server. It is up the client's library to do something useful with that information, and apparently your php library does not. – jjanes Feb 24 '16 at 6:54
2

For instance, you can read these separate fields mentioned in your quote inside exception handlers in PL/pgSQL (as instructed in the manual, like @dezso commented):

DO
$$
DECLARE
   err_constraint text;

BEGIN
   INSERT INTO recipes (title) VALUES ('foo'), ('foo');  -- provoke unique violation

EXCEPTION
   WHEN SQLSTATE '23000' THEN  -- Class 23 — Integrity Constraint Violation
      GET STACKED DIAGNOSTICS err_constraint = CONSTRAINT_NAME;
      -- do something with it, for instance:
      RAISE NOTICE '%', err_constraint;
      RAISE;  -- raise original error

END;
$$

Result:

NOTICE:  title_unique

ERROR:  duplicate key value violates unique constraint "title_unique"
DETAIL:  Key (title)=(foo) already exists.
CONTEXT:  SQL statement "INSERT INTO recipes (title) VALUES ('foo')"
PL/pgSQL function inline_code_block line 5 at SQL statement

Aside: you declare the unique constraints twice in your DDL command. (To my surprise, this results in a single constraint in my test on pg 9.5). Either way, do it only once.

CREATE TABLE recipes (
    id SERIAL,
    title TEXT NOT NULL,  -- remove redundant clause
    description TEXT NOT NULL,
    instructions TEXT NOT NULL,
    image BYTEA,
    CONSTRAINT recipes_pk PRIMARY KEY(id),
    CONSTRAINT title_unique UNIQUE(title)
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.