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

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_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

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

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

invalid_authorization_specification

28P01

invalid_password

Class 2B — Dependent Privilege Descriptors Still Exist

2B000

dependent_privilege_descriptors_still_exist

2BP01

dependent_objects_still_exist

Class 2D — Invalid Transaction Termination

2D000

invalid_transaction_termination

Class 2F — SQL Routine Exception

2F000

sql_routine_exception

2F005

function_executed_no_return_statement

2F002

modifying_sql_data_not_permitted

2F003

prohibited_sql_statement_attempted

2F004

reading_sql_data_not_permitted

Class 34 — Invalid Cursor Name

34000

invalid_cursor_name

Class 38 — External Routine Exception

38000

external_routine_exception

38001

containing_sql_not_permitted

38002

modifying_sql_data_not_permitted

38003

prohibited_sql_statement_attempted

38004

reading_sql_data_not_permitted

Class 39 — External Routine Invocation Exception

39000

external_routine_invocation_exception

39001

invalid_sqlstate_returned

39004

null_value_not_allowed

39P01

trigger_protocol_violated

39P02

srf_protocol_violated

39P03

event_trigger_protocol_violated

Class 3B — Savepoint Exception

3B000

savepoint_exception

3B001

invalid_savepoint_specification

Class 3D — Invalid Catalog Name

3D000

invalid_catalog_name

Class 3F — Invalid Schema Name

3F000

invalid_schema_name

Class 40 — Transaction Rollback

40000

transaction_rollback

40002

transaction_integrity_constraint_violation

40001

serialization_failure

40003

statement_completion_unknown

40P01

deadlock_detected

Class 42 — Syntax Error or Access Rule Violation

42000

syntax_error_or_access_rule_violation

42601

syntax_error

42501

insufficient_privilege

42846

cannot_coerce

42803

grouping_error

42P20

windowing_error

42P19

invalid_recursion

42830

invalid_foreign_key

42602

invalid_name

42622

name_too_long

42939

reserved_name

42804

datatype_mismatch

42P18

indeterminate_datatype

42P21

collation_mismatch

42P22

indeterminate_collation

42809

wrong_object_type

428C9

generated_always

42703

undefined_column

42883

undefined_function

42P01

undefined_table

42P02

undefined_parameter

42704

undefined_object

42701

duplicate_column

42P03

duplicate_cursor

42P04

duplicate_database

42723

duplicate_function

42P05

duplicate_prepared_statement

42P06

duplicate_schema

42P07

duplicate_table

42712

duplicate_alias

42710

duplicate_object

42702

ambiguous_column

42725

ambiguous_function

42P08

ambiguous_parameter

42P09

ambiguous_alias

42P10

invalid_column_reference

42611

invalid_column_definition

42P11

invalid_cursor_definition

42P12

invalid_database_definition

42P13

invalid_function_definition

42P14

invalid_prepared_statement_definition

42P15

invalid_schema_definition

42P16

invalid_table_definition

42P17

invalid_object_definition

Class 44 — WITH CHECK OPTION Violation

44000

with_check_option_violation

Class 53 — Insufficient Resources

53000

insufficient_resources

53100

disk_full

53200

out_of_memory

53300

too_many_connections

53400

configuration_limit_exceeded

Class 54 — Program Limit Exceeded

54000

program_limit_exceeded

54001

statement_too_complex

54011

too_many_columns

54023

too_many_arguments

Class 55 — Object Not In Prerequisite State

55000

object_not_in_prerequisite_state

55006

object_in_use

55P02

cant_change_runtime_param

55P03

lock_not_available

55P04

unsafe_new_enum_value_usage

Class 57 — Operator Intervention

57000

operator_intervention

57014

query_canceled

57P01

admin_shutdown

57P02

crash_shutdown

57P03

cannot_connect_now

57P04

database_dropped

Class 58 — System Error (errors external toPostgreSQLitself)

58000

system_error

58030

io_error

58P01

undefined_file

58P02

duplicate_file

Class 72 — Snapshot Failure

72000

snapshot_too_old

Class F0 — Configuration File Error

F0000

config_file_error

F0001

lock_file_exists

Class HV — Foreign Data Wrapper Error (SQL/MED)

HV000

fdw_error

HV005

fdw_column_name_not_found

HV002

fdw_dynamic_parameter_value_needed

HV010

fdw_function_sequence_error

HV021

fdw_inconsistent_descriptor_information

HV024

fdw_invalid_attribute_value

HV007

fdw_invalid_column_name

HV008

fdw_invalid_column_number

HV004

fdw_invalid_data_type

HV006

fdw_invalid_data_type_descriptors

HV091

fdw_invalid_descriptor_field_identifier

HV00B

fdw_invalid_handle

HV00C

fdw_invalid_option_index

HV00D

fdw_invalid_option_name

HV090

fdw_invalid_string_length_or_buffer_length

HV00A

fdw_invalid_string_format

HV009

fdw_invalid_use_of_null_pointer

HV014

fdw_too_many_handles

HV001

fdw_out_of_memory

HV00P

fdw_no_schemas

HV00J

fdw_option_name_not_found

HV00K

fdw_reply_handle

HV00Q

fdw_schema_not_found

HV00R

fdw_table_not_found

HV00L

fdw_unable_to_create_execution

HV00M

fdw_unable_to_create_reply

HV00N

fdw_unable_to_establish_connection

Class P0 — PL/pgSQL Error

P0000

plpgsql_error

P0001

raise_exception

P0002

no_data_found

P0003

too_many_rows

P0004

assert_failure

Class XX — Internal Error

XX000

internal_error

XX001

data_corrupted

XX002

index_corrupted