I'm not sure what you really want but the official documentation is a good place to start, all 86 pages of it.
Just thinking about Oracle error codes gets me all nostalgic for those cryptic messages
- ORA-00600: internal error code (something happened but we can't tell you)
- ORA-03113: end-of-file on communication channel (you, or your database or your connection are down, somewhere...)
- ORA-12154: TNS: could not resolve the connect identifier specified (your database does not want to talk to you)
You also may be thinking of DBMS_UTILITY.FORMAT_ERROR_BACKTRACE which will tell you more about errors in PL/SQL programs. This is not the same as your question about verbose messages for SQL queries.
As for NoSQL vs Relational, Postgres gives you the best of both but with the benefit of ACID. Along with its robust reliable relational engine, the JSONB data type allows you to save JSON data, internally stored in an optimized binary format that provides for indexing individual attributes. So you get the flexibility of less-structured data (JSON) with the very fast performance of Postgres indexed queries.
Also, Postgres supports UUID as a data type, storing the native 128-bit values. Support includes indexing and use as primary key.
As for your database structure, while I am do not know your data well, it appears from your example data that have a simple parent-child relationship between an "identity" or "login" or whatever you want to call it, and a "cred". On the cred table, for rows without a salt, just leave the extra fields null or with empty text ('').
Your description suggests you have a many-to-many between Person and Key entities. If so, in a relational database a many-to-many is always represented with a third table containing at lead two fields, the key of each table, so a pair of foreign keys. Classic example: Book table and Author table are connected by an Authorship table contains an Author ID and a Book ID for each author contributing to each book.
Tip: devise another name for your Key entity as the word key is, well, key to deigning and using a database. So, "key" is a confusing entity name.
Seems like you should be able to store all this data relationally rather than as JSON. But Postgres gives you both options.
Best Answer
If the unique field is properly indexed, a UNIQUE violation should be fairly quick.
Honestly though, I think doing a "two inner select" (I think I know what you mean here) is a better solution. Even with trapping for an exception (at the application level), that's just not a very clean way to handle programatic logic. Exception handling should be reserved for those rare occasions where you have an "exception to the rule."
And (without knowing which language you're coding in) if you're just trapping for a "SQLException", it's important to remember that those can be thrown for a variety of reasons. Your application might produce unexpected results if you incorrectly assume why an exception happened.
Bottom-line, I would say that the best approach is to do the SELECT to check for duplication.