How to Insert Array Value in PostgreSQL: Syntax and Examples

To insert an array value in PostgreSQL, use the ARRAY keyword or curly braces {} to define the array in the INSERT statement. For example, INSERT INTO table_name (array_column) VALUES (ARRAY[1,2,3]); inserts an integer array into the specified column.

Syntax

In PostgreSQL, you insert array values using the INSERT INTO statement with either the ARRAY constructor or curly braces {}. The array elements must be enclosed in ARRAY[...] or '{...}' and match the column's array type.

• ARRAY[...]: Preferred way to define arrays in SQL statements.
• '{...}': String representation of an array, must be quoted.
• Column: Must be declared as an array type, e.g., integer[], text[].

sql

INSERT INTO table_name (array_column) VALUES (ARRAY[1, 2, 3]);

-- or using string syntax
INSERT INTO table_name (array_column) VALUES ('{1,2,3}');

Example

This example creates a table with an integer array column and inserts a row with an array value using the ARRAY syntax. It then selects the data to show the stored array.

sql

CREATE TABLE fruits (
  id SERIAL PRIMARY KEY,
  names TEXT[]
);

INSERT INTO fruits (names) VALUES (ARRAY['apple', 'banana', 'cherry']);

SELECT * FROM fruits;

Output

 id |        names        
----+---------------------
  1 | {apple,banana,cherry}

Common Pitfalls

Common mistakes when inserting arrays include:

• Not declaring the column as an array type (e.g., text[]).
• Using incorrect syntax without ARRAY or missing quotes around string arrays.
• Mixing data types inside the array.
• Forgetting to match the array element type with the column type.

Example of wrong and right ways:

sql

-- Wrong: missing ARRAY keyword and quotes for strings
INSERT INTO fruits (names) VALUES ('apple,banana,cherry');

-- Right: use ARRAY with string literals
INSERT INTO fruits (names) VALUES (ARRAY['apple', 'banana', 'cherry']);

Quick Reference

Concept	Example
Declare array column	names TEXT[]
Insert integer array	INSERT INTO table (col) VALUES (ARRAY[1,2,3]);
Insert text array	INSERT INTO table (col) VALUES (ARRAY['a','b','c']);
Insert using string syntax	INSERT INTO table (col) VALUES ('{1,2,3}');
Select array data	SELECT * FROM table;

Key Takeaways

Use the ARRAY keyword or curly braces '{}' to insert array values in PostgreSQL.

Ensure the column is declared with an array type like integer[] or text[].

Match the array element types with the column's declared type.

String arrays require single quotes around elements inside ARRAY or the string syntax.

Avoid inserting arrays without proper syntax to prevent errors.