PopSQL

How to Insert Data Into an Array in PostgreSQL

There are two accepted syntaxes for inserting data to an array column. The first uses ARRAY [value1, value2, etc]:

insert into contacts (first_name, last_name, phone_numbers)
values ('John', 'Doe', ARRAY ['999-876-5432','999-123-4567']);

insert into player_scores (player_number, round_scores)
values (10001, ARRAY [95, 92, 96, 97, 98] );

-- multi-dimension arrays must have same array lengths for the inner dimensions
insert into student_scores (student_number, test_scores)
values (20001, ARRAY [[1, 95], [2, 94], [3, 98]]);

The second one uses single quotes and curly braces.

insert into contacts (first_name, last_name, phone_numbers)
values ('Bob', 'Parr', '{"555-INC-RDBL"}');

insert into player_scores (player_number, round_scores)
values (10002, '{91, 92, 93, 95, 99}' );

insert into student_scores (student_number, test_scores)
values (20002, '{{1, 96}, {2, 93}, {4, 97}}');

Note above that Bob Parr's phone number is now enclosed in double quotations, since we are using the single quotes to wrap the array. Also note that even if there is only one phone number, it still has to be inserted in array form.

database icon
SQL editing that just rocks
PopSQL and PostgreSQL, better together