Postgres 9 Error Codes
Contents |
8.3 / 8.4 / 9.0 PostgreSQL 9.2.18 Documentation Prev Up Next Appendix A. PostgreSQL Error Codes All messages emitted postgresql sqlstate by the PostgreSQL server are assigned five-character error codes that follow the postgresql exception handling SQL standard's conventions for "SQLSTATE" codes. Applications that need to know which error condition has occurred should usually test
Postgresql Exception Handling Example
the error code, rather than looking at the textual error message. The error codes are less likely to change across PostgreSQL releases, and also are not subject to change due
Postgres Exception When Others
to localization of error messages. Note that some, but not all, of the error codes produced by PostgreSQL are defined by the SQL standard; some additional error codes for conditions not defined by the standard have been invented or borrowed from other databases. According to the standard, the first two characters of an error code denote a class of errors, npgsqlexception while the last three characters indicate a specific condition within that class. Thus, an application that does not recognize the specific error code can still be able to infer what to do from the error class. Table A-1 lists all the error codes defined in PostgreSQL 9.2.18. (Some are not actually used at present, but are defined by the SQL standard.) The error classes are also shown. For each error class there is a "standard" error code having the last three characters 000. This code is used only for error conditions that fall within the class but do not have any more-specific code assigned. The symbol shown in the column "Condition Name" is also the condition name to use in PL/pgSQL. Condition names can be written in either upper or lower case. (Note that PL/pgSQL does not recognize warning, as opposed to error, condition names; those are classes 00, 01, and 02.) Table A-1. PostgreSQL Error Codes Error Code Condition Name Class 00 — Successful Completion 00000 successful_completion Class 01 — Warning 01000 warning 0100C dynamic_result_sets_returned 01008 implicit_zero_bit_padding 01003 null_value_eliminated_in_s
8.1 / 8.2 / 8.3 / 8.4 / 9.0 PostgreSQL 9.0.23 Documentation Prev Up Chapter 39. PL/pgSQL -
Postgres 23505
SQL Procedural Language Next 39.8. Errors and Messages Use the RAISE statement postgres raise exception to report messages and raise errors. RAISE [ level ] 'format' [, expression [, ... ]] [ psql error codes USING option = expression [, ... ] ]; RAISE [ level ] condition_name [ USING option = expression [, ... ] ]; RAISE [ level ] SQLSTATE 'sqlstate' [ USING https://www.postgresql.org/docs/9.2/static/errcodes-appendix.html option = expression [, ... ] ]; RAISE [ level ] USING option = expression [, ... ]; RAISE ; The level option specifies the error severity. Allowed levels are DEBUG, LOG, INFO, NOTICE, WARNING, and EXCEPTION, with EXCEPTION being the default. EXCEPTION raises an error (which normally aborts the current transaction); the other levels only generate messages of https://www.postgresql.org/docs/9.0/static/plpgsql-errors-and-messages.html different priority levels. Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_messages and client_min_messages configuration variables. See Chapter 18 for more information. After level if any, you can write a format (which must be a simple string literal, not an expression). The format string specifies the error message text to be reported. The format string can be followed by optional argument expressions to be inserted into the message. Inside the format string, % is replaced by the string representation of the next optional argument's value. Write %% to emit a literal %. In this example, the value of v_job_id will replace the % in the string: RAISE NOTICE 'Calling cs_create_job(%)', v_job_id; You can attach additional information to the error report by writing USING followed by option = expression items. The allowed option keywords are MESSAGE, DETAIL, HINT, and ERRCODE, while each expression can be any string-valued expression. MESSAGE sets the error message text (this option can't be used in the
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more http://stackoverflow.com/questions/19908564/can-i-have-parametrized-sqlstate-in-exceptions-in-postgresql about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack https://github.com/symetics/postgres-error-codes-as-json Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Can I have parametrized SQLSTATE in exceptions in PostgreSQL up vote error codes 2 down vote favorite I tend to write my PostgreSQL functions in a way that I always have default exception handler, that adds function name to the exception thrown and re-throws it again. Example: BEGIN RAISE exception SQLSTATE '77777' using message = 'Test exception'; EXCEPTION WHEN others THEN raise exception using message = 'error_handling_test: ' || sqlstate || '/' || sqlerrm; END; In this case, the SQLSTATE 77777 is postgresql exception handling not propagated further and exception I catch later has default SQLSTATE P0001. It is possible to add SQLSTATE also to exception handler raise, but this seems to be limited to hardcoded string. What I would like to get is something like this: DECLARE err_code varchar; BEGIN RAISE exception SQLSTATE '77777' using message = 'Test exception'; EXCEPTION WHEN others THEN err_code:=sqlstate; raise exception sqlstate err_code using message = 'error_handling_test: ' || sqlstate || '/' || sqlerrm; END; This won't compile. database postgresql exception-handling plpgsql share|improve this question edited Nov 11 '13 at 17:55 Erwin Brandstetter 220k29357468 asked Nov 11 '13 at 14:18 Raqua 4317 1 Always provide a complete function definition, including the header. Even if it's trivial, it makes testing easier. And your Postgres version, of course. –Erwin Brandstetter Nov 11 '13 at 17:24 1 if you can use a 9.3, then you can use a additional exception fields postgresql.org/docs/9.3/static/… so you don't need to soil message field. –Pavel Stehule Nov 11 '13 at 18:04 @PavelStehule - this is of no use for my use case as I need the sqlstate outside of Postgres, in Java. –Raqua Nov 12 '13 at 9:24 add a comment| 1 Answer 1 active oldest v
Sign in Pricing Blog Support Search GitHub This repository Watch 3 Star 0 Fork 0 symetics/postgres-error-codes-as-json Code Issues 0 Pull requests 0 Projects 0 Pulse Graphs No description or website provided. 3 commits 1 branch 0 releases Fetching contributors Clone or download Clone with HTTPS Use Git or checkout with SVN using the web URL. Open in Desktop Download ZIP Find file Branch: master Switch branches/tags Branches Tags master Nothing to show Nothing to show New pull request Fetching latest commit… Cannot retrieve the latest commit at this time. Permalink Failed to load latest commit information. README.md error-classes.json error-codes.json README.md PostgreSQL Error Codes as JSON error-codes.json { "0": "successful_completion", "1000": "warning", "0100C": "dynamic_result_sets_returned", ... error-classes.json { "20": "Case Not Found", "21": "Cardinality Violation", "22": "Data Exception", "23": "Integrity Constraint Violation", Contact GitHub API Training Shop Blog About © 2016 GitHub, Inc. Terms Privacy Security Status Help You can't perform that action at this time. You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.