Ошибка 22008 postgresql - Не ошибается лишь тот, кто ничего не делает!

I’m trying to cast a varchar into a date with this following code, and the following error is outputting, any idea why?

ALTER TABLE import.employee
ALTER COLUMN birth_date
TYPE date
USING (birth_date::date);

    ERROR:  date/time field value out of range: "05/29/1960"
HINT:  Perhaps you need a different "datestyle" setting.
********** Error **********

ERROR: date/time field value out of range: "05/29/1960"
SQL state: 22008
Hint: Perhaps you need a different "datestyle" setting.

asked Apr 27, 2014 at 20:55

Set the datestyle before:

set datestyle = mdy;

answered Apr 27, 2014 at 21:08

Clodoaldo NetoClodoaldo Neto

117k26 gold badges229 silver badges255 bronze badges

If you need to get the ::date from birth_date, first give the DATE format to your field using the given functions in Data Type Formatting Functions docs.

answered Apr 27, 2014 at 21:25

Источник

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`
`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`	`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`
`57P05`	`idle_session_timeout`
Class 58 — System Error (errors external to PostgreSQL itself)
`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`

Источник

Подозрительные типы

Время на прочтение
8 мин

Количество просмотров 11K

В их внешнем облике ничто не вызывает подозрений. Более того, они даже кажутся тебе хорошо и давно знакомыми. Но это только до тех пор, пока ты их не проверишь. Вот тут-то они и проявят свою коварную сущность, сработав совсем не так, как ты ожидал. А иногда выкидывают такое, от чего волосы просто встают дыбом — к примеру, теряют доверенные им секретные данные. Когда ты делаешь им очную ставку, они утверждают, что не знают друг друга, хотя в тени усердно трудятся под одним колпаком. Пора уже наконец-то вывести их на чистую воду. Давайте же и мы разберемся с этими подозрительными типами.

Типизация данных в PostgreSQL, при всей своей логичности, действительно преподносит порой очень странные сюрпризы. В этой статье мы постараемся прояснить некоторые их причуды, разобраться в причине их странного поведения и понять, как не столкнуться с проблемами в повседневной практике. Сказать по правде, я составил эту статью в том числе и в качестве некоего справочника для самого себя, справочника, к которому можно было бы легко обратиться в спорных случаях. Поэтому он будет пополняться по мере обнаружения новых сюрпризов от подозрительных типов. Итак, в путь, о неутомимые следопыты баз данных!

Досье номер один. real/double precision/numeric/money

Казалось бы, числовые типы наименее проблемные с точки зрения сюрпризов в поведении. Но как бы не так. Поэтому с них и начнем. Итак…

Разучились считать

SELECT 0.1::real = 0.1

?column?
boolean
---------
f

В чем дело? В том, что PostgreSQL приводит нетипизированную константу 0.1 к типу double precision и пытается сравнить ее с 0.1 типа real. А это абсолютно разные значения! Суть в представлении вещественных чисел в машинной памяти. Поскольку 0.1 невозможно представить в виде конечной двоичной дроби (это будет 0.0(0011) в двоичном виде), числа с разной разрядностью будут отличаться, отсюда и результат, что они не равны. Вообще говоря, это тема для отдельной статьи, подробнее писать тут не буду.

Откуда ошибка?

SELECT double precision(1)

ERROR:  syntax error at or near "("
LINE 1: SELECT double precision(1)
                               ^
********** Ошибка **********
ERROR: syntax error at or near "("
SQL-состояние: 42601
Символ: 24

Многие знают, что PostgreSQL допускает функциональную запись приведения типов. То есть можно написать не только 1::int, но и int(1), что будет равнозначно. Но только не для типов, название которых состоит из нескольких слов! Поэтому, если вы хотите привести числовое значение к типу double precision в функциональном виде, используйте алиас этого типа float8, то есть SELECT float8(1).

Что больше бесконечности?

SELECT 'Infinity'::double precision < 'NaN'::double precision

?column?
boolean
---------
t

Вон оно как! Оказывается, есть нечто, большее бесконечности, и это NaN! При этом документация PostgreSQL честными глазами смотрит на нас и утверждает, что NaN заведомо больше любого другого числа, а, следовательно, бесконечности. Справедливо и обратное для -NaN. Привет, любители матанализа! Но надо помнить, что все это действует в контексте вещественных чисел.

Округление глаз

SELECT round('2.5'::double precision)
     , round('2.5'::numeric)

      round      |  round
double precision | numeric
-----------------+---------
2                | 3

Еще один неожиданный привет от базы. И снова надо запомнить, что для типов double precision и numeric действуют разные округления. Для numeric — обычное, когда 0,5 округляется в большую сторону, а для double precision — округление 0,5 происходит в сторону ближайшего четного целого.

Деньги — это нечто особое

SELECT '10'::money::float8

ERROR:  cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
                          ^
********** Ошибка **********
ERROR: cannot cast type money to double precision
SQL-состояние: 42846
Символ: 19

По мнению PostgreSQL, деньги не являются вещественным числом. По мнению некоторых индивидуумов, тоже. Нам же надо помнить, что приведение типа money возможно только к типу numeric, равно как и к типу money можно привести только тип numeric. А вот с ним уже можно играться, как душе будет угодно. Но это будут уже не те деньги.

Smallint и генерация последовательностей

SELECT *
  FROM generate_series(1::smallint, 5::smallint, 1::smallint)

ERROR:  function generate_series(smallint, smallint, smallint) is not unique
LINE 2:   FROM generate_series(1::smallint, 5::smallint, 1::smallint...
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
********** Ошибка **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL-состояние: 42725
Подсказка: Could not choose a best candidate function. You might need to add explicit type casts.
Символ: 18

Не любит PostgreSQL мелочиться. Какие такие последовательности на основании smallint? int, не меньше! Поэтому при попытке выполнения вышеприведенного запроса база пытается привести smallint к какому-то другому целочисленному типу, и видит, что таких приведений может быть несколько. Какое приведение выбрать? Это она решить не может, и поэтому падает с ошибкой.

Досье номер два. «char»/char/varchar/text

Ряд странностей присутствует и у символьных типов. Давайте тоже познакомимся с ними.

Это что за фокусы?

SELECT 'ПЕТЯ'::"char"
     , 'ПЕТЯ'::"char"::bytea
     , 'ПЕТЯ'::char
     , 'ПЕТЯ'::char::bytea

 char  | bytea |    bpchar    | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
 ╨     | xd0  | П            | xd09f

Что это за тип «char», что это за клоун? Нам таких не надо… Потому, что он прикидывается обычным char, даром что в кавычках. А отличается он от обычного char, который без кавычек, тем, что выводит только первый байт строкового представления, тогда как нормальный char выводит первый символ. В нашем случае первый символ — буква П, которая в unicode-представлении занимает 2 байта, о чем свидетельствует конвертация результата в тип bytea. А тип «char» берет только первый байт этого unicode-представления. Тогда зачем этот тип нужен? Документация PostgreSQL говорит, что это специальный тип, используемый для особых нужд. Так что он вряд ли нам потребуется. Но посмотрите ему в глаза и не ошибитесь, когда встретите его с его особенным поведением.

Лишние пробелы. С глаз долой, из сердца вон

SELECT 'abc   '::char(6)::bytea
     , 'abc   '::char(6)::varchar(6)::bytea
     , 'abc   '::varchar(6)::bytea

     bytea     |   bytea  |     bytea
     bytea     |   bytea  |     bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020

Взгляните на приведенный пример. Я специально все результаты привел к типу bytea, чтобы было наглядно видно, что там лежит. Где хвостовые пробелы после приведения к типу varchar(6)? Документация лаконично утверждает: «При приведении значения character к другому символьному типу дополняющие пробелы отбрасываются». Эту нелюбовь надо запомнить. И заметьте, что если строковая константа в кавычках сразу приводится к типу varchar(6), концевые пробелы сохраняются. Такие вот чудеса.

Досье номер три. json/jsonb

JSON — отдельная структура, которая живет своей жизнью. Поэтому ее сущности и сущности PostgreSQL немного отличаются. Вот примеры.

Джонсон и Джонсон. Почувствуйте разницу

SELECT 'null'::jsonb IS NULL

?column?
boolean
---------
f

Все дело в том, что у JSON есть своя сущность null, которая не является аналогом NULL в PostgreSQL. В то же время, сам JSON-объект вполне может иметь значение NULL, поэтому выражение SELECT null::jsonb IS NULL (обратите внимание на отсутствие одинарных кавычек) на сей раз вернет true.

Одна буква меняет все

SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::json

                     json
                     json
------------------------------------------------
{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}

---

SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::jsonb

             jsonb
             jsonb
--------------------------------
{"1": [7, 8, 9], "2": [4, 5, 6]}

Все дело в том, что json и jsonb — совершенно разные структуры. В json объект хранится как есть, а в jsonb он хранится уже в виде разобранной проиндексированной структуры. Именно поэтому во втором случае значение объекта по ключу 1 было заменено с [1, 2, 3] на [7, 8, 9], которое пришло в структуру в самом конце с тем же ключом.

С лица воды не пить

SELECT '{"reading": 1.230e-5}'::jsonb
     , '{"reading": 1.230e-5}'::json

          jsonb         |         json
          jsonb         |         json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}

PostgreSQL в реализации JSONB меняет форматирование вещественных чисел, приводя их к классическому виду. Для типа JSON такого не происходит. Странно немного, но его право.

Досье номер четыре. date/time/timestamp

С типами даты/времени тоже есть некоторые странности. Посмотрим на них. Сразу оговорюсь, что некоторые из особенностей поведения становятся понятными, если хорошо понимать суть работы с часовыми поясами. Но это также тема для отдельной статьи.

Моя твоя не понимать

SELECT '08-Jan-99'::date

ERROR:  date/time field value out of range: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
               ^
HINT:  Perhaps you need a different "datestyle" setting.
********** Ошибка **********
ERROR: date/time field value out of range: "08-Jan-99"
SQL-состояние: 22008
Подсказка: Perhaps you need a different "datestyle" setting.
Символ: 8

Казалось бы, что тут непонятного? Но все же база не понимает, что мы тут поставили на первое место — год или день? И решает, что это 99 января 2008 года, что взрывает ей мозг. Вообще говоря, в случае передачи дат в текстовом формате нужно очень внимательно проверять то, насколько правильно база их распознала (в частности, анализировать параметр datestyle командой SHOW datestyle), поскольку неоднозначности в этом вопросе могут стоить очень дорого.

Ты откуда такой взялся?

SELECT '04:05 Europe/Moscow'::time

ERROR:  invalid input syntax for type time: "04:05 Europe/Moscow"
LINE 1: SELECT '04:05 Europe/Moscow'::time
               ^
********** Ошибка **********
ERROR: invalid input syntax for type time: "04:05 Europe/Moscow"
SQL-состояние: 22007
Символ: 8

Почему база не может понять явно указанное время? Потому что для часового пояса указана не аббревиатура, а полное наименование, которое имеет смысл только в контексте даты, поскольку учитывает историю изменения часовых поясов, а она без даты не работает. Да и сама формулировка строки времени вызывает вопросы — а что же на самом деле имел в виду программист? Поэтому тут все логично, если разобраться.

Что ему не так?

Представьте себе ситуацию. У вас в таблице есть поле с типом timestamptz. Вы хотите его проиндексировать. Но понимаете, что строить по этому полю индекс не всегда оправдано ввиду его высокой селективности (почти все значения этого типа будут уникальными). Поэтому вы решаете снизить селективность индекса, приведя этот тип к дате. И получаете сюрприз:

CREATE INDEX "iIdent-DateLastUpdate"
  ON public."Ident" USING btree
  (("DTLastUpdate"::date));

ERROR:  functions in index expression must be marked IMMUTABLE
********** Ошибка **********
ERROR: functions in index expression must be marked IMMUTABLE
SQL-состояние: 42P17

В чем дело? В том, что для приведения типа timestamptz к типу date используется значение системного параметра TimeZone, что делает функцию приведения типа зависимой от настраиваемого параметра, т.е. изменчивой (volatile). Такие функции в индексе недопустимы. В этом случае надо явно указывать, в каком часовом поясе производится приведение типа.

Когда now совсем даже не now

Мы привыкли, что now() возвращает текущую дату/время с учетом часового пояса. Но посмотрите на следующие запросы:

START TRANSACTION;
SELECT now();

            now
  timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03

COMMIT;

Дата/время возвращаются одинаковыми независимо от того, сколько времени прошло с момента предыдущего запроса! В чем дело? В том, что now() — это не текущее время, а время начала текущей транзакции. Поэтому в рамках транзакции оно не меняется. Любой запрос, запускаемый вне рамок транзакции, оборачивается в транзакцию неявно, поэтому мы и не замечаем, что время, выдаваемое простым запросом SELECT now(); на самом деле-то не текущее… Если хотите получить честное текущее время, нужно пользоваться функцией clock_timestamp().

Досье номер пять. bit

Strange a little bit

SELECT '111'::bit(4)

 bit
bit(4)
------
1110

С какой стороны следует добавлять биты в случае расширения типа? Кажется, что слева. Но только у базы на этот счет другое мнение. Будьте осторожны: при несоответствии количества разрядов при приведении типа вы получите совсем не то, что хотели. Это относится как к добавлению битов справа, так и к урезанию битов. Тоже справа…

Досье номер шесть. Массивы

Даже NULL не стрельнул

SELECT ARRAY[1, 2] || NULL

?column?
integer[]
---------
{1,2}

Как нормальные люди, воспитанные на SQL, мы ожидаем, что результатом этого выражения будет NULL. Но не тут-то было. Возвращается массив. Почему? Потому что в данном случае база приводит NULL к целочисленному массиву и неявно вызывает функцию array_cat. Но все равно остается неясным, почему этот «массивовый котик» не обнуляет массив. Такое поведение тоже надо просто запомнить.

Подведем итог. Странностей хватает. Большинство из них, конечно, не настолько критичны, чтобы говорить о вопиюще неадекватном поведении. А другие объясняются удобством использования или частотой их применимости в тех или иных ситуациях. Но в то же время неожиданностей много. Поэтому надо о них знать. Если найдете еще что-то странное или необычное в поведении каких-либо типов, пишите в комментариях, с удовольствием дополню имеющиеся на них досье.

Источник

My PostgreSQL database version is

PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

In both databases, datestyle is set to"ISO, DMY".

ATTEMPT 1

Query :

insert into station(name , date) values('test' , '21/1/2016')

Error Log:

ERROR:  date/time field value out of range: "21/1/2016"
LINE 1: select '21/1/2016'::date
               ^
HINT:  Perhaps you need a different "datestyle" setting.

********** Error **********

ERROR: date/time field value out of range: "21/1/2016"
SQL state: 22008
Hint: Perhaps you need a different "datestyle" setting.
Character: 8

ATTEMPT 2

Query :

select '21/1/2016'::date

Error log :

ERROR:  date/time field value out of range: "21/1/2016"
LINE 1: select '21/1/2016'::date
               ^
HINT:  Perhaps you need a different "datestyle" setting.

********** Error **********

ERROR: date/time field value out of range: "21/1/2016"
SQL state: 22008
Hint: Perhaps you need a different "datestyle" setting.
Character: 8

On one system it works and on other system it doesn’t. Both have Ubuntu and the same PostgreSQL version.

COLLATE SETTINGS ON BOTH SERVERS

Query works successfuly on this server and has below COLLATE setting

show lc_CTYPE   -> C
show LC_CoLLATE -> C
SHOW lc_time;   -> "en_IN"
select '21/1/2016'::date   -> "2016-01-21"

Query Does not Works on this server and has below COLLATE setting

show lc_CTYPE  -> "en_IN"
show LC_CoLLATE ->  "en_IN"
SHOW lc_time -> "en_IN"
select '21/1/2016'::date  -> above error log

Источник

Hello,

Thank you for responding to my question. Yes, I am trying to use pgloader.
I’m using the precompiled .rpm, which I downloaded today. I invoked the
pgloader command as follows:

pgloader -v ‘mysql://dbname:xxxxx@localhost/db1’
‘postgresql://dbname:yyyyy@localhost/db2’

Here’s what the log file contains.

2015-02-27T14:20:37.037000Z NOTICE Starting pgloader, log system is ready.

2015-02-27T14:20:37.066000Z LOG Main logs in ‘/tmp/pgloader/pgloader.log’

2015-02-27T14:20:37.075000Z LOG Data errors in ‘/tmp/pgloader/’

2015-02-27T14:20:37.277000Z NOTICE DROP then CREATE TABLES

2015-02-27T14:20:37.277000Z WARNING Postgres warning: CREATE TABLE will
create implicit sequence «blacklist_raw_id_seq» for serial column «
blacklist_raw.id»

2015-02-27T14:20:37.477000Z WARNING Postgres warning: CREATE TABLE will
create implicit sequence «categories_category_id_seq» for serial column
«categories.category_id»

2015-02-27T14:20:37.477000Z WARNING Postgres warning: CREATE TABLE will
create implicit sequence «domains_domain_id_seq» for serial column
«domains.domain_id»

2015-02-27T14:20:37.477000Z WARNING Postgres warning: CREATE TABLE will
create implicit sequence «sources_source_id_seq» for serial column
«sources.source_id»

2015-02-27T14:20:37.477000Z WARNING Postgres warning: CREATE TABLE will
create implicit sequence «types_type_id_seq» for serial column
«types.type_id»

2015-02-27T14:20:37.477000Z WARNING Postgres warning: CREATE TABLE will
create implicit sequence «urls_url_id_seq» for serial column «urls.url_id»

2015-02-27T14:20:37.477000Z NOTICE COPY blacklist_raw

2015-02-27T14:20:37.478000Z NOTICE CREATE UNIQUE INDEX idx_16687_primary ON
blacklist_raw (id);

2015-02-27T14:20:37.478000Z NOTICE COPY categories

2015-02-27T14:20:37.678000Z NOTICE CREATE INDEX
idx_16696_fk_categories_sources_idx ON categories (source_id);

2015-02-27T14:20:37.678000Z NOTICE CREATE INDEX
idx_16696_fk_categories_types1_idx ON categories (type_id);

2015-02-27T14:20:37.678000Z NOTICE CREATE UNIQUE INDEX idx_16696_primary ON
categories (category_id, type_id, source_id);

2015-02-27T14:20:37.678000Z NOTICE COPY domains

2015-02-27T14:20:38.502000Z ERROR Database error 22008: date/time field
value out of range: «0000-00-00 00:00:00»