A. PostgreSQL 錯誤代碼
所有由PostgreSQL伺服器回傳的訊息,都會搭配一個 5 個字元的錯誤代碼,這些代碼均以 SQL 標準的 SQLSTATE 代碼所定義。應用程式應該以錯誤代碼作為程式行為,而非處理文字的錯誤訊息。這些錯誤代碼會隨 PostgreSQL 版本發佈而微幅更新,但不會因為不同語言的文字訊息影響其定義。特別注意,部份錯誤代碼是以 SQL 標準的定義所制定,而有些錯誤代碼是額外制定的,以對應其他 SQL 標準未定義的使用情況。
標準來看,錯誤代碼的前 2 個字元代表著這個錯誤的類別,而後面 3 個字元則指出在此類別中更詳細的發生情況。所以,應用程式即使未完整支援特定的錯誤代碼,仍然可以依類別代碼瞭解大概發生了什麼事。
下方的 Table A.1 列出了所有在 PostgreSQL 10 中所定義的錯誤代碼。(部份錯誤代碼可能實際上沒有使用,但仍然依 SQL 標準所制定)每一個錯誤的類別都有一個"標準"的錯誤情況,其最後 3 個字元為 000,只用於在該類別未特別詳細定義的錯誤情況。
在"Condition Name"欄位中的內容將能在 PL/pgSQL 中被使用。Condition Name 不論大小寫字母均可合法使用。(特別注意,PL/pgSQL 並不支援 00、01 及 02 的錯誤類別。)
有一些錯誤訊息,其回傳內容是發生錯誤的資料庫物件名稱(表格 Table、表格欄位 Table column、資料型別 data type、限制條件 constraint)。舉例來說,如果產生了 unique_violation 錯誤,則會回傳某個限制條件名稱。這些訊息將會分別額外的欄位回傳,所以應用程式不需要特別解譯描述性的訊息內容。在 PostgreSQL 9.3之前,這種回傳方式僅限於 SQLSTATE 類別 23(違反限制條件的一致性 integrity constraint violation),日後將儘可能延伸支援到所有類別。
Table A.1. PostgreSQL Error Codes
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_set_function
01007
privilege_not_granted
01006
privilege_not_revoked
01004
string_data_right_truncation
01P01
deprecated_feature
Class 02 — No Data (this is also a warning class per the SQL standard)
02000
no_data
02001
no_additional_dynamic_result_sets_returned
Class 03 — SQL Statement Not Yet Complete
03000
sql_statement_not_yet_complete
Class 08 — Connection Exception
08000
connection_exception
08003
connection_does_not_exist
08006
connection_failure
08001
sqlclient_unable_to_establish_sqlconnection
08004
sqlserver_rejected_establishment_of_sqlconnection
08007
transaction_resolution_unknown
08P01
protocol_violation
Class 09 — Triggered Action Exception
09000
triggered_action_exception
Class 0A — Feature Not Supported
0A000
feature_not_supported
Class 0B — Invalid Transaction Initiation
0B000
invalid_transaction_initiation
Class 0F — Locator Exception
0F000
locator_exception
0F001
invalid_locator_specification
Class 0L — Invalid Grantor
0L000
invalid_grantor
0LP01
invalid_grant_operation
Class 0P — Invalid Role Specification
0P000
invalid_role_specification
Class 0Z — Diagnostics Exception
0Z000
diagnostics_exception
0Z002
stacked_diagnostics_accessed_without_active_handler
Class 20 — Case Not Found
20000
case_not_found
Class 21 — Cardinality Violation
21000
cardinality_violation
Class 22 — Data Exception
22000
data_exception
2202E
array_subscript_error
22021
character_not_in_repertoire
22008
datetime_field_overflow
22012
division_by_zero
22005
error_in_assignment
2200B
escape_character_conflict
22022
indicator_overflow
22015
interval_field_overflow
2201E
invalid_argument_for_logarithm
22014
invalid_argument_for_ntile_function
22016
invalid_argument_for_nth_value_function
2201F
invalid_argument_for_power_function
2201G
invalid_argument_for_width_bucket_function
22018
invalid_character_value_for_cast
22007
invalid_datetime_format
22019
invalid_escape_character
2200D
invalid_escape_octet
22025
invalid_escape_sequence
22P06
nonstandard_use_of_escape_character
22010
invalid_indicator_parameter_value
22023
invalid_parameter_value
22013
invalid_preceding_or_following_size
2201B
invalid_regular_expression
2201W
invalid_row_count_in_limit_clause
2201X
invalid_row_count_in_result_offset_clause
2202H
invalid_tablesample_argument
2202G
invalid_tablesample_repeat
22009
invalid_time_zone_displacement_value
2200C
invalid_use_of_escape_character
2200G
most_specific_type_mismatch
22004
null_value_not_allowed
22002
null_value_no_indicator_parameter
22003
numeric_value_out_of_range
2200H
sequence_generator_limit_exceeded
22026
string_data_length_mismatch
22001
string_data_right_truncation
22011
substring_error
22027
trim_error
22024
unterminated_c_string
2200F
zero_length_character_string
22P01
floating_point_exception
22P02
invalid_text_representation
22P03
invalid_binary_representation
22P04
bad_copy_file_format
22P05
untranslatable_character
2200L
not_an_xml_document
2200M
invalid_xml_document
2200N
invalid_xml_content
2200S
invalid_xml_comment
2200T
invalid_xml_processing_instruction
22030
duplicate_json_object_key_value
22031
invalid_argument_for_sql_json_datetime_function
22032
invalid_json_text
22033
invalid_sql_json_subscript
22034
more_than_one_sql_json_item
22035
no_sql_json_item
22036
non_numeric_sql_json_item
22037
non_unique_keys_in_a_json_object
22038
singleton_sql_json_item_required
22039
sql_json_array_not_found
2203A
sql_json_member_not_found
2203B
sql_json_number_not_found
2203C
sql_json_object_not_found
2203D
too_many_json_array_elements
2203E
too_many_json_object_members
2203F
sql_json_scalar_required
2203G
sql_json_item_cannot_be_cast_to_target_type
Class 23 — Integrity Constraint Violation
23000
integrity_constraint_violation
23001
restrict_violation
23502
not_null_violation
23503
foreign_key_violation
23505
unique_violation
23514
check_violation
23P01
exclusion_violation
Class 24 — Invalid Cursor State
24000
invalid_cursor_state
Class 25 — Invalid Transaction State
25000
invalid_transaction_state
25001
active_sql_transaction
25002
branch_transaction_already_active
25008
held_cursor_requires_same_isolation_level
25003
inappropriate_access_mode_for_branch_transaction
25004
inappropriate_isolation_level_for_branch_transaction
25005
no_active_sql_transaction_for_branch_transaction
25006
read_only_sql_transaction
25007
schema_and_data_statement_mixing_not_supported
25P01
no_active_sql_transaction
25P02
in_failed_sql_transaction
25P03
idle_in_transaction_session_timeout
Class 26 — Invalid SQL Statement Name
26000
invalid_sql_statement_name
Class 27 — Triggered Data Change Violation
27000
triggered_data_change_violation
Class 28 — Invalid Authorization Specification
28000