Node-postgres: Please, an example; how to catch a duplicate key error

[martinmurciego]

Greetings I want to ask you if someone has a complete example of how to catch a duplicate key error. While comment that I'm using bookshelf.js but I do not understand how to catch the errors of this module.

For example, how would the detail of this object of the exception be recovered?
{ error: duplicate key value violates unique constraint "ak_documento_personas" name: 'error', length: 241, severity: 'ERROR', code: '23505', detail: 'Key (tipo_documento_id, numero_documento)=(1, 11456963) already exists.', hint: undefined, position: undefined, internalPosition: undefined, internalQuery: undefined, where: undefined, schema: 'public', table: 'personas', column: undefined, dataType: undefined, constraint: 'ak_documento_personas', file: 'nbtinsert.c', line: '424', routine: '_bt_check_unique' }

[sehrope]

The code value of the Error object indicates the backend error code and a bunch of other details from the server. For unique key violations the code will always be 23505. Here's the full list of possible values (it's a lot): https://www.postgresql.org/docs/10/static/errcodes-appendix.html.

If you're using async/await and the promise interface then you can wrap your code in a regular try/catch block. To handle the error you would check if the code is "23505" and then also check the constraint property of the error to see the specific constraint that was violated. Handling the error would be specific to your app.

Note that if you have an active transaction you'd need to issue a ROLLBACK prior to doing anything else with the connection. If you want to recover from that error you'd either need to retry the entire transaction or wrap the portion that could fail in a savepoint (https://www.postgresql.org/docs/current/static/sql-savepoint.html) so that you can roll back to the savepoint (rather than undoing the entire transaction).

[martinmurciego]

Thank you. With persistence of analysis, trial and error I realized.
I solved my problem was that I could not access the properties of the exception generated by this module due to another strange exception that was generated after preventing me from capturing the exception that provides information about the unique or foreign violation of the database (postgres).
The interposed exception is: Transaction query already complete, run with DEBUG = knex: tx for more info.
It was generated only when a rollback was executed that includes the exception object as a parameter. Without setting a parameter, he did not launch that strange exception from which explanations are not understood. Now I can parse the node-postgres exception object because I can capture and propagate the exception without problems.

//...
.catch(function(e) {
console.log('Error during person registration!!!', e);
t.rollback;
console.log('Create record of canceled person (rollback).');
return Promise.reject(e);
})
//...`

t.rollback; instead of t.rollback(e);

Now I can access the properties of the exception that this module generates without problems: e.detail
And filter by the error code as you indicated it or by what appears in the error message.