本文共 7194 字，大约阅读时间需要 23 分钟。
PostgreSQL , SQLSTATE , 错误代码 , org.postgresql.util.PSQLState
Does such a class enumerating the PostgreSQL error codes already exist?
Yes, it does:
However, there are 238 error codes listed on the page you referenced, and org.postgresql.util.PSQLState only enumerates 41 values. Of those 41 values, only 33 are from that list of PostgreSQL error codes (roughly 14% coverage).
If you require any of the other constants, you will have to enumerate those yourself.
grep -c ERRCODE errcodes.txt 250
src/backend/utils/errcodes.txt
00000 S ERRCODE_SUCCESSFUL_COMPLETION successful_completion 01000 W ERRCODE_WARNING warning 0100C W ERRCODE_WARNING_DYNAMIC_RESULT_SETS_RETURNED dynamic_result_sets_returned 01008 W ERRCODE_WARNING_IMPLICIT_ZERO_BIT_PADDING implicit_zero_bit_padding 01003 W ERRCODE_WARNING_NULL_VALUE_ELIMINATED_IN_SET_FUNCTION null_value_eliminated_in_set_function 01007 W ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED privilege_not_granted ........... XX000 E ERRCODE_INTERNAL_ERROR internal_error XX001 E ERRCODE_DATA_CORRUPTED data_corrupted XX002 E ERRCODE_INDEX_CORRUPTED index_corrupted
PSQLState
// begin constant state codes public final static PSQLState UNKNOWN_STATE = new PSQLState(""); public final static PSQLState TOO_MANY_RESULTS = new PSQLState("0100E"); public final static PSQLState NO_DATA = new PSQLState("02000"); public final static PSQLState INVALID_PARAMETER_TYPE = new PSQLState("07006"); /** * We could establish a connection with the server for unknown reasons. Could be a network * problem. */ public final static PSQLState CONNECTION_UNABLE_TO_CONNECT = new PSQLState("08001"); public final static PSQLState CONNECTION_DOES_NOT_EXIST = new PSQLState("08003"); /** * The server rejected our connection attempt. Usually an authentication failure, but could be a * configuration error like asking for a SSL connection with a server that wasn't built with SSL * support. */ public final static PSQLState CONNECTION_REJECTED = new PSQLState("08004"); /** * After a connection has been established, it went bad. */ public final static PSQLState CONNECTION_FAILURE = new PSQLState("08006"); public final static PSQLState CONNECTION_FAILURE_DURING_TRANSACTION = new PSQLState("08007"); /** * The server sent us a response the driver was not prepared for and is either bizarre datastream * corruption, a driver bug, or a protocol violation on the server's part. */ public final static PSQLState PROTOCOL_VIOLATION = new PSQLState("08P01"); public final static PSQLState COMMUNICATION_ERROR = new PSQLState("08S01"); public final static PSQLState NOT_IMPLEMENTED = new PSQLState("0A000"); public final static PSQLState DATA_ERROR = new PSQLState("22000"); public final static PSQLState NUMERIC_VALUE_OUT_OF_RANGE = new PSQLState("22003"); public final static PSQLState BAD_DATETIME_FORMAT = new PSQLState("22007"); public final static PSQLState DATETIME_OVERFLOW = new PSQLState("22008"); public final static PSQLState DIVISION_BY_ZERO = new PSQLState("22012"); public final static PSQLState MOST_SPECIFIC_TYPE_DOES_NOT_MATCH = new PSQLState("2200G"); public final static PSQLState INVALID_PARAMETER_VALUE = new PSQLState("22023"); public final static PSQLState INVALID_CURSOR_STATE = new PSQLState("24000"); public final static PSQLState TRANSACTION_STATE_INVALID = new PSQLState("25000"); public final static PSQLState ACTIVE_SQL_TRANSACTION = new PSQLState("25001"); public final static PSQLState NO_ACTIVE_SQL_TRANSACTION = new PSQLState("25P01"); public final static PSQLState IN_FAILED_SQL_TRANSACTION = new PSQLState("25P02"); public final static PSQLState INVALID_SQL_STATEMENT_NAME = new PSQLState("26000"); public final static PSQLState INVALID_AUTHORIZATION_SPECIFICATION = new PSQLState("28000"); public final static PSQLState STATEMENT_NOT_ALLOWED_IN_FUNCTION_CALL = new PSQLState("2F003"); public final static PSQLState INVALID_SAVEPOINT_SPECIFICATION = new PSQLState("3B000"); public final static PSQLState SYNTAX_ERROR = new PSQLState("42601"); public final static PSQLState UNDEFINED_COLUMN = new PSQLState("42703"); public final static PSQLState UNDEFINED_OBJECT = new PSQLState("42704"); public final static PSQLState WRONG_OBJECT_TYPE = new PSQLState("42809"); public final static PSQLState NUMERIC_CONSTANT_OUT_OF_RANGE = new PSQLState("42820"); public final static PSQLState DATA_TYPE_MISMATCH = new PSQLState("42821"); public final static PSQLState UNDEFINED_FUNCTION = new PSQLState("42883"); public final static PSQLState INVALID_NAME = new PSQLState("42602"); public final static PSQLState OUT_OF_MEMORY = new PSQLState("53200"); public final static PSQLState OBJECT_NOT_IN_STATE = new PSQLState("55000"); public final static PSQLState SYSTEM_ERROR = new PSQLState("60000"); public final static PSQLState IO_ERROR = new PSQLState("58030"); public final static PSQLState UNEXPECTED_ERROR = new PSQLState("99999"); 由于未完全对应PG的SQLSTATE，所以，建议可以使用getSQLState获取一下错误代码并输出，或者扩展PSQLState.java，映射完整的PostgreSQL SQLSTATE。
拿到了SQLSTATE代码，还不够，因为可能有多处代码报同一个错误，如果要定位更加详细的原因，可以配置数据库的log_error_verbosity参数，在报错误代码的同时，可输出源代码的位置。
例如1，在psql中，将错误代码以及源码输出到客户端：
postgres=# \set VERBOSITY verbose postgres=# select t.oid,(select string_agg(relname, s',') from pg_class) from pg_class t; ERROR: 42704: type "s" does not exist LINE 1: select t.oid,(select string_agg(relname, s',') from pg_class... ^ LOCATION: typenameType, parse_type.c:546
例如2，在postgresql的错误日志中拿到错误代码以及源码.
postgres=# set log_error_verbosity ='verbose'; SET postgres=# select t.oid,(select string_agg(relname, s',') from pg_class) from pg_class t; ERROR: type "s" does not exist LINE 1: select t.oid,(select string_agg(relname, s',') from pg_class... ^ 错误日志: 2018-07-11 13:22:40.505 CST,"enterprisedb","postgres",28166,"[local]",5b45743c.6e06,5,"SELECT",2018-07-11 11:06:36 CST,4/80,0,ERROR,42704,"type ""s"" does not exist",,,,,,"select t.oid,(select string_agg(relname, s',') from pg_class) from pg_class t;",42,"typenameType, parse_type.c:546","psql.bin"
log_error_verbosity (enum)
Controls the amount of detail written in the server log for each message that is logged. Valid values are TERSE, DEFAULT, and VERBOSE, each adding more fields to displayed messages. TERSE excludes the logging of DETAIL, HINT, QUERY, and CONTEXT error information. VERBOSE output includes the SQLSTATE error code (see also Appendix A) and the source code file name, function name, and line number that generated the error. Only superusers can change this setting.
转载地址：http://tczxx.baihongyu.com/