1 <!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.19 2002/01/20 22:19:55 petere Exp $ -->
7 <primary>arrays</primary>
11 <productname>PostgreSQL</productname> allows columns of a table to be
12 defined as variable-length multidimensional arrays. Arrays of any
13 built-in type or user-defined type can be created. To illustrate
14 their use, we create this table:
16 CREATE TABLE sal_emp (
18 pay_by_quarter integer[],
22 As shown, an array data type is named by appending square brackets
23 (<literal>[]</>) to the data type name of the array elements.
24 The above query will create a table named
25 <structname>sal_emp</structname> with a <type>text</type> string
26 (<structfield>name</structfield>), a one-dimensional array of type
27 <type>integer</type> (<structfield>pay_by_quarter</structfield>),
28 which represents the employee's salary by quarter, and a
29 two-dimensional array of <type>text</type>
30 (<structfield>schedule</structfield>), which represents the
31 employee's weekly schedule.
35 Now we do some <command>INSERT</command>s. Observe that to write an array
36 value, we enclose the element values within curly braces and separate them
37 by commas. If you know C, this is not unlike the syntax for
38 initializing structures.
43 '{10000, 10000, 10000, 10000}',
44 '{{"meeting", "lunch"}, {}}');
48 '{20000, 25000, 25000, 25000}',
49 '{{"talk", "consult"}, {"meeting"}}');
54 Now, we can run some queries on <structname>sal_emp</structname>.
55 First, we show how to access a single element of an array at a time.
56 This query retrieves the names of the employees whose pay changed in
60 SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
68 The array subscript numbers are written within square brackets.
69 <productname>PostgreSQL</productname> uses the
70 <quote>one-based</quote> numbering convention for arrays, that is,
71 an array of <replaceable>n</> elements starts with <literal>array[1]</literal> and
72 ends with <literal>array[<replaceable>n</>]</literal>.
76 This query retrieves the third quarter pay of all employees:
79 SELECT pay_by_quarter[3] FROM sal_emp;
90 We can also access arbitrary rectangular slices of an array, or
91 subarrays. An array slice is denoted by writing
92 <literal><replaceable>lower subscript</replaceable> :
93 <replaceable>upper subscript</replaceable></literal> for one or more
94 array dimensions. This query retrieves the first item on Bill's
95 schedule for the first two days of the week:
98 SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
106 We could also have written
109 SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';
112 with the same result. An array subscripting operation is taken to
113 represent an array slice if any of the subscripts are written in the
114 form <replaceable>lower</replaceable> <literal>:</literal>
115 <replaceable>upper</replaceable>. A lower bound of 1 is assumed for
116 any subscript where only one value is specified.
120 An array value can be replaced completely:
123 UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
124 WHERE name = 'Carol';
127 or updated at a single element:
130 UPDATE sal_emp SET pay_by_quarter[4] = 15000
134 or updated in a slice:
137 UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
138 WHERE name = 'Carol';
143 An array can be enlarged by assigning to an element adjacent to
144 those already present, or by assigning to a slice that is adjacent
145 to or overlaps the data already present. For example, if an array
146 value currently has 4 elements, it will have five elements after an
147 update that assigns to array[5]. Currently, enlargement in this
148 fashion is only allowed for one-dimensional arrays, not
149 multidimensional arrays.
153 The syntax for <command>CREATE TABLE</command> allows fixed-length
154 arrays to be defined:
157 CREATE TABLE tictactoe (
158 squares integer[3][3]
162 However, the current implementation does not enforce the array size
163 limits --- the behavior is the same as for arrays of unspecified
168 Actually, the current implementation does not enforce the declared
169 number of dimensions either. Arrays of a particular element type are
170 all considered to be of the same type, regardless of size or number
175 The current dimensions of any array value can be retrieved with the
176 <function>array_dims</function> function:
179 SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
187 <function>array_dims</function> produces a <type>text</type> result,
188 which is convenient for people to read but perhaps not so convenient
193 To search for a value in an array, you must check each value of the
194 array. This can be done by hand (if you know the size of the array):
197 SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
198 pay_by_quarter[2] = 10000 OR
199 pay_by_quarter[3] = 10000 OR
200 pay_by_quarter[4] = 10000;
203 However, this quickly becomes tedious for large arrays, and is not
204 helpful if the size of the array is unknown. Although it is not part
205 of the primary <productname>PostgreSQL</productname> distribution,
206 there is an extension available that defines new functions and
207 operators for iterating over array values. Using this, the above
211 SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 10000;
214 To search the entire array (not just specified columns), you could
218 SELECT * FROM sal_emp WHERE pay_by_quarter *= 10000;
221 In addition, you could find rows where the array had all values
222 equal to 10 000 with:
225 SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000;
228 To install this optional module, look in the
229 <filename>contrib/array</filename> directory of the
230 <productname>PostgreSQL</productname> source distribution.
235 Arrays are not sets; using arrays in the manner described in the
236 previous paragraph is often a sign of database misdesign. The
237 array field should generally be split off into a separate table.
238 Tables can obviously be searched easily.
244 A limitation of the present array implementation is that individual
245 elements of an array cannot be SQL NULLs. The entire array can be set
246 to NULL, but you can't have an array with some elements NULL and some
247 not. Fixing this is on the to-do list.
252 <title>Quoting array elements.</title>
254 As shown above, when writing an array literal value you may write double
255 quotes around any individual array
256 element. You <emphasis>must</> do so if the element value would otherwise
257 confuse the array-value parser. For example, elements containing curly
258 braces, commas, double quotes, backslashes, or white space must be
259 double-quoted. To put a double quote or backslash in an array element
260 value, precede it with a backslash.
266 Remember that what you write in an SQL query will first be interpreted
267 as a string literal, and then as an array. This doubles the number of
268 backslashes you need. For example, to insert a <type>text</> array
269 value containing a backslash and a double quote, you'd need to write
271 INSERT ... VALUES ('{"\\\\","\\""}');
273 The string-literal processor removes one level of backslashes, so that
274 what arrives at the array-value parser looks like <literal>{"\\","\""}</>.
275 In turn, the strings fed to the <type>text</> data type's input routine
276 become <literal>\</> and <literal>"</> respectively. (If we were working
277 with a data type whose input routine also treated backslashes specially,
278 <type>bytea</> for example, we might need as many as eight backslashes
279 in the query to get one backslash into the stored array element.)