PostgreSQL - Array Data Type

Last Updated : 11 Nov, 2024

PostgreSQL provides an advanced and flexible feature known as the Array Data Type, which allows us to store multiple values in a single column. Arrays in PostgreSQL can be used with all built-in data types and even user-defined types, enabling a wide range of use cases.

In this article, we will explain the PostgreSQL Array Data Type, its syntax, practical examples, and how we can use arrays to optimize our database operations. By the end, we'll have a clear understanding of how to use arrays in PostgreSQL effectively.

PostgreSQL Array Data Type

Arrays in PostgreSQL allow us to store collections of elements in a single column. This can be particularly useful when we need to associate multiple values with a single record. For instance, we might store multiple phone numbers for a single contact in a contacts table.

The PostgreSQL Array type allows us to create fields that store multiple values, significantly reducing the need for creating additional tables or restructuring our data model

Key Features of PostgreSQL Arrays:

Arrays can store any data type: From integers and strings to custom data types.
Flexible and compact: Store multiple values within a single record, improving query performance and data structure.
Standard SQL compliance: PostgreSQL’s array implementation adheres to SQL standards, ensuring portability and ease of use.

Syntax

variable_name DATA TYPE [];

PostgreSQL Array Data Type Example

Let's go through some practical examples to better understand how the PostgreSQL Array Data Type can be used. These examples will demonstrate how to create, query, and manipulate arrays within PostgreSQL tables, showcasing the flexibility and power of arrays in managing multi-valued data.

Example 1: Creating a Table with an Array Column

In this example, we will create a table named contacts with an array column called phones to store multiple phone numbers for each contact.

Query:

CREATE TABLE contacts (
    id serial PRIMARY KEY,
    name VARCHAR (100),
    phones TEXT []
);

INSERT INTO contacts (name, phones)
VALUES
    (
        'Raju Kumar',
        '{"(408)-589-5841"}'
    ),
    (
        'Nikhil Aggarwal',
        '{"(408)-589-5841"}'
    ),
    (
        'Anshul Aggarwal',
        '{"(408)-589-5841"}'
    ),
    (
        'Puja Singh',
        '{"(408)-589-5842", "(408)-589-58423"}'
    );

SELECT
    name,
    phones
FROM
    contacts;

Output PostgreSQL Array Data Type Example

Example 2: Searching for a Specific Phone Number

In the same table we created in the above example, we will query to know who has the phone number '(408)-589-5842' irrespective of the position of the phone number in the phone's array, using ANY() function as follows.

Query:

SELECT
    name,
    phones
FROM
    contacts
WHERE
    '(408)-589-5842' = ANY (phones);

Output

Example 3: Updating Array Elements

We can also update the elements within an array. For example, if we want to add a new phone number to Raju Kumar's contact, we can do this by updating the phones array:

Query:

UPDATE contacts
SET phones = array_append(phones, '(408)-589-5899')
WHERE name = 'Raju Kumar';

Output

id	name	phones
1	Raju Kumar	{ "(408)-589-5841", "(408)-589-5899" }
2	Nikhil Aggarwal	{ "(408)-589-5841" }
3	Anshul Aggarwal	{ "(408)-589-5841" }
4	Puja Singh	{ "(408)-589-5842", "(408)-589-58423" }

Example 4: Removing a Specific Element from the Array

If we need to remove a specific element from an array, we can use the array_remove function. For example, to remove the phone number "(408)-589-5845" from Puja Singh's contact, you can do the following:

Query:

UPDATE contacts
SET phones = array_remove(phones, '(408)-589-5845')
WHERE name = 'Puja Singh';

Output

id	name	phones
1	Raju Kumar	{ "(408)-589-5841", "(408)-589-5899" }
2	Nikhil Aggarwal	{ "(408)-589-5841" }
3	Anshul Aggarwal	{ "(408)-589-5841" }
4	Puja Singh	{ "(408)-589-5842", "(408)-589-58423" }

Conclusion

The PostgreSQL Array Data Type is a flexible feature that allows us to store multiple values within a single column, enabling more compact and efficient data storage. By using arrays, we can simplify our database schema and improve performance when dealing with multi-valued data. Whether we need to store multiple phone numbers, tags, or other related data points, the PostgreSQL Array Data Type provides an elegant solution.

PostgreSQL - Array Data Type

RajuKumar19

Improve

Article Tags :

PostgreSQL - Array Data Type

PostgreSQL Array Data Type

Key Features of PostgreSQL Arrays:

PostgreSQL Array Data Type Example

Example 1: Creating a Table with an Array Column

Example 2: Searching for a Specific Phone Number

Example 3: Updating Array Elements

Example 4: Removing a Specific Element from the Array

Conclusion

Similar Reads