{"id":13013,"date":"2019-11-18T06:30:47","date_gmt":"2019-11-18T05:30:47","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/"},"modified":"2019-11-18T06:30:47","modified_gmt":"2019-11-18T05:30:47","slug":"fun-with-arrays-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/","title":{"rendered":"Fun with arrays in PostgreSQL"},"content":{"rendered":"

As you might already know, PostgreSQL comes with many, many data types<\/a>. What you might not know is, that you can create arrays<\/a> over all this data types quite easily. Is that important? Well, as always it depends on your requirements but there are use cases where arrays are great. As always, lets do some simple tests.<\/p>\n

<\/p>\n

The following will create very simple table with one column, which is a one-dimensional array of integers.<\/p>\n

\npostgres=# d t1\n                  Table \"public.t1\"\n Column |   Type    | Collation | Nullable | Default \n--------+-----------+-----------+----------+---------\n a      | integer[] |           |          | \n<\/pre>\n
To insert data into that table you would either do it like this:<\/p>\n
\npostgres=# insert into t1 (a) values ( '{1,2,3,4,5,6}' );\nINSERT 0 1\npostgres=# select * from t1;\n       a       \n---------------\n {1,2,3,4,5,6}\n(1 row)\n<\/pre>\n
… or you can do it like this as well:<\/p>\n
\npostgres=# insert into t1 (a) values ( ARRAY[1,2,3,4,5,6] );\nINSERT 0 1\npostgres=# select * from t1;\n       a       \n---------------\n {1,2,3,4,5,6}\n {1,2,3,4,5,6}\n(2 rows)\n<\/pre>\n
Notice that I did not specify any size of the array. Although you can do that:<\/p>\n
\npostgres=# create table t2 ( a int[6] );\nCREATE TABLE\n<\/pre>\n
… the limit is not enforced by PostgreSQL:<\/p>\n
\npostgres=# insert into t2 (a) values ( '{1,2,3,4,5,6,7,8}' );\nINSERT 0 1\npostgres=# select * from t2;\n         a         \n-------------------\n {1,2,3,4,5,6,7,8}\n(1 row)\n<\/pre>\n
PostgreSQL does not limit you to one-dimensional arrays, you can well go ahead and create more dimensions:<\/p>\n
\npostgres=# create table t3 ( a int[], b int[][], c int[][][] );\nCREATE TABLE\npostgres=# d t3\n                  Table \"public.t3\"\n Column |   Type    | Collation | Nullable | Default \n--------+-----------+-----------+----------+---------\n a      | integer[] |           |          | \n b      | integer[] |           |          | \n c      | integer[] |           |          | \n<\/pre>\n
Although it does look like all of the columns are one-dimensional they are actually not:<\/p>\n
\npostgres=# insert into t3 (a,b,c) values ( '{1,2,3}', '{{1,2,3},{1,2,3}}','{{{1,2,3},{1,2,3},{1,2,3}}}' );\nINSERT 0 1\npostgres=# select * from t3;\n    a    |         b         |              c              \n---------+-------------------+-----------------------------\n {1,2,3} | {{1,2,3},{1,2,3}} | {{{1,2,3},{1,2,3},{1,2,3}}}\n(1 row)\n<\/pre>\n
In reality those array columns are not really one-dimensional, you can create as many dimensions as you like even when you think you created one dimension only:<\/p>\n
\npostgres=# create table t4 ( a int[] );\nCREATE TABLE\npostgres=# insert into t4 (a) values ( '{1}' );\nINSERT 0 1\npostgres=# insert into t4 (a) values ( '{1,2}' );\nINSERT 0 1\npostgres=# insert into t4 (a) values ( '{{1,2},{1,2}}' );\nINSERT 0 1\npostgres=# insert into t4 (a) values ( '{{{1,2},{1,2},{1,2}}}' );\nINSERT 0 1\npostgres=# insert into t4 (a) values ( '{{{{1,2},{1,2},{1,2},{1,2}}}}' );\nINSERT 0 1\npostgres=# select * from t4;\n               a               \n-------------------------------\n {1}\n {1,2}\n {{1,2},{1,2}}\n {{{1,2},{1,2},{1,2}}}\n {{{{1,2},{1,2},{1,2},{1,2}}}}\n(5 rows)\n<\/pre>\n
Now that there are some rows: how can we query that? This matches the first two rows of the table:<\/p>\n
\npostgres=# select ctid,* from t4 where a[1] = 1;\n ctid  |   a   \n-------+-------\n (0,1) | {1}\n (0,2) | {1,2}\n(2 rows)\n<\/pre>\n
This matches the second row only:<\/p>\n
\npostgres=# select ctid,* from t4 where a = '{1,2}';\n ctid  |   a   \n-------+-------\n (0,2) | {1,2}\n(1 row)\n<\/pre>\n
This matches row three only:<\/p>\n
\npostgres=# select ctid, * from t4 where a[1:2][1:3] = '{{1,2},{1,2}}';\n ctid  |       a       \n-------+---------------\n (0,3) | {{1,2},{1,2}}\n(1 row)\n<\/pre>\n
You can even index array data types by using a GIN index<\/a>:<\/p>\n
\npostgres=# create index i1 ON t4 using gin (a);\nCREATE INDEX\npostgres=# d t4\n                  Table \"public.t4\"\n Column |   Type    | Collation | Nullable | Default \n--------+-----------+-----------+----------+---------\n a      | integer[] |           |          | \nIndexes:\n    \"i1\" gin (a)\n<\/pre>\n
This does not make much sense right now is we do not have sufficient data for PostgreSQL considering the index, but a as soon as we have more data the index will be helpful:<\/p>\n
\npostgres=# insert into t4 select '{{1,2},{1,2}}' from generate_series(1,1000000);\nINSERT 0 1000000\npostgres=# explain select ctid,* from t4 where a = '{1,2}';\n                            QUERY PLAN                            \n------------------------------------------------------------------\n Bitmap Heap Scan on t4  (cost=28.00..32.01 rows=1 width=51)\n   Recheck Cond: (a = '{1,2}'::integer[])\n   ->  Bitmap Index Scan on i1  (cost=0.00..28.00 rows=1 width=0)\n         Index Cond: (a = '{1,2}'::integer[])\n(4 rows)\n<\/pre>\n
In addition to that PostgreSQL comes with many support functions<\/a> for working with arrays, e.g. to get the length of an array:<\/p>\n
\npostgres=# select array_length(a,1) from t4 limit 2;\n array_length \n--------------\n            1\n            2\n<\/pre>\n
As I mentioned at the beginning of this post you can create arrays of all kinds of data types, not only integers:<\/p>\n
\npostgres=# create table t5 ( a date[], b timestamp[], c text[], d point[], e boolean[] );\nCREATE TABLE\npostgres=# d t5\n                            Table \"public.t5\"\n Column |             Type              | Collation | Nullable | Default \n--------+-------------------------------+-----------+----------+---------\n a      | date[]                        |           |          | \n b      | timestamp without time zone[] |           |          | \n c      | text[]                        |           |          | \n d      | point[]                       |           |          | \n e      | boolean[]                     |           |          | \n<\/pre>\n
Whatever you want. You can even create arrays over user typed types:<\/p>\n
\npostgres=# create type type1 as ( a int, b text );\nCREATE TYPE\npostgres=# create table t6 ( a type1[] );\nCREATE TABLE\npostgres=# d t6\n                 Table \"public.t6\"\n Column |  Type   | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a      | type1[] |           |          | \n<\/pre>\n
Quite powerful.<\/p>\n","protected":false},"excerpt":{"rendered":"
As you might already know, PostgreSQL comes with many, many data types. What you might not know is, that you can create arrays over all this data types quite easily. Is that important? Well, as always it depends on your requirements but there are use cases where arrays are great. As always, lets do some […]<\/p>\n","protected":false},"author":29,"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-13013","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nFun with arrays 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\/fun-with-arrays-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Fun with arrays in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"As you might already know, PostgreSQL comes with many, many data types. What you might not know is, that you can create arrays over all this data types quite easily. Is that important? Well, as always it depends on your requirements but there are use cases where arrays are great. As always, lets do some […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-11-18T05:30:47+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\/fun-with-arrays-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Fun with arrays in PostgreSQL\",\"datePublished\":\"2019-11-18T05:30:47+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/\"},\"wordCount\":319,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/\",\"name\":\"Fun with arrays in PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2019-11-18T05:30:47+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Fun with arrays 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 &\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":"Fun with arrays 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\/fun-with-arrays-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Fun with arrays in PostgreSQL","og_description":"As you might already know, PostgreSQL comes with many, many data types. What you might not know is, that you can create arrays over all this data types quite easily. Is that important? Well, as always it depends on your requirements but there are use cases where arrays are great. As always, lets do some […]","og_url":"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2019-11-18T05:30:47+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\/fun-with-arrays-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Fun with arrays in PostgreSQL","datePublished":"2019-11-18T05:30:47+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/"},"wordCount":319,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/","name":"Fun with arrays in PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-11-18T05:30:47+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/fun-with-arrays-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Fun with arrays 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 &\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\/13013","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=13013"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13013\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=13013"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=13013"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=13013"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=13013"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}