This example takes data from a source table which records a phone number associated with an employee and inserts it into a new table which records phone numbers in an ARRAY type. In many cases, an employee may have multiple phone numbers, such as office phone, mobile phone, home phone, etc. A better way to represent this type of data is with a 1-D ARRAY type which records multiple phone numbers. This reduces the number of rows in the table.
Note that the same type of behavior can be illustrated with an n-D ARRAY type. The only difference is that the storage of elements is done in row-major order.
The following statement creates a 1-D ARRAY type that can hold up to 100 phone number values:
CREATE TYPE emp_phone_array AS VARCHAR(14) ARRAY[100];
The following source table contains one row per employee phone number.
CREATE SET TABLE employee (emp_id INTEGER, emp_name VARCHAR(30), emp_phone CHAR(14)); SELECT * FROM employee;
emp_id emp_name emp_phone
-------- ----------- -------------
1 Beth (619) 619-6190
1 Beth (619) 620-6200
1 Beth (619) 720-7200
2 Greg (858) 858-8580
2 Greg (858) 859-8590
2 Greg (858) 860-8600
3 Louise (421) 421-4210
3 Louise (421) 422-4220
3 Louise (421) 423-4230
The following target table contains one row per employee and stores all phone numbers associated with an employee in an ARRAY type.
CREATE SET TABLE employeePhoneInfo (emp_id INTEGER, emp_name VARCHAR(30), emp_phone emp_phone_array); INSERT INTO employeePhoneInfo SELECT emp_id, emp_name, ARRAY_AGG(emp_phone, NEW emp_phone_array()) FROM employee GROUP BY emp_id,emp_name WHERE emp_id < 100; SELECT * FROM employeePhoneInfo;
The result is:
emp_id emp_name emp_phone
-------- ----------- -------------
1 Beth ( (619) 619-6190, (619) 620-6200, (619) 720-7200 )
2 Greg ( (858) 858-8580, (858) 859-8590, (858) 860-8600 )
3 Louise ( (421) 421-4210, (421) 422-4220, (421) 423-4230 )