{"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:07:00","modified_gmt":"2022-12-13T10:07:00","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>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>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>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>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>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>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>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;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[2602],"type_dbi":[],"class_list":["post-21018","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql-2"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>New cast testing functions for PostgreSQL 16 - dbi 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;]\" \/>\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 Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-12-13T10:06:59+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-12-13T10:07:00+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\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\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"New cast testing functions for PostgreSQL 16\",\"datePublished\":\"2022-12-13T10:06:59+00:00\",\"dateModified\":\"2022-12-13T10:07:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/new-cast-testing-functions-for-postgresql-16\\\/\"},\"wordCount\":172,\"commentCount\":0,\"keywords\":[\"postgresql\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\"],\"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 Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2022-12-13T10:06:59+00:00\",\"dateModified\":\"2022-12-13T10:07:00+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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 Blog\",\"description\":\"\",\"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\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\\\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/westermanndanie\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-westermann\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"New cast testing functions for PostgreSQL 16 - dbi 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;]","og_url":"https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/","og_site_name":"dbi Blog","article_published_time":"2022-12-13T10:06:59+00:00","article_modified_time":"2022-12-13T10:07:00+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","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":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"New cast testing functions for PostgreSQL 16","datePublished":"2022-12-13T10:06:59+00:00","dateModified":"2022-12-13T10:07:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/new-cast-testing-functions-for-postgresql-16\/"},"wordCount":172,"commentCount":0,"keywords":["postgresql"],"articleSection":["Database Administration &amp; Monitoring","Database management"],"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 Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-12-13T10:06:59+00:00","dateModified":"2022-12-13T10:07:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"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 Blog","description":"","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":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_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\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=21018"}],"version-history":[{"count":2,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/21018\/revisions"}],"predecessor-version":[{"id":21020,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/21018\/revisions\/21020"}],"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"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=21018"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}