{"id":2054,"date":"2025-02-12T10:31:50","date_gmt":"2025-02-12T03:31:50","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=2054"},"modified":"2025-03-07T11:45:40","modified_gmt":"2025-03-07T04:45:40","slug":"postgresql-array","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-array\/","title":{"rendered":"PostgreSQL ARRAY Data Type"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the PostgreSQL Array type to store arrays in the database table.<\/p>\n\n\n\n

PostgreSQL ARRAY datatype overview #<\/a><\/h2>\n\n\n\n

In PostgreSQL, you can use the Array type to store multiple values of the same type in a single column.<\/p>\n\n\n\n

For every data type, including user-defined type<\/a>, PostgreSQL creates a corresponding Array data type implicitly.<\/p>\n\n\n\n

For example, the INT<\/code> type has the INT[]<\/code> array type, VARCHAR<\/code> has the VARCHAR[]<\/code> array type, etc.<\/p>\n\n\n\n

Here’s the syntax for defining a column with an array data type:<\/p>\n\n\n

column_name<\/span> datatype[]<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you use square brackets [] after the data type to define an array of a particular data type<\/p>\n\n\n\n
CREATE<\/span> TABLE<\/span> products (\n  id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n  price DECIMAL<\/span>(10<\/span>, 2<\/span>) NOT<\/span> NULL<\/span>,\n  features TEXT<\/span>[]\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In the products<\/code> table, the features<\/code> column has a data type of a one-dimensional array that can store multiple text values.<\/p>\n\n\n\n
An array can be one-dimensional or multi-dimensional. The number of bracket squares determines the number of dimensions of an array.<\/p>\n\n\n\n
For example, the following shows how to define a column with two dimensions:<\/p>\n\n\n
column_name<\/span> datatype [][]<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Inserting Data into the Array Column #<\/a><\/h2>\n\n\n\n
To construct a value of an array, you can use either curly braces {}<\/code> or the ARRAY<\/code> constructor.<\/p>\n\n\n\n
The following statement uses an ARRAY<\/code> constructor inserts a new row into the products<\/code> table :<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  products (name<\/span>, price, features)\nVALUES<\/span>\n  (\n    'Smartphone X1'<\/span>,\n    699.99<\/span>,\n    ARRAY<\/span>['5G'<\/span>, 'OLED Display'<\/span>, '512GB Storage'<\/span>]\n  )\nRETURNING<\/span>\n  name<\/span>,\n  price,\n  features;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
     name<\/span>      | price  |              features\n---------------+--------+-------------------------------------<\/span>\n Smartphone X1 | 699.99<\/span> | {5<\/span>G,\"OLED Display\",\"512GB Storage\"}<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following INSERT<\/code> statement uses curly braces to insert a new product into the products table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  products (name<\/span>, price, features)\nVALUES<\/span>\n  (\n    'Laptop Pro 15'<\/span>,\n    1299.99<\/span>,\n    '{\"Touchscreen\", \"32GB RAM\", \"1024GB SSD\"}'<\/span>\n  )\nRETURNING<\/span>\n  name<\/span>,\n  price,\n  features;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
     name<\/span>      |  price  |               features\n---------------+---------+---------------------------------------<\/span>\n Laptop Pro 15<\/span> | 1299.99<\/span> | {Touchscreen,\"32GB RAM\",\"1024GB SSD\"}<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Querying array data #<\/a><\/h2>\n\n\n\n
PostgreSQL arrays use 1-based indexing i.e., the first arary element is 1, the second array element is 2, etc.<\/p>\n\n\n\n
The following query<\/a> retrieves the product name and the first feature from the products<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  features[1<\/span>] AS<\/span> main_feature\nFROM<\/span>\n  products;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
     name<\/span>      | main_feature\n---------------+--------------<\/span>\n Smartphone X1 | 5<\/span>G\n Laptop Pro 15<\/span> | Touchscreen<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the query retrieves the first feature of each product in the products<\/code> table. <\/p>\n\n\n\n
The following query retrieves the full array of features:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  features\nFROM<\/span>\n  products;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
     name<\/span>      |               features\n---------------+---------------------------------------<\/span>\n Smartphone X1 | {5<\/span>G,\"OLED Display\",\"512GB Storage\"}\n Laptop Pro 15<\/span> | {Touchscreen,\"32GB RAM\",\"1024GB SSD\"}<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Modifying array elements #<\/a><\/h2>\n\n\n\n
PostgreSQL allows you to update a specific element in an array or replace the entire array.<\/p>\n\n\n\n
For example, the following UPDATE<\/a><\/code> statement modifies the second element in an array:<\/p>\n\n\n
UPDATE<\/span> products\nSET<\/span>\n  features[2<\/span>] = 'Super Retina Display'<\/span>\nWHERE<\/span>\n  id = 1<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
And the following statement replaces the entire array:<\/p>\n\n\n
UPDATE<\/span> products\nSET<\/span>\n  features = ARRAY<\/span>[\n    'OLED Display'<\/span>,\n    '256GB Storage'<\/span>,\n    'Water Resistant'<\/span>\n  ]\nWHERE<\/span>\n  id = 2<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Searching within an array #<\/a><\/h2>\n\n\n\n
You use the ANY<\/a><\/code> operator to check if an element exists in an array. For example, the following statement retrieves the products that have 5G as one of their features:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>, features\nFROM<\/span>\n  products\nWHERE<\/span>\n  '5G'<\/span> = ANY<\/span> (features);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
     name<\/span>      |                  features\n---------------+---------------------------------------------<\/span>\n Smartphone X1 | {5<\/span>G,\"Super Retina Display\",\"512GB Storage\"}<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Expanding arrays into rows #<\/a><\/h2>\n\n\n\n
To expand an arrays into a set of rows, you use the UNNEST<\/code> function:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  UNNEST(features) AS<\/span> feature\nFROM<\/span>\n  products;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
     name<\/span>      |       feature\n---------------+----------------------<\/span>\n Smartphone X1 | 5<\/span>G\n Smartphone X1 | Super Retina Display\n Smartphone X1 | 512<\/span>GB Storage<\/span>\n Laptop Pro 15<\/span> | OLED Display\n Laptop Pro 15<\/span> | 256<\/span>GB Storage<\/span>\n Laptop Pro 15<\/span> | Water Resistant<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
You use the UNNEST<\/code> function to treat array elements as individual rows.<\/p>\n\n\n\n
Counting array elements #<\/a><\/h2>\n\n\n\n
The ARRAY_LENGTH<\/code> function returns the number of elements in an array:<\/p>\n\n\n
ARRAY_LENGTH(array<\/span>, dimension)<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following query uses the ARRAY_LENGTH<\/code> function to return the number of features for each product:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  array_length(features, 1<\/span>) AS<\/span> feature_count\nFROM<\/span>\n  products;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The argument 1 indicates one-dimensional array.<\/p>\n\n\n\n
Output:<\/p>\n\n\n
     name<\/span>      | feature_count\n---------------+---------------<\/span>\n Smartphone X1 |             3<\/span>\n Laptop Pro 15<\/span> |             3<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Adding a new element to an array #<\/a><\/h2>\n\n\n\n
The ARRAY_APPEND<\/code> appends an element to the end of an array.<\/p>\n\n\n\n
For example, the following statement uses the ARRAY_APPEND<\/code> function to add a feature to the product id 1:<\/p>\n\n\n
UPDATE<\/span> products\nSET<\/span>\n  features = ARRAY_APPEND(features, 'Fingerprint'<\/span>)\nWHERE<\/span>\n  id = 1<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following query verifies the update:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  features\nFROM<\/span>\n  products\nWHERE<\/span>\n  id = 1<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
     name<\/span>      |                        features\n---------------+---------------------------------------------------------<\/span>\n Smartphone X1 | {5<\/span>G,\"Super Retina Display\",\"512GB Storage\",Fingerprint}<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Removing an element from an array #<\/a><\/h2>\n\n\n\n
To delete all occurrences of an element from an array, you use the ARRAY_REMOVE<\/code> function:<\/p>\n\n\n
ARRAY_REMOVE(array<\/span>, element)<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement removes the Touchscreen feature from the product id 2:<\/p>\n\n\n
UPDATE<\/span> products\nSET<\/span>\n  features = ARRAY_REMOVE(features, 'Touchscreen'<\/span>)\nWHERE<\/span>\n  id = 2<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following query checks the update:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  features\nFROM<\/span>\n  products\nWHERE<\/span>\n  id = 2<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
     name<\/span>      |                      features\n---------------+----------------------------------------------------<\/span>\n Laptop Pro 15<\/span> | {\"OLED Display\",\"256GB Storage\",\"Water Resistant\"}<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Indexing array columns #<\/a><\/h2>\n\n\n\n
If performance is critical, consider using GIN indexes to speed up searches in arrays.<\/p>\n\n\n\n
For example, the following statement creates an index for the data in the features column:<\/p>\n\n\n
CREATE<\/span> INDEX<\/span> idx_features ON<\/span> products USING<\/span> GIN (features);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Typically, you use arrays for small lists like tags, attributes, etc.<\/p>\n\n\n\n
If you need to query data frequently, consider storing array data in a separate table and creating a relationship with the main table.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
For example, the following statement creates a table<\/a> called products<\/code> to store product information:<\/p>\n\n\n