How to Create an Array Column in PostgreSQL: Syntax and Examples

In PostgreSQL, you create an array column by specifying the data type followed by square brackets, like integer[] or text[]. Use CREATE TABLE or ALTER TABLE statements with this syntax to define columns that store arrays.

Syntax

To create an array column, specify the base data type followed by square brackets []. For example, integer[] means an array of integers, and text[] means an array of text strings.

You can define the array column when creating a table or add it later with ALTER TABLE.

sql

CREATE TABLE table_name (
  column_name data_type[]
);

-- Example:
CREATE TABLE users (
  id serial PRIMARY KEY,
  tags text[]
);

Example

This example creates a table with an array column and inserts data into it. Then it selects the data to show the stored arrays.

sql

CREATE TABLE products (
  product_id serial PRIMARY KEY,
  name text NOT NULL,
  prices numeric[]
);

INSERT INTO products (name, prices) VALUES
('Widget', ARRAY[19.99, 24.99, 29.99]),
('Gadget', ARRAY[9.99, 14.99]);

SELECT * FROM products;

Output

 product_id |  name  |      prices       
------------+--------+-------------------
          1 | Widget | {19.99,24.99,29.99}
          2 | Gadget | {9.99,14.99}
(2 rows)

Common Pitfalls

• Forgetting the square brackets [] after the data type will create a normal column, not an array.
• Using incorrect syntax like integer array instead of integer[] causes errors.
• When inserting data, use the ARRAY[] constructor or curly braces {} to specify array values.

sql

/* Wrong: missing brackets */
CREATE TABLE example_wrong (
  numbers integer
);

/* Right: with brackets */
CREATE TABLE example_right (
  numbers integer[]
);

Quick Reference

Action	Syntax Example
Create array column	column_name data_type[]
Insert array data	INSERT INTO table (col) VALUES (ARRAY[1,2,3])
Select array data	SELECT col FROM table
Add array column	ALTER TABLE table ADD COLUMN col data_type[]

Key Takeaways

Use square brackets [] after the data type to define an array column in PostgreSQL.

Insert array values using the ARRAY[] constructor or curly braces {} syntax.

Always specify the correct data type before the brackets to avoid errors.

You can create array columns during table creation or add them later with ALTER TABLE.

Remember that arrays can store multiple values of the same data type in one column.