In PostgreSQL we have the ability to create variable-length multidimensional arrays and define columns as array types.<\/p>\n\n\n\n
Here’s a quick overview of PostgreSQL arrays.<\/p>\n\n\n\n\n\n\n\n
We have a few options when it comes to creating a column with an array type in PostgreSQL. Regardless of which method we use, we need to specify the data type of the elements within the array.<\/p>\n\n\n\n
We don’t use the keyword One way to define a column as an array is to append square brackets ( Here’s an example:<\/p>\n\n\n\n That code creates a table with three columns. All three columns are defined as array types. <\/p>\n\n\n\n At the time of writing, PostgreSQL 16.2 does not enforce the number of dimensions. So we could insert say, a two dimensional array into our It’s possible to specify the array size, but PostgreSQL basically ignores this (at least as of PostgreSQL 16 at the time of writing). So we could do the following:<\/p>\n\n\n\n But as alluded to, PostgreSQL does not enforce any such size restrictions. However, arrays must be rectangular. Therefore, all sub-arrays at the same level must be of the same dimensions.<\/p>\n\n\n\n We can also use the We have the option of specifying the array size by appending square brackets to the Using the Once we’ve created a table or column that accepts an array type, we can go ahead and insert an array into it.<\/p>\n\n\n\n One way to insert an array is to provide it as a literal constant:<\/p>\n\n\n\n Here, all three arrays are provided as a literal constant. These are provided in the format specified by the following syntax:<\/p>\n\n\n\n Here, Each Another way to insert an array is with the We can also use the Result:<\/p>\n\n\n\n As mentioned, all sub-arrays at the same level must have the same dimensions. Therefore, we can do this:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n But we can’t do this:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n Here, the outer array contains two inner arrays. The first one is defined as having three elements but the second has two. This is a “no no”, and we got an error accordingly.<\/p>\n\n\n\n Let’s return all data from our table:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n As expected, the rows reflect the data that we inserted.<\/p>\n\n\n\n We can return any of these arrays by simply including that column in our Result:<\/p>\n\n\n\n In this case we returned the arrays in the We can select specific elements by specifying the element index within square brackets.<\/p>\n\n\n\n Here’s an example:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n Array subscripts start at 1 by default, and so We can also use this syntax in a Result:<\/p>\n\n\n\n In the above example we returned the whole array, but we can also return individual elements from within that array:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n We can access elements from multi dimensional arrays by using multiple sets of square brackets:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n In this case we retrieved the third element of the second array in the We can return parts of an array by using a syntax that specifies the lower bound and the upper bound for the elements. This is written with a colon syntax, where the lower bound and upper bound is separated by a colon.<\/p>\n\n\n\n Example:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n Here we extracted the second, third and fourth elements of each array.<\/p>\n\n\n\n We can leave the lower or upper bound open, so that it returns all prior or subsequent elements from the given point. For example, if we omit our upper bound of 4, we get the following:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n This time it returned all elements from the second one to the end of the array.<\/p>\n\n\n\n We can do a similar thing to get all elements from the start of the array up to a certain point:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n This time we got all elements up to the second element.<\/p>\n\n\n\n On multi dimensional arrays we can use multiple pairs of square brackets:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n In this case I got the second and third elements of the first two arrays in the array at Note that if any dimension is written as a slice (i.e. using the colon syntax) then all dimensions are treated as slices, even if not all of them are written as a slice (i.e. no colon). Therefore we could write the following:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n Here, we only included a colon in the first pair of square brackets but not the second. Any dimension that doesn’t contain a colon is treated as starting from 1 and ending at the specified number. Therefore in our example, the We can use the Result:<\/p>\n\n\n\n Here it is across all columns:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n Another way to do it is with the Result:<\/p>\n\n\n\n That returned the upper and lower bounds for the second dimension of the The We can use the Result:<\/p>\n\n\n\n Here we can see that the function returned the total number of elements across the arrays at Previously we selected the value from a given index of an array. But we also have the option of searching through the whole array for a given value. <\/p>\n\n\n\n One way to do this is with the Result:<\/p>\n\n\n\n We can also use Another way to search through an array is to use the Result:<\/p>\n\n\n\n In this case all elements of Another tool we have for searching arrays is the Result:<\/p>\n\n\n\n The We can use The Result:<\/p>\n\n\n\n We can replace an array in full by using the literal constant:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n Or by using the Result:<\/p>\n\n\n\n We can update a specific array element by referencing its index:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n And here it is updating a multi dimensional array:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n We can update a whole slice like this:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n We can also leave one of the bounds open in order to update all remaining or preceding elements. For example, the following updates all elements from the fourth subscript:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n Here’s what happens if we provide too many values:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n Only the remaining elements were updated. So in our case, the first two elements ( We can enlarge an array by assigning an element to an index that doesn’t currently exist:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n When we do this, any positions between existing elements and the specified one\/s will be set to By default, the lower bound of an array is 1. This means that the first element starts at 1, the second starts at 2, and so on.<\/p>\n\n\n\n But we do have the option of changing this. <\/p>\n\n\n\n We can use subscript assignment to specify the subscript for the array. We can do this by prepending the array with the subscript ranges enclosed in square brackets, with the dimensions separated by a colon.<\/p>\n\n\n\n Example:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n In this example I created a zero based array. In other words, the subscripts start at zero. <\/p>\n\n\n\n Therefore, in order to get the first item, we need to use We can use this approach when inserting data into a table:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n We can see that the arrays are inserted along with their respective subscript assignments.<\/p>\n\n\n\n We can reference the array elements by using the subscript based on the specified assignment. For example:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n In this case Badger is at We can push new values to an array by concatenating the value with the array:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n Here’s an example that prepends and appends a value to an array from the database:<\/p>\n\n\n\n Result (using vertical output):<\/p>\n\n\n\n For this example I used We can also use the Result (using vertical output):<\/p>\n\n\n\n The Here’s an example:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n And here’s one using an array from the database:<\/p>\n\n\n\n Result:<\/p>\n\n\n\n <\/p>\n\n\n\n <\/p>\n","protected":false},"excerpt":{"rendered":" In PostgreSQL we have the ability to create variable-length multidimensional arrays and define columns as array types. Here’s a quick overview of PostgreSQL arrays.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[169,91,20],"class_list":["post-34685","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-arrays","tag-data-types","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/34685","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=34685"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/34685\/revisions"}],"predecessor-version":[{"id":36357,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/34685\/revisions\/36357"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=34685"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=34685"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=34685"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}array<\/code> in the same fashion that we do when defining other types.<\/p>\n\n\n\n[]<\/code>) to the data type name of the array elements.<\/p>\n\n\n\nCREATE TABLE array_test(\n c1 integer[],\n c2 text[],\n c3 integer[][]\n);<\/code><\/pre>\n\n\n\n\n
c1<\/code> column is a single dimensional integer array, which means it accepts an array of integer values. We know it’s an array type because we appended square brackets to the data type name. And we know it’s single dimensional because it only has one pair of square brackets.<\/li>\n\n\n\nc2<\/code> column is a single dimensional text array. <\/li>\n\n\n\nc3<\/code> array is a multi dimensional integer array. It’s multi dimensional because we included more than one pair of square brackets. In this case there are two sets of square brackets, so it’s a two dimensional array.<\/li>\n<\/ul>\n\n\n\nc1<\/code> column, or a one dimensional array into the c3<\/code> column for example.<\/p>\n\n\n\nCREATE TABLE array_test_2(\n c1 integer[3],\n c2 text[4],\n c3 integer[3][2]\n);<\/code><\/pre>\n\n\n\nARRAY<\/code> keyword to define a column as a one dimensional array:<\/p>\n\n\n\nCREATE TABLE array_test_3(\n c1 integer ARRAY,\n c2 text ARRAY[4],\n c3 integer ARRAY\n);<\/code><\/pre>\n\n\n\nARRAY<\/code> keyword (as seen in the definition for c2<\/code>) but again, such size limits are not enforced by PostgreSQL.<\/p>\n\n\n\nARRAY<\/code> keyword helps keep the code compliant with the SQL standard, but is entirely optional.<\/p>\n\n\n\nInserting an Array<\/h2>\n\n\n\n
INSERT INTO array_test VALUES \n (\n '{ 1,2,3 }',\n '{ \"Cat\", \"Dog\", \"Horse\", \"Bear\", \"Duck\" }',\n '{ {1,2,3}, {4,5,6}, {7,8,9} }'\n );<\/code><\/pre>\n\n\n\n'{ val1 delim val2 delim ... }'<\/code><\/pre>\n\n\n\ndelim<\/code><\/em> is the delimiter character for the type. This delimiter is stored in the pg_type<\/code> catalog. Most of the standard data types use a comma (,<\/code>), but the box<\/code> type uses a semicolon (;<\/code>). <\/p>\n\n\n\nval<\/code><\/em> is either a constant of the array element type, or a subarray.<\/p>\n\n\n\nARRAY<\/code> constructor<\/a>:<\/p>\n\n\n\nINSERT INTO array_test VALUES \n (\n ARRAY[ 41, 52, 67 ],\n ARRAY[ 'Zebra', 'Bird', 'Mouse', 'Cow', 'Pig' ],\n ARRAY[ ARRAY[ 32, 78, 14 ], ARRAY[ 102, 99, 37 ], ARRAY[ 18, 65, 29 ] ]\n );<\/code><\/pre>\n\n\n\nARRAY<\/code> constructor by itself to create an array on the fly:<\/p>\n\n\n\nSELECT ARRAY[ 1,2,3 ];<\/code><\/pre>\n\n\n\n array
---------
{1,2,3}<\/pre>\n\n\n\nSELECT ARRAY[ ARRAY[1,2,3], ARRAY[4,5,6] ];<\/code><\/pre>\n\n\n\n array
-------------------
{{1,2,3},{4,5,6}}<\/pre>\n\n\n\nSELECT ARRAY[ ARRAY[1,2,3], ARRAY[4,5] ];<\/code><\/pre>\n\n\n\nERROR: multidimensional arrays must have array expressions with matching dimensions<\/pre>\n\n\n\n
Selecting an Array<\/h2>\n\n\n\n
SELECT * FROM array_test;<\/code><\/pre>\n\n\n\n c1 | c2 | c3
------------+----------------------------+-------------------------------------
{1,2,3} | {Cat,Dog,Horse,Bear,Duck} | {{1,2,3},{4,5,6},{7,8,9}}
{41,52,67} | {Zebra,Bird,Mouse,Cow,Pig} | {{32,78,14},{102,99,37},{18,65,29}}<\/pre>\n\n\n\nSELECT<\/code> list:<\/p>\n\n\n\nSELECT c2 FROM array_test;<\/code><\/pre>\n\n\n\n c2
----------------------------
{Cat,Dog,Horse,Bear,Duck}
{Zebra,Bird,Mouse,Cow,Pig}<\/pre>\n\n\n\nc2<\/code> column. We didn’t narrow it down with a WHERE<\/code> clause or anything, so all rows were returned.<\/p>\n\n\n\nSelecting Array Elements<\/h2>\n\n\n\n
SELECT c2[1] FROM array_test;<\/code><\/pre>\n\n\n\n c2
-------
Cat
Zebra<\/pre>\n\n\n\nc2[1]<\/code> returns the first element of the array. <\/p>\n\n\n\nWHERE<\/code> clause to narrow the results down:<\/p>\n\n\n\nSELECT c2 FROM array_test \nWHERE c1[1] = 41;<\/code><\/pre>\n\n\n\n c2
----------------------------
{Zebra,Bird,Mouse,Cow,Pig}<\/pre>\n\n\n\nSELECT c2[1] FROM array_test \nWHERE c1[1] = 41; <\/code><\/pre>\n\n\n\n c2
-------
Zebra<\/pre>\n\n\n\nSELECT c3[2][3] FROM array_test;<\/code><\/pre>\n\n\n\n c3
----
6
37<\/pre>\n\n\n\nc3<\/code> column.<\/p>\n\n\n\nReturning Slices from Arrays<\/h2>\n\n\n\n
SELECT c2[2:4] FROM array_test;<\/code><\/pre>\n\n\n\n c2
------------------
{Dog,Horse,Bear}
{Bird,Mouse,Cow}<\/pre>\n\n\n\nSELECT c2[2:] FROM array_test;<\/code><\/pre>\n\n\n\n c2
-----------------------
{Dog,Horse,Bear,Duck}
{Bird,Mouse,Cow,Pig}<\/pre>\n\n\n\nSELECT c2[:2] FROM array_test;<\/code><\/pre>\n\n\n\n c2
--------------
{Cat,Dog}
{Zebra,Bird}<\/pre>\n\n\n\nSELECT c3[1:2][2:3] FROM array_test;<\/code><\/pre>\n\n\n\n c3
-------------------
{{2,3},{5,6}}
{{78,14},{99,37}}<\/pre>\n\n\n\nc3<\/code>.<\/p>\n\n\n\nSELECT c3[1:2][3] FROM array_test;<\/code><\/pre>\n\n\n\n c3
--------------------------
{{1,2,3},{4,5,6}}
{{32,78,14},{102,99,37}}<\/pre>\n\n\n\n[3]<\/code> part meant that it started at the first element and ended at the third.<\/p>\n\n\n\nGet the Array’s Dimension<\/h2>\n\n\n\n
array_dims()<\/code> function<\/a> to return the dimension of a given array:<\/p>\n\n\n\nSELECT array_dims(c3) FROM array_test;<\/code><\/pre>\n\n\n\n array_dims
------------
[1:3][1:3]
[1:3][1:3]<\/pre>\n\n\n\nSELECT \n array_dims(c1) AS c1, \n array_dims(c2) AS c2, \n array_dims(c3) AS c3 \nFROM array_test;<\/code><\/pre>\n\n\n\n c1 | c2 | c3
-------+-------+------------
[1:3] | [1:5] | [1:3][1:3]
[1:3] | [1:5] | [1:3][1:3]<\/pre>\n\n\n\narray_upper()<\/a><\/code> and array_lower()<\/a><\/code> functions:<\/p>\n\n\n\nSELECT \n array_lower(c3, 2) AS lower, \n array_upper(c3, 2) AS upper\nFROM array_test;<\/code><\/pre>\n\n\n\n lower | upper
-------+-------
1 | 3
1 | 3<\/pre>\n\n\n\nc3<\/code> array. <\/p>\n\n\n\narray_upper()<\/code> and array_lower()<\/code> functions return an integer whereas array_dims()<\/code> returns text. <\/p>\n\n\n\nGet the Array’s Cardinality<\/h2>\n\n\n\n
cardinality()<\/code> function to return the total number of elements in an array across all dimensions:<\/p>\n\n\n\nSELECT cardinality(c3) FROM array_test;<\/code><\/pre>\n\n\n\n cardinality
-------------
9
9<\/pre>\n\n\n\nc3<\/code>.<\/p>\n\n\n\nSearch an Array<\/h2>\n\n\n\n
ANY()<\/code> construct:<\/p>\n\n\n\nSELECT * FROM array_test \nWHERE 'Horse' = ANY(c2);<\/code><\/pre>\n\n\n\n c1 | c2 | c3
---------+---------------------------+---------------------------
{1,2,3} | {Cat,Dog,Horse,Bear,Duck} | {{1,2,3},{4,5,6},{7,8,9}}<\/pre>\n\n\n\nSOME()<\/code>, which is a synonym for ANY()<\/code>. So the following produces the same result:<\/p>\n\n\n\nSELECT * FROM array_test \nWHERE 'Horse' = SOME(c2);<\/code><\/pre>\n\n\n\nALL()<\/code> construct:<\/p>\n\n\n\nSELECT * FROM array_test \nWHERE 40 < ALL(c1);<\/code><\/pre>\n\n\n\n c1 | c2 | c3
------------+----------------------------+-------------------------------------
{41,52,67} | {Zebra,Bird,Mouse,Cow,Pig} | {{32,78,14},{102,99,37},{18,65,29}}<\/pre>\n\n\n\nc1<\/code> are greater than 40 on the second row, and so ALL()<\/code> returns true<\/code> for that row, which results in the row being returned.<\/p>\n\n\n\n&&<\/code> operator:<\/p>\n\n\n\nSELECT * FROM array_test \nWHERE c2 && ARRAY['Mouse'];<\/code><\/pre>\n\n\n\n c1 | c2 | c3
------------+----------------------------+-------------------------------------
{41,52,67} | {Zebra,Bird,Mouse,Cow,Pig} | {{32,78,14},{102,99,37},{18,65,29}}<\/pre>\n\n\n\n&&<\/code> operator looks for an overlap between two arrays.<\/p>\n\n\n\nGet the Position of a Value in an Array<\/h2>\n\n\n\n
array_position()<\/code> and array_positions()<\/code> to get the position of a given value within an array. <\/p>\n\n\n\narray_position()<\/code> function returns the first occurrence of the value in the array, whereas array_positions()<\/code> returns all positions:<\/p>\n\n\n\nSELECT \n array_position(ARRAY[ 'Cat', 'Dog', 'Cat', 'Horse', 'Cat' ], 'Cat'),\n array_positions(ARRAY[ 'Cat', 'Dog', 'Cat', 'Horse', 'Cat' ], 'Cat');<\/code><\/pre>\n\n\n\n array_position | array_positions
----------------+-----------------
1 | {1,3,5}<\/pre>\n\n\n\nReplace an Array<\/h2>\n\n\n\n
UPDATE array_test \nSET c2 = '{\"a\", \"b\", \"c\", \"d\", \"e\"}'\nWHERE c1 = '{1,2,3}';\n\nSELECT * FROM array_test; <\/code><\/pre>\n\n\n\n c1 | c2 | c3
------------+----------------------------+-------------------------------------
{41,52,67} | {Zebra,Bird,Mouse,Cow,Pig} | {{32,78,14},{102,99,37},{18,65,29}}
{1,2,3} | {a,b,c,d,e} | {{1,2,3},{4,5,6},{7,8,9}}<\/pre>\n\n\n\nARRAY<\/code> constructor:<\/p>\n\n\n\nUPDATE array_test \nSET c2 = ARRAY['Rabbit', 'Rat', 'Cheetah', 'Lion', 'Leopard']\nWHERE c1 = '{1,2,3}';\n\nSELECT * FROM array_test; <\/code><\/pre>\n\n\n\n c1 | c2 | c3
------------+-----------------------------------+-------------------------------------
{41,52,67} | {Zebra,Bird,Mouse,Cow,Pig} | {{32,78,14},{102,99,37},{18,65,29}}
{1,2,3} | {Rabbit,Rat,Cheetah,Lion,Leopard} | {{1,2,3},{4,5,6},{7,8,9}}<\/pre>\n\n\n\nUpdate an Array Element<\/h2>\n\n\n\n
UPDATE array_test \nSET c2[2] = 'Squirrel'\nWHERE c1 = '{1,2,3}';\n\nSELECT * FROM array_test;<\/code><\/pre>\n\n\n\n c1 | c2 | c3
------------+----------------------------------------+-------------------------------------
{41,52,67} | {Zebra,Bird,Mouse,Cow,Pig} | {{32,78,14},{102,99,37},{18,65,29}}
{1,2,3} | {Rabbit,Squirrel,Cheetah,Lion,Leopard} | {{1,2,3},{4,5,6},{7,8,9}}<\/pre>\n\n\n\nUPDATE array_test \nSET c3[2][3] = 66\nWHERE c1 = '{1,2,3}';\n\nSELECT * FROM array_test;<\/code><\/pre>\n\n\n\n c1 | c2 | c3
------------+----------------------------------------+-------------------------------------
{41,52,67} | {Zebra,Bird,Mouse,Cow,Pig} | {{32,78,14},{102,99,37},{18,65,29}}
{1,2,3} | {Rabbit,Squirrel,Cheetah,Lion,Leopard} | {{1,2,3},{4,5,66},{7,8,9}}<\/pre>\n\n\n\nUpdate a Whole Slice<\/h2>\n\n\n\n
UPDATE array_test \nSET c2[2:4] = '{\"Cat\",\"Dog\",\"Cow\"}'\nWHERE c1 = '{1,2,3}';\n\nSELECT * FROM array_test;<\/code><\/pre>\n\n\n\n c1 | c2 | c3
------------+------------------------------+-------------------------------------
{41,52,67} | {Zebra,Bird,Mouse,Cow,Pig} | {{32,78,14},{102,99,37},{18,65,29}}
{1,2,3} | {Rabbit,Cat,Dog,Cow,Leopard} | {{1,2,3},{4,5,66},{7,8,9}}<\/pre>\n\n\n\nUPDATE array_test \nSET c2[4:] = '{\"Buffalo\",\"Ant\"}'\nWHERE c1 = '{1,2,3}';\n\nSELECT * FROM array_test;<\/code><\/pre>\n\n\n\n c1 | c2 | c3
------------+------------------------------+-------------------------------------
{41,52,67} | {Zebra,Bird,Mouse,Cow,Pig} | {{32,78,14},{102,99,37},{18,65,29}}
{1,2,3} | {Rabbit,Cat,Dog,Buffalo,Ant} | {{1,2,3},{4,5,66},{7,8,9}}<\/pre>\n\n\n\nUPDATE array_test \nSET c2[4:] = '{\"Monkey\",\"Moth\",\"Butterfly\"}'\nWHERE c1 = '{1,2,3}';\n\nSELECT * FROM array_test;<\/code><\/pre>\n\n\n\n c1 | c2 | c3
------------+------------------------------+-------------------------------------
{41,52,67} | {Zebra,Bird,Mouse,Cow,Pig} | {{32,78,14},{102,99,37},{18,65,29}}
{1,2,3} | {Rabbit,Cat,Dog,Monkey,Moth} | {{1,2,3},{4,5,66},{7,8,9}}<\/pre>\n\n\n\nMonkey<\/code> and Moth<\/code>) made their way on to the array, but the third one (Butterfly<\/code>) didn’t.<\/p>\n\n\n\nEnlarge an Array<\/h2>\n\n\n\n
UPDATE array_test \nSET c2[7] = 'Swan'\nWHERE c1 = '{41,52,67}';\n\nSELECT * FROM array_test;<\/code><\/pre>\n\n\n\n c1 | c2 | c3
------------+--------------------------------------+-------------------------------------
{1,2,3} | {Rabbit,Cat,Dog,Monkey,Moth} | {{1,2,3},{4,5,66},{7,8,9}}
{41,52,67} | {Zebra,Bird,Mouse,Cow,Pig,NULL,Swan} | {{32,78,14},{102,99,37},{18,65,29}}<\/pre>\n\n\n\nNULL<\/code>. We can see this in our example. I specified that Swan<\/code> should be inserted into the 7th position, but there were previously only 5 positions. Therefore, the 6th position was filled with NULL<\/code> in order to get Swan<\/code> to the 7th position.<\/p>\n\n\n\nSpecify the Lower Bound of an Array<\/h2>\n\n\n\n
SELECT ('[0:2]={ \"Cat\", \"Dog\", \"Horse\" }'::text[])[0];<\/code><\/pre>\n\n\n\n text
------
Cat<\/pre>\n\n\n\n0<\/code> instead of 1<\/code>. That’s exactly what we did here. We used [0]<\/code> to return the first item in the array, which was Cat<\/code>.<\/p>\n\n\n\nINSERT INTO array_test VALUES \n (\n '[0:2]={ 8,7,9 }',\n '[-5:-1]={ \"Gecko\", \"Agra\", \"Badger\", \"Beaver\", \"Platypus\" }',\n '[1:3][10:12]={ {45,78,13}, {14,53,31}, {98,26,27} }'\n );\n\nSELECT * FROM array_test;<\/code><\/pre>\n\n\n\n c1 | c2 | c3
---------------+---------------------------------------------+-------------------------------------------------
{1,2,3} | {Rabbit,Cat,Dog,Monkey,Moth} | {{1,2,3},{4,5,66},{7,8,9}}
{41,52,67} | {Zebra,Bird,Mouse,Cow,Pig,NULL,Swan} | {{32,78,14},{102,99,37},{18,65,29}}
[0:2]={8,7,9} | [-5:-1]={Gecko,Agra,Badger,Beaver,Platypus} | [1:3][10:12]={{45,78,13},{14,53,31},{98,26,27}}<\/pre>\n\n\n\nSELECT * FROM array_test\nWHERE c2[-3] = 'Badger';<\/code><\/pre>\n\n\n\n c1 | c2 | c3
---------------+---------------------------------------------+-------------------------------------------------
[0:2]={8,7,9} | [-5:-1]={Gecko,Agra,Badger,Beaver,Platypus} | [1:3][10:12]={{45,78,13},{14,53,31},{98,26,27}}<\/pre>\n\n\n\n-3<\/code>, and that’s what I specified in my query. If this was a normal one-based array, then we would have needed to specify 3<\/code> instead of -3<\/code>.<\/p>\n\n\n\nPush a Value to an Array<\/h2>\n\n\n\n
SELECT ARRAY[1,2,3] || ARRAY[4,5,6];<\/code><\/pre>\n\n\n\n{1,2,3,4,5,6}<\/pre>\n\n\n\n\\x\nSELECT \n c2 AS Original,\n c2 || ARRAY['Horse'] AS Appended,\n ARRAY['Horse'] || c2 AS Prepended\nFROM array_test\nWHERE c1 = '{ 1,2,3 }';<\/code><\/pre>\n\n\n\noriginal | {Rabbit,Cat,Dog,Monkey,Moth}
appended | {Rabbit,Cat,Dog,Monkey,Moth,Horse}
prepended | {Horse,Rabbit,Cat,Dog,Monkey,Moth}<\/pre>\n\n\n\npsql<\/code>‘s \\x<\/code> meta command to change the results to expanded display to make the results easier to read without scrolling.<\/p>\n\n\n\narray_prepend()<\/a><\/code>, array_append()<\/a><\/code>, and array_cat()<\/a><\/code> functions to prepend and append values to arrays:<\/p>\n\n\n\nSELECT \n c2 AS Original,\n array_append(c2, 'Horse') AS Appended,\n array_prepend('Horse', c2) AS Prepended,\n array_cat(c2, ARRAY['Horse']) AS \"Appended with array_cat\",\n array_cat(ARRAY['Horse'], c2) AS \"Prepended with array_cat\"\nFROM array_test\nWHERE c1 = '{ 1,2,3 }';<\/code><\/pre>\n\n\n\noriginal | {Rabbit,Cat,Dog,Monkey,Moth}
appended | {Rabbit,Cat,Dog,Monkey,Moth,Horse}
prepended | {Horse,Rabbit,Cat,Dog,Monkey,Moth}
Appended with array_cat | {Rabbit,Cat,Dog,Monkey,Moth,Horse}
Prepended with array_cat | {Horse,Rabbit,Cat,Dog,Monkey,Moth}<\/pre>\n\n\n\narray_cat()<\/code> function can be used with multi dimensional arrays, but the other two can’t.<\/p>\n\n\n\nSELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);<\/code><\/pre>\n\n\n\n{{1,2},{3,4},{5,6}}<\/pre>\n\n\n\nSELECT \n c3,\n array_cat( c3, ARRAY[10,11,12] )\nFROM array_test\nWHERE c1 = '{1,2,3}';<\/code><\/pre>\n\n\n\n c3 | array_cat
----------------------------+---------------------------------------
{{1,2,3},{4,5,66},{7,8,9}} | {{1,2,3},{4,5,66},{7,8,9},{10,11,12}}<\/pre>\n\n\n\n