PostgreSQL – Array Data Type

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

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

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

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.

FAQs

What is an array in PostgreSQL?

An array in PostgreSQL is a collection of elements of the same data type, stored in a single column. It allows you to store multiple values in a single field, making it useful for handling lists or sets of related data.

Does PostgreSQL allow arrays?

Yes, PostgreSQL supports arrays natively, allowing users to create columns that can store arrays of any built-in or user-defined data type, making it a powerful tool for working with multiple values.

How do you declare an array in PostgreSQL?

To declare an array in PostgreSQL, you specify the data type followed by square brackets []. For example: CREATE TABLE example (ids INTEGER[]); creates a column that stores an array of integers

R

RajuKumar19

Follow

Improve

Article Tags :

• Databases
• PostgreSQL
• postgreSQL
• postgreSQL-dataTypes