ARRAY DATA TYPEData Types
Buy Me A Coffee

A column type that stores an ordered list of values of a uniform type within a single cell. Eliminates the need for a child table for simple multi-value attributes, at the cost of normalization.

Columns that hold arrays of a single base type, available in PostgreSQL natively. Useful for denormalized data (tags, categories) without a separate junction table. Avoid for normalized relationships -- use proper relational tables instead.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✗ Not Supported — MySQL has no native array data type. Common alternatives: (1) a normalized child table (preferred); (2) a JSON column storing a JSON array; (3) a comma-separated string (discouraged — hard to query). The JSON approach gives the most array-like functionality with json_contains(), json_extract(), and json_array_append().
PostgreSQL ✓ Supported 7.1 First-class array type for every base type: integer[], text[], boolean[], numeric[], timestamptz[], etc. Arrays are zero-indexed in the C tradition internally but one-indexed in SQL access (arr[1] is the first element). Multidimensional arrays supported. GIN indexes enable fast containment queries (@>, &&). unnest() expands an array to rows. array_agg() is the inverse — collapses rows into an array.
SQL Server ✗ Not Supported — SQL Server has no native array type. Alternatives: (1) a normalized child table; (2) a JSON column (SQL Server 2016+); (3) a delimited string with STRING_SPLIT; (4) an XML column with nodes() querying.
Oracle ✓ Supported 8i Oracle supports arrays through VARRAY (variable-size array with a fixed maximum) and nested tables (unbounded, stored out-of-line). Both require defining a named type. The SQL/JSON path operators (12c+) on JSON columns are commonly preferred over VARRAY for flexible multi-value storage in modern Oracle. VARRAYs are stored inline in the row; nested tables require a separate storage clause.
SQLite ✗ Not Supported — SQLite has no native array type. SQLite's flexible type system (type affinity) means you can store a JSON array in any TEXT column. The json_each() function provides array iteration. For simple cases, a comma-separated string with json_each on a JSON conversion is the lightest workaround.

Details

PostgreSQL is the only engine with a true native array type supporting arbitrary element types, multidimensional arrays, GIN indexes for fast containment queries, and a rich function library (array_agg, unnest, array_append, @>, &&). Oracle has arrays via VARRAY and nested tables but they require DDL type definitions and have storage complexities that make JSON columns a more common modern choice. MySQL, SQL Server, and SQLite all lack native arrays — the JSON column workaround is the most ergonomic alternative in all three, especially since JSON array operators (JSON_CONTAINS in MySQL, OPENJSON in SQL Server, json_each in SQLite) are reasonably capable.

Standard Syntax

-- PostgreSQL: CREATE TABLE products ( id SERIAL PRIMARY KEY, tags TEXT[], scores INTEGER[] ); INSERT INTO products (tags, scores) VALUES (ARRAY['featured','sale'], ARRAY[4,5,3]);

Version Support

MySQL: Not supported PostgreSQL: Since 7.1 SQL Server: Not supported Oracle: Since 8i SQLite: Not supported

Per-Database Syntax & Notes

MySQL

MySQL has no native array data type. Common alternatives: (1) a normalized child table (preferred); (2) a JSON column storing a JSON array; (3) a comma-separated string (discouraged — hard to query). The JSON approach gives the most array-like functionality with json_contains(), json_extract(), and json_array_append().

-- JSON array alternative: CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, tags JSON ); INSERT INTO products (tags) VALUES (JSON_ARRAY('featured','sale')); SELECT * FROM products WHERE JSON_CONTAINS(tags, JSON_QUOTE('featured')); -- Append to array: UPDATE products SET tags = JSON_ARRAY_APPEND(tags, '$', 'new-tag') WHERE id = 1;

PostgreSQL

First-class array type for every base type: integer[], text[], boolean[], numeric[], timestamptz[], etc. Arrays are zero-indexed in the C tradition internally but one-indexed in SQL access (arr[1] is the first element). Multidimensional arrays supported. GIN indexes enable fast containment queries (@>, &&). unnest() expands an array to rows. array_agg() is the inverse — collapses rows into an array.

CREATE TABLE products ( id SERIAL PRIMARY KEY, tags TEXT[], scores INTEGER[] ); INSERT INTO products (tags, scores) VALUES (ARRAY['featured','sale'], ARRAY[4,5,3]); -- Index access (1-based): SELECT tags[1] AS first_tag FROM products; -- Containment: rows where tags includes 'featured': SELECT * FROM products WHERE tags @> ARRAY['featured']; -- Overlap: any tag in common: SELECT * FROM products WHERE tags && ARRAY['sale','new']; -- Expand to rows: SELECT id, unnest(tags) AS tag FROM products; -- GIN index for fast containment: CREATE INDEX idx_tags ON products USING GIN (tags); -- Array functions: SELECT array_length(tags, 1), array_append(tags, 'clearance'), array_remove(tags, 'sale') FROM products;

SQL Server

SQL Server has no native array type. Alternatives: (1) a normalized child table; (2) a JSON column (SQL Server 2016+); (3) a delimited string with STRING_SPLIT; (4) an XML column with nodes() querying.

-- JSON array alternative (2016+): ALTER TABLE products ADD tags NVARCHAR(MAX); UPDATE products SET tags = JSON_MODIFY(tags, 'append $', 'featured') WHERE id = 1; SELECT p.id, s.value AS tag FROM products p CROSS APPLY STRING_SPLIT( (SELECT STRING_AGG(value,',') FROM OPENJSON(p.tags)), ',' ) s; -- XML alternative: SELECT t.c.value('.','NVARCHAR(50)') AS tag FROM products p CROSS APPLY ( SELECT CAST('<t>' + REPLACE(p.tags_csv,',','</t><t>') + '</t>' AS XML) ) x(xml_val) CROSS APPLY xml_val.nodes('/t') t(c);

Oracle

Oracle supports arrays through VARRAY (variable-size array with a fixed maximum) and nested tables (unbounded, stored out-of-line). Both require defining a named type. The SQL/JSON path operators (12c+) on JSON columns are commonly preferred over VARRAY for flexible multi-value storage in modern Oracle. VARRAYs are stored inline in the row; nested tables require a separate storage clause.

-- Define a VARRAY type: CREATE TYPE tag_array AS VARRAY(20) OF VARCHAR2(50); -- Use it in a table: CREATE TABLE products ( id NUMBER PRIMARY KEY, tags tag_array ); INSERT INTO products VALUES (1, tag_array('featured','sale')); -- Query: unnest via TABLE(): SELECT p.id, t.COLUMN_VALUE AS tag FROM products p, TABLE(p.tags) t WHERE t.COLUMN_VALUE = 'featured'; -- Nested table (unbounded, out-of-line storage): CREATE TYPE tag_table AS TABLE OF VARCHAR2(50);

SQLite

SQLite has no native array type. SQLite's flexible type system (type affinity) means you can store a JSON array in any TEXT column. The json_each() function provides array iteration. For simple cases, a comma-separated string with json_each on a JSON conversion is the lightest workaround.

-- JSON array in a TEXT column: CREATE TABLE products ( id INTEGER PRIMARY KEY, tags TEXT -- store as JSON array string ); INSERT INTO products (tags) VALUES ('["featured","sale"]'); -- Query elements: SELECT p.id, j.value AS tag FROM products p, json_each(p.tags) j WHERE j.value = 'featured'; -- Append to array: UPDATE products SET tags = json_insert(tags, '$[#]', 'clearance') WHERE id = 1;