{"id":21018,"date":"2022-12-13T11:06:59","date_gmt":"2022-12-13T10:06:59","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=21018"},"modified":"2022-12-13T11:06:59","modified_gmt":"2022-12-13T10:06:59","slug":"new-cast-testing-functions-for-postgresql-16","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/","title":{"rendered":"New cast testing functions for PostgreSQL 16"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">As the development of PostgreSQL moves on, more and more new features get committed. A recently commit patch added two new functions, which can be used to validate if a cast will work and what error message would be returned if the cast fails. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Before looking at the new functions in PostgreSQL 16, lets have a look at what happens in version before 16 if a cast fails:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# select &#039;aaaa&#039;::int;\nERROR:  invalid input syntax for type integer: &quot;aaaa&quot;\nLINE 1: select &#039;aaaa&#039;::int;\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Not really a surprise, this fails. The downside with this is, that it will also abort the transaction it is failing in:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# begin;\nBEGIN\npostgres=*# select 1;\n ?column? \n----------\n        1\n(1 row)\n\npostgres=*# select &#039;aaaa&#039;::int;\nERROR:  invalid input syntax for type integer: &quot;aaaa&quot;\nLINE 1: select &#039;aaaa&#039;::int;\n               ^\npostgres=!# select 1;\nERROR:  current transaction is aborted, commands ignored until end of transaction block\npostgres=!# end;\nROLLBACK\npostgres=# \n\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">With PostgreSQL 16 this can be done more convenient. The first new function is called pg_input_is_valid and can be used to test if a cast will succeed or fail:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# select pg_input_is_valid(&#039;aaa&#039;,&#039;text&#039;);\n pg_input_is_valid \n-------------------\n t\n(1 row)\n\npostgres=# select pg_input_is_valid(&#039;aaa&#039;,&#039;int&#039;);\n pg_input_is_valid \n-------------------\n f\n(1 row)\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">This gives you more control if you are in a transaction, as you now can test before you actually execute the cast:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# begin;\nBEGIN\npostgres=*# select 1;\n ?column? \n----------\n        1\n(1 row)\n\npostgres=*# select pg_input_is_valid(&#039;aaa&#039;,&#039;int&#039;);\n pg_input_is_valid \n-------------------\n f\n(1 row)\n\npostgres=*# select pg_input_is_valid(&#039;aaa&#039;,&#039;text&#039;);\n pg_input_is_valid \n-------------------\n t\n(1 row)\n\npostgres=*# end;\nCOMMIT\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">The second new function is called pg_input_error_message and will give you the error message you would get, if a cast fails:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# select pg_input_error_message(&#039;aaaa&#039;,&#039;int&#039;);\n            pg_input_error_message             \n-----------------------------------------------\n invalid input syntax for type integer: &quot;aaaa&quot;\n(1 row)\n\npostgres=# select pg_input_error_message(&#039;22.22&#039;,&#039;int&#039;);\n             pg_input_error_message             \n------------------------------------------------\n invalid input syntax for type integer: &quot;22.22&quot;\n(1 row)\n\npostgres=# \n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Nice.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>As the development of PostgreSQL moves on, more and more new features get committed. A recently commit patch added two new functions, which can be used to validate if a cast will work and what error message would be returned if the cast fails. Before looking at the new functions in PostgreSQL 16, lets have&hellip;&nbsp;<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[4133],"tags":[],"class_list":["post-21018","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>New cast testing functions for PostgreSQL 16 - dbi services | Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"New cast testing functions for PostgreSQL 16\" \/>\n<meta property=\"og:description\" content=\"As the development of PostgreSQL moves on, more and more new features get committed. A recently commit patch added two new functions, which can be used to validate if a cast will work and what error message would be returned if the cast fails. Before looking at the new functions in PostgreSQL 16, lets have&hellip;&nbsp;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi services | Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-12-13T10:06:59+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-cast-testing-functions-for-postgresql-16\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-cast-testing-functions-for-postgresql-16\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\"},\"headline\":\"New cast testing functions for PostgreSQL 16\",\"datePublished\":\"2022-12-13T10:06:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-cast-testing-functions-for-postgresql-16\\\/\"},\"wordCount\":172,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"articleSection\":[\"Database\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-cast-testing-functions-for-postgresql-16\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-cast-testing-functions-for-postgresql-16\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-cast-testing-functions-for-postgresql-16\\\/\",\"name\":\"New cast testing functions for PostgreSQL 16 - dbi services | Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2022-12-13T10:06:59+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-cast-testing-functions-for-postgresql-16\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-cast-testing-functions-for-postgresql-16\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-cast-testing-functions-for-postgresql-16\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"New cast testing functions for PostgreSQL 16\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"alternateName\":\"dbi Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\",\"name\":\"dbi services\",\"alternateName\":\"dbi\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"caption\":\"Open source Team\"},\"description\":\"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"New cast testing functions for PostgreSQL 16 - dbi services | Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/","og_locale":"en_US","og_type":"article","og_title":"New cast testing functions for PostgreSQL 16","og_description":"As the development of PostgreSQL moves on, more and more new features get committed. A recently commit patch added two new functions, which can be used to validate if a cast will work and what error message would be returned if the cast fails. Before looking at the new functions in PostgreSQL 16, lets have&hellip;&nbsp;","og_url":"https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/","og_site_name":"dbi services | Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2022-12-13T10:06:59+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","twitter_misc":{"Written by":"Open source Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a"},"headline":"New cast testing functions for PostgreSQL 16","datePublished":"2022-12-13T10:06:59+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/"},"wordCount":172,"commentCount":0,"publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"articleSection":["Database"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/","url":"https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/","name":"New cast testing functions for PostgreSQL 16 - dbi services | Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-12-13T10:06:59+00:00","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"New cast testing functions for PostgreSQL 16"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"alternateName":"dbi Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.dbi-services.com\/blog\/#organization","name":"dbi services","alternateName":"dbi","url":"https:\/\/www.dbi-services.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","caption":"Open source Team"},"description":"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.","url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/21018","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=21018"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/21018\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=21018"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=21018"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=21018"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}