{"id":19251,"date":"2022-09-24T18:08:56","date_gmt":"2022-09-24T16:08:56","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=19251"},"modified":"2022-09-25T09:59:03","modified_gmt":"2022-09-25T07:59:03","slug":"do-you-really-need-varcharn-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/do-you-really-need-varcharn-in-postgresql\/","title":{"rendered":"Do you really need varchar(n) in PostgreSQL?"},"content":{"rendered":"<p>PostgreSQL comes with three data types for storing characters. char, varchar and text. What we often see at our customers is, that varchar(n) is used when they want to store characters and limit the amount of characters which are allowed to store into the field\/column. Before we go into the details: The only reason you might want to use char or varchar is, that both are defined in the SQL standard. For all other cases you should use text.<br \/>\n<!--more--><br \/>\nLet&#8217;s start with three sample tables, containing one column of char, varchar and text:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">postgres=# create table t1 ( a char(2000) );\nCREATE TABLE\npostgres=# \\d t1\n                     Table \"public.t1\"\n Column |      Type       | Collation | Nullable | Default \n--------+-----------------+-----------+----------+---------\n a      | character(2000) |           |          | \npostgres=# create table t2 ( a varchar(2000) );\nCREATE TABLE\npostgres=# \\d t2\n                         Table \"public.t2\"\n Column |          Type           | Collation | Nullable | Default \n--------+-------------------------+-----------+----------+---------\n a      | character varying(2000) |           |          | \n\npostgres=# create table t3 ( a text );\nCREATE TABLE\npostgres=# \\d t3\n               Table \"public.t3\"\n Column | Type | Collation | Nullable | Default \n--------+------+-----------+----------+---------\n a      | text |           |          | \n\npostgres=# \n<\/pre>\n<p>Tables t1 and t2 can store strings up to the length of 2000 characters (not bytes). Table t3 can store any string up to one GB. To see the first difference between those data types we&#8217;ll populate them with one million rows like this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [5,8,11]\">postgres=# \\timing on\nTiming is on.\npostgres=# insert into t1 select lpad(i::char,100,'0') from generate_series(1,1000000) i; \nINSERT 0 1000000\nTime: 42041.031 ms (00:42.041)\npostgres=# insert into t2 select lpad(i::varchar,100,'0') from generate_series(1,1000000) i; \nINSERT 0 1000000\nTime: 2813.176 ms (00:02.813)\npostgres=# insert into t3 select lpad(i::text,100,'0') from generate_series(1,1000000) i; \nINSERT 0 1000000\nTime: 2790.226 ms (00:02.790)\npostgres=# \n<\/pre>\n<p>What you&#8217;ll notice is, that table t1 containing the char column is much slower to insert data into. There is not much difference between the varchar(n) and text tables. The questions is: Why is the char(n) table so much slower than the other two? The reason is quite obvious if you compare the size of the tables:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">postgres=# select pg_size_pretty(pg_relation_size('t1'));\n pg_size_pretty \n----------------\n 1953 MB\n(1 row)\n\nTime: 1.719 ms\npostgres=# select pg_size_pretty(pg_relation_size('t2'));\n pg_size_pretty \n----------------\n 128 MB\n(1 row)\n\nTime: 0.682 ms\npostgres=# select pg_size_pretty(pg_relation_size('t3'));\n pg_size_pretty \n----------------\n 128 MB\n(1 row)\n\nTime: 0.629 ms\n<\/pre>\n<p>The first table containing the char(n) column is almost 2GB while the other two are only 128MB in size, this is quite a huge difference. The reason is, that char(n) is space padded and will always consume the size of 2000 characters, while varchar(n) and text are dynamic. Dynamic means, they only consume as much space as it is required to store the strings, but not the maximum possible. This already disqualifies char(n).<\/p>\n<p>What about varchar(n) and text? There is no difference when it comes to space consumption on disk. Both use as much space as it is required to store the strings. What is the difference then? The difference is of course the length constraint of the varchar(n) type. You cannot insert more than 2000 characters into the t2 table:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">postgres=# insert into t2 select lpad('i',2000,'0'); \nINSERT 0 1\nTime: 9.201 ms\npostgres=# insert into t2 select lpad('i',2001,'0'); \nERROR:  value too long for type character varying(2000)\nTime: 3.445 ms\n<\/pre>\n<p>The t3 table with the text column does not have such a limit:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">postgres=# insert into t3 select lpad('i',2001,'0'); \nINSERT 0 1\nTime: 9.818 ms\n<\/pre>\n<p>This might seem to be an advantage of the varchar data type, but you can easily have the same behavior for text by adding a constraint:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">postgres=# delete from t3 where a = lpad('i',2001,'0'); \nDELETE 1\nTime: 200.586 ms\npostgres=# alter table t3 add constraint c1 check ( length(a) &lt; 2001 );\nALTER TABLE\nTime: 289.121 ms\npostgres=# insert into t3 select lpad('i',2001,'0');\nERROR:  new row for relation \"t3\" violates check constraint \"c1\"\nDETAIL:  Failing row contains (0000000000000000000000000000000000000000000000000000000000000000...).\nTime: 1.623 ms\n<\/pre>\n<p>Does that make any difference when data inserted?<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">postgres=# truncate t2,t3;\nTRUNCATE TABLE\nTime: 58.063 ms\npostgres=# insert into t2 select lpad(i::varchar,100,'0') from generate_series(1,1000000) i; \nINSERT 0 1000000\nTime: 2281.157 ms (00:02.281)\npostgres=# insert into t3 select lpad(i::text,100,'0') from generate_series(1,1000000) i; \nINSERT 0 1000000\nTime: 2572.467 ms (00:02.572)\n<\/pre>\n<p>Not really. So what is the case for varchar(n)? SQL standard compliance, that&#8217;s it.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL comes with three data types for storing characters. char, varchar and text. What we often see at our customers is, that varchar(n) is used when they want to store characters and limit the amount of characters which are allowed to store into the field\/column. Before we go into the details: The only reason you [&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],"tags":[2602],"type_dbi":[],"class_list":["post-19251","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","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>Do you really need varchar(n) 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\/do-you-really-need-varcharn-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Do you really need varchar(n) in PostgreSQL?\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL comes with three data types for storing characters. char, varchar and text. What we often see at our customers is, that varchar(n) is used when they want to store characters and limit the amount of characters which are allowed to store into the field\/column. Before we go into the details: The only reason you [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/do-you-really-need-varcharn-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-24T16:08:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-09-25T07:59:03+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=\"4 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\\\/do-you-really-need-varcharn-in-postgresql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-really-need-varcharn-in-postgresql\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Do you really need varchar(n) in PostgreSQL?\",\"datePublished\":\"2022-09-24T16:08:56+00:00\",\"dateModified\":\"2022-09-25T07:59:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-really-need-varcharn-in-postgresql\\\/\"},\"wordCount\":406,\"commentCount\":2,\"keywords\":[\"postgresql\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-really-need-varcharn-in-postgresql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-really-need-varcharn-in-postgresql\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-really-need-varcharn-in-postgresql\\\/\",\"name\":\"Do you really need varchar(n) in PostgreSQL? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2022-09-24T16:08:56+00:00\",\"dateModified\":\"2022-09-25T07:59:03+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-really-need-varcharn-in-postgresql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-really-need-varcharn-in-postgresql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-really-need-varcharn-in-postgresql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Do you really need varchar(n) 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\\\/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":"Do you really need varchar(n) 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\/do-you-really-need-varcharn-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Do you really need varchar(n) in PostgreSQL?","og_description":"PostgreSQL comes with three data types for storing characters. char, varchar and text. What we often see at our customers is, that varchar(n) is used when they want to store characters and limit the amount of characters which are allowed to store into the field\/column. Before we go into the details: The only reason you [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/do-you-really-need-varcharn-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2022-09-24T16:08:56+00:00","article_modified_time":"2022-09-25T07:59:03+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/do-you-really-need-varcharn-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/do-you-really-need-varcharn-in-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Do you really need varchar(n) in PostgreSQL?","datePublished":"2022-09-24T16:08:56+00:00","dateModified":"2022-09-25T07:59:03+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/do-you-really-need-varcharn-in-postgresql\/"},"wordCount":406,"commentCount":2,"keywords":["postgresql"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/do-you-really-need-varcharn-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/do-you-really-need-varcharn-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/do-you-really-need-varcharn-in-postgresql\/","name":"Do you really need varchar(n) in PostgreSQL? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-09-24T16:08:56+00:00","dateModified":"2022-09-25T07:59:03+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/do-you-really-need-varcharn-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/do-you-really-need-varcharn-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/do-you-really-need-varcharn-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Do you really need varchar(n) 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\/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\/19251","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=19251"}],"version-history":[{"count":13,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/19251\/revisions"}],"predecessor-version":[{"id":19264,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/19251\/revisions\/19264"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=19251"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=19251"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=19251"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=19251"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}