24

The PostgreSql documentation does not seem to mention a special class range for custom error codes. Has any been defined or is there an unofficial agreement on a specific class range?

Jérôme Verstrynge
  • 57,710
  • 92
  • 283
  • 453
  • 3
    Very good question, I'll raise it on the mailing list. – Craig Ringer Mar 24 '14 at 01:39
  • 1
    Love this question - I'm currently trying to work with both PostgreSQL and Oracle, where Oracle uses and supports returning custom error codes via `SQLException#getErrorCode()`, while PostgreSQL always return `0` from that method. I've looked pretty hard and found nothing discussing custom errors via `SQLException#getSQLState()`. – rbellamy Feb 18 '16 at 04:21
  • Hopefully we will see an answer one day. – Alex Feb 10 '20 at 19:52
  • See also [Custom error code class and numbers for Postgres stored functions](https://dba.stackexchange.com/questions/258328/custom-error-code-class-and-numbers-for-postgres-stored-functions) on [dba.SE] – Bergi Apr 11 '23 at 22:45

1 Answers1

18

At the bottom of the first section on this page there is some oblique, and indeed rather unspecific, reference to custom error codes. My approach for custom error codes:

  • Start with a capital letter but not F (predefined config file errors), H (fdw), P (PL/pgSQL) or X (internal).
  • Do not use 0 (zero) or P in the 3rd column. Predefined error codes use these commonly.
  • Use a capital letter in the 4th position. No predefined error codes have this.

As an example, start with a character for your app: "T". Then a two-char error class: "3G". Then a sequential code "A0"-"A9", "B0"-"B9", etc. Yields T3GA0, T3GA1, etc.

But as Craig Ringer indicated in his comment to the OP, some better guidance on the topic might be useful. Keep in mind though that such guidance would hardly solve the issue, unless a global registry is made of error ranges for common extensions. I am doubtful that such a registry will be made, however.

Patrick
  • 29,357
  • 6
  • 62
  • 90
  • Yeah, the only robust mechanism is to do the "follow the DNS" trick, with namespaced values like `com.mycompany.myproject.ErrorCode`. Which isn't possible with an `SQLState`. – Craig Ringer Mar 24 '14 at 05:44
  • @Patrick et al, 6 years later, I have a question... Since we have no crystal ball, starting with an as-yet unused letter strikes me as perhaps only slightly less risky than starting with an as-yet unused number. It appears that starting with a '9' also places your errors in an as-yet unused bucket. Does anyone see problems with this approach? – Wellspring Jul 27 '20 at 17:19
  • same here. I finaly use the "ZZ999" as my all-in-one custom error code. – BufferUnderRun Mar 23 '22 at 18:01