{"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":28,"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 v28.8 (Yoast SEO v28.8) - 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=\"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=\"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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/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":"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":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","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":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/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\/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\/28"}],"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}]}}