{"id":36890,"date":"2024-07-08T11:32:01","date_gmt":"2024-07-08T01:32:01","guid":{"rendered":"https:\/\/database.guide\/?p=36890"},"modified":"2024-07-08T11:32:03","modified_gmt":"2024-07-08T01:32:03","slug":"understanding-postgresqls-pg_input_error_info-function","status":"publish","type":"post","link":"https:\/\/database.guide\/understanding-postgresqls-pg_input_error_info-function\/","title":{"rendered":"Understanding PostgreSQL’s pg_input_error_info() Function"},"content":{"rendered":"\n

PostgreSQL has a pg_input_error_info()<\/code> function that allows us to see the error details that would be returned if we were to pass an invalid value for the specified data type.<\/p>\n\n\n\n

Basically it tests whether the given string is valid input for the specified data type. If invalid, it returns the details of the error that would have been thrown. If valid, the result is null<\/code>.<\/p>\n\n\n\n\n\n\n\n

The pg_input_error_info()<\/code> function was introduced in PostgreSQL 16, which was released on September 14th 2023. The function was initially started as pg_input_error_message()<\/code>, as the initial intention was to return just the error message. But it was later decided that it should return more information, such as the sql error code, error detail (if set), and hint (if set) and so it was renamed to pg_input_error_info()<\/code> to reflect this.<\/p>\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

pg_input_error_info ( string text, type text ) <\/code><\/pre>\n\n\n\n
The function returns its results as a record<\/code>:<\/p>\n\n\n\n
record ( message text, detail text, hint text, sql_error_code text )<\/pre>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s an example to demonstrate:<\/p>\n\n\n\n
SELECT * FROM pg_input_error_info('1.5', 'integer');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
                   message                    | detail | hint | sql_error_code 
----------------------------------------------+--------+------+----------------
 invalid input syntax for type integer: \"1.5\" | null   | null | 22P02<\/pre>\n\n\n\n
Here, my first argument was 1.5<\/code>, and that was validated against my second argument (integer<\/code>). An integer can’t have a decimal point and so this would result in an error. The pg_input_error_info()<\/code> function kindly provided information about the error that I would get if I were to try to provide that value as input to an integer<\/code>.<\/p>\n\n\n\n
Here are some more examples:<\/p>\n\n\n\n
SELECT * FROM pg_input_error_info('dog', 'integer')\nUNION ALL\nSELECT * FROM pg_input_error_info('{5}', 'smallint')\nUNION ALL\nSELECT * FROM pg_input_error_info('7', 'integer[]')\nUNION ALL\nSELECT * FROM pg_input_error_info('3', 'boolean');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
                    message                    |                          detail                           | hint | sql_error_code 
-----------------------------------------------+-----------------------------------------------------------+------+----------------
 invalid input syntax for type integer: \"dog\"  | null                                                      | null | 22P02
 invalid input syntax for type smallint: \"{5}\" | null                                                      | null | 22P02
 malformed array literal: \"7\"                  | Array value must start with \"{\" or dimension information. | null | 22P02
 invalid input syntax for type boolean: \"3\"    | null                                                      | null | 22P02<\/pre>\n\n\n\n
No Error<\/h2>\n\n\n\n
If the input value wouldn’t result in an error, then null<\/code> is returned in all columns:<\/p>\n\n\n\n
SELECT * FROM pg_input_error_info('7', 'integer');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
 message | detail | hint | sql_error_code 
---------+--------+------+----------------
 null    | null   | null | null<\/pre>\n\n\n\n
Selecting Just the Error Message or Code<\/h2>\n\n\n\n
We can select any of the columns returned by pg_input_error_info()<\/code> simply by naming the desired column in our SELECT<\/a><\/code> list:<\/p>\n\n\n\n
SELECT message FROM pg_input_error_info('dog', 'integer');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
                   message                    
----------------------------------------------
 invalid input syntax for type integer: \"dog\"<\/pre>\n\n\n\n
And here’s the error code:<\/p>\n\n\n\n
SELECT sql_error_code FROM pg_input_error_info('dog', 'integer');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
 sql_error_code 
----------------
 22P02<\/pre>\n\n\n\n
Returning the Raw Record<\/h2>\n\n\n\n
If we select the function without specifying the columns, we get the record as is (without it being separated into columns):<\/p>\n\n\n\n
SELECT pg_input_error_info('dog', 'integer');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
                    pg_input_error_info                     
------------------------------------------------------------
 (\"invalid input syntax for type integer: \"\"dog\"\"\",,,22P02)<\/pre>\n\n\n\n
Passing the Wrong Argument Type<\/h2>\n\n\n\n
Passing an argument of the wrong type results in an actual error:<\/p>\n\n\n\n
SELECT * FROM pg_input_error_info(7, 'integer');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR:  function pg_input_error_info(integer, unknown) does not exist
LINE 1: SELECT * FROM pg_input_error_info(7, 'integer');
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.<\/pre>\n\n\n\n
Validating the Input<\/h2>\n\n\n\n
PostgreSQL also has a pg_input_is_valid()<\/code> function<\/a> that simply checks whether or not the value is valid against the data type, returning a boolean value of either true<\/code> or false<\/code>.<\/p>\n","protected":false},"excerpt":{"rendered":"
PostgreSQL has a pg_input_error_info() function that allows us to see the error details that would be returned if we were to pass an invalid value for the specified data type. Basically it tests whether the given string is valid input for the specified data type. If invalid, it returns the details of the error that would … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[93,74,20],"class_list":["post-36890","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-functions","tag-system-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36890","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=36890"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36890\/revisions"}],"predecessor-version":[{"id":36916,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36890\/revisions\/36916"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=36890"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=36890"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=36890"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}