PostgreSQL UNIQUE constraint error message

Question

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.

• András Váczi: 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/…
• Magnus: Yes I know... but shouldn't I see the separate fields of the error report message somewhere as well?
• András Váczi: No, unless you consume them as shown in the linked page. This error message is produced by libpq, I believe.
• jjanes: 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.

Answer 1

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)
);