{"id":9798,"date":"2017-02-20T17:15:58","date_gmt":"2017-02-20T16:15:58","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/converting-a-column-from-one-data-type-to-another-in-postgresql\/"},"modified":"2017-02-20T17:15:58","modified_gmt":"2017-02-20T16:15:58","slug":"converting-a-column-from-one-data-type-to-another-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/converting-a-column-from-one-data-type-to-another-in-postgresql\/","title":{"rendered":"Converting a column from one data type to another in PostgreSQL"},"content":{"rendered":"<p>Last week at a customer the following question came up: We have a varchar2 column in Oracle that contains a date but actually is stored as a string. When we want to convert this column to be a real date data type in Oracle we can use <a href=\"http:\/\/docs.oracle.com\/database\/122\/ARPLS\/DBMS_REDEFINITION.htm\" target=\"_blank\" rel=\"noopener\">dbms_redefinition<\/a>. How can we do that in PostgreSQL? The first answer that came to me mind is: Add a new column (of data type date) and populate it with the converted string from the source column, drop the source column and rename the new column to the name of the dropped column. This for sure will work but it is not the most convenient way: What happens if the application is somehow dependent on the order of the columns? Shouldn&#8217;t be the case if the application is written well, but you never know.<\/p>\n<p><!--more--><\/p>\n<p>To start with lets generate some test data:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">drop table if exists t1;\ncreate table t1 ( a varchar(20) );\ninsert into t1 (a) values ('01012017');\ninsert into t1 (a) values ('02012017');\ninsert into t1 (a) values ('03012017');\ninsert into t1 (a) values ('04012017');\ninsert into t1 (a) values ('05012017');\ninsert into t1 (a) values ('06012017');\ninsert into t1 (a) values ('07012017');\ninsert into t1 (a) values ('08012017');\ninsert into t1 (a) values ('09012017');\ninsert into t1 (a) values ('10012017');\ninsert into t1 (a) values ('11012017');\n(postgres@[local]:5440) [postgres] &gt; select * from t1;\n    a     \n----------\n 01012017\n 02012017\n 03012017\n 04012017\n 05012017\n 06012017\n 07012017\n 08012017\n 09012017\n 10012017\n 11012017\n(11 rows)\n<\/pre>\n<p>We now have a varchar column holding the date values as a string. When you look at the &#8220;alter table&#8221; command in PostgtreSQL you&#8217;ll notice something like this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [5]\">where action is one of:\n\n    ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]\n    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]\n    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]\n<\/pre>\n<p>What will really help here is the &#8220;using&#8221; keyword because you can do things like this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">(postgres@[local]:5440) [postgres] &gt; alter table t1 alter column a type date using to_date(a,'DDMMYYYY');\nALTER TABLE\n<\/pre>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">(postgres@[local]:5440) [postgres] &gt; d t1\n     Table \"public.t1\"\n Column | Type | Modifiers \n--------+------+-----------\n a      | date | \n<\/pre>\n<p>That&#8217;s really cool: You can pass a function (this mean a self written function as well) to the alter command to specify on how you want to do the conversion.<\/p>\n<p>When there is an index on the column, what happens to the index?<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">(postgres@[local]:5440) [postgres] &gt; d t1\n             Table \"public.t1\"\n Column |         Type          | Modifiers \n--------+-----------------------+-----------\n a      | character varying(20) | \nIndexes:\n    \"i1\" btree (a)\n(postgres@[local]:5440) [postgres] &gt; alter table t1 alter column a type date using to_date(a,'DDMMYYYY');\nALTER TABLE\nTime: 5.931 ms\n(postgres@[local]:5440) [postgres] &gt; d t1\n     Table \"public.t1\"\n Column | Type | Modifiers \n--------+------+-----------\n a      | date | \nIndexes:\n    \"i1\" btree (a)\n(postgres@[local]:5440) [postgres] &gt; select indisvalid,indisready,indislive from pg_index where indexrelid = 'i1'::regclass; \n indisvalid | indisready | indislive \n------------+------------+-----------\n t          | t          | t\n(1 row)\n<\/pre>\n<p>Looks fine as well, lets do a quick test if the index is really usable:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">Time: 0.453 ms\n(postgres@[local]:5440) [postgres] &gt; insert into t1 select * from t1;\nINSERT 0 11\nTime: 2.373 ms\n(postgres@[local]:5440) [postgres] &gt; insert into t1 select * from t1;\nINSERT 0 22\nTime: 39.653 ms\n(postgres@[local]:5440) [postgres] &gt; insert into t1 select * from t1;\nINSERT 0 44\nTime: 1.110 ms\n(postgres@[local]:5440) [postgres] &gt; insert into t1 select * from t1;\nINSERT 0 88\nTime: 1.072 ms\n(postgres@[local]:5440) [postgres] &gt; insert into t1 select * from t1;\nINSERT 0 176\nTime: 1.455 ms\n(postgres@[local]:5440) [postgres] &gt; insert into t1 select * from t1;\nINSERT 0 352\nTime: 1.432 ms\n(postgres@[local]:5440) [postgres] &gt; insert into t1 select * from t1;\nINSERT 0 704\nTime: 3.344 ms\n(postgres@[local]:5440) [postgres] &gt; insert into t1 select * from t1;\nINSERT 0 1408\nTime: 20.972 ms\n(postgres@[local]:5440) [postgres] &gt; explain analyze select a from t1 where a = to_date('01012017','dd.mm.yyyy');\n                                                 QUERY PLAN                                                 \n------------------------------------------------------------------------------------------------------------\n Index Only Scan using i1 on t1  (cost=0.27..8.29 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=1)\n   Index Cond: (a = to_date('01012017'::text, 'dd.mm.yyyy'::text))\n   Heap Fetches: 0\n Planning time: 0.062 ms\n Execution time: 0.029 ms\n(5 rows)\n<\/pre>\n<p>Perfect. The remaining question is: Does this operation block others from reading the table while it is being executed? Lets generate some more date to make the select operation a bit longer and then &#8220;watch&#8221; the statement in a separate session while we execute the conversion:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">Time: 0.453 ms\ndrop table if exists t1;\ncreate table t1 ( a varchar(20) );\ninsert into t1 (a) values ('01012017');\ninsert into t1 (a) values ('02012017');\ninsert into t1 (a) values ('03012017');\ninsert into t1 (a) values ('04012017');\ninsert into t1 (a) values ('05012017');\ninsert into t1 (a) values ('06012017');\ninsert into t1 (a) values ('07012017');\ninsert into t1 (a) values ('08012017');\ninsert into t1 (a) values ('09012017');\ninsert into t1 (a) values ('10012017');\ninsert into t1 (a) values ('11012017');\n(postgres@[local]:5440) [postgres] &gt; insert into t1 select * from t1;\nINSERT 0 11\n-- and so on and so on ...\n(postgres@[local]:5440) [postgres] &gt; insert into t1 select * from t1;\nINSERT 0 360448\n<\/pre>\n<p>Then, in session 1 I am doing this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">Time: 0.453 ms\n(postgres@[local]:5440) [postgres] &gt; select count(*) from t1 where a = '01012017';\n count  \n--------\n 131072\n(1 row)\n(postgres@[local]:5440) [postgres] &gt; watch 0.1\n<\/pre>\n<p>In session 2 I am doing the conversion:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">Time: 0.453 ms\n(postgres@[local]:5440) [postgres] &gt; alter table t1 alter column a type date using to_date(a,'DDMMYYYY');\nALTER TABLE\n<\/pre>\n<p>In session 1 you&#8217;ll notice that the statement is blocked and finally you get this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">Time: 0.453 ms\nERROR:  date\/time field value out of range: \"01012017\"\nLINE 1: select count(*) from t1 where a = '01012017';\n                                          ^\nHINT:  Perhaps you need a different \"datestyle\" setting.\n<\/pre>\n<p>Conclusion: Converting a column from one data type to another is nothing you want to do when you system is live. When you can afford some downtime the &#8220;using&#8221; syntax is what you want to do and it is working quite fast. Hope this helps &#8230;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Last week at a customer the following question came up: We have a varchar2 column in Oracle that contains a date but actually is stored as a string. When we want to convert this column to be a real date data type in Oracle we can use dbms_redefinition. How can we do that in PostgreSQL? [&hellip;]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-9798","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"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>Converting a column from one data type to another in PostgreSQL - 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\/converting-a-column-from-one-data-type-to-another-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Converting a column from one data type to another in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"Last week at a customer the following question came up: We have a varchar2 column in Oracle that contains a date but actually is stored as a string. When we want to convert this column to be a real date data type in Oracle we can use dbms_redefinition. How can we do that in PostgreSQL? [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/converting-a-column-from-one-data-type-to-another-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-02-20T16:15:58+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\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=\"5 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\\\/converting-a-column-from-one-data-type-to-another-in-postgresql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-a-column-from-one-data-type-to-another-in-postgresql\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"Converting a column from one data type to another in PostgreSQL\",\"datePublished\":\"2017-02-20T16:15:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-a-column-from-one-data-type-to-another-in-postgresql\\\/\"},\"wordCount\":385,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-a-column-from-one-data-type-to-another-in-postgresql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-a-column-from-one-data-type-to-another-in-postgresql\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-a-column-from-one-data-type-to-another-in-postgresql\\\/\",\"name\":\"Converting a column from one data type to another in PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2017-02-20T16:15:58+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-a-column-from-one-data-type-to-another-in-postgresql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-a-column-from-one-data-type-to-another-in-postgresql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/converting-a-column-from-one-data-type-to-another-in-postgresql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Converting a column from one data type to another in PostgreSQL\"}]},{\"@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\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Converting a column from one data type to another in PostgreSQL - 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\/converting-a-column-from-one-data-type-to-another-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Converting a column from one data type to another in PostgreSQL","og_description":"Last week at a customer the following question came up: We have a varchar2 column in Oracle that contains a date but actually is stored as a string. When we want to convert this column to be a real date data type in Oracle we can use dbms_redefinition. How can we do that in PostgreSQL? [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/converting-a-column-from-one-data-type-to-another-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2017-02-20T16:15:58+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/converting-a-column-from-one-data-type-to-another-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/converting-a-column-from-one-data-type-to-another-in-postgresql\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"Converting a column from one data type to another in PostgreSQL","datePublished":"2017-02-20T16:15:58+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/converting-a-column-from-one-data-type-to-another-in-postgresql\/"},"wordCount":385,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/converting-a-column-from-one-data-type-to-another-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/converting-a-column-from-one-data-type-to-another-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/converting-a-column-from-one-data-type-to-another-in-postgresql\/","name":"Converting a column from one data type to another in PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-02-20T16:15:58+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/converting-a-column-from-one-data-type-to-another-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/converting-a-column-from-one-data-type-to-another-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/converting-a-column-from-one-data-type-to-another-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Converting a column from one data type to another in PostgreSQL"}]},{"@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\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"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\/9798","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=9798"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9798\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9798"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9798"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9798"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9798"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}