PostgreSQL – Array Data Type

PostgreSQL Supports the concept of Arrays. All data type has a companion array associated with it irrespective of the properties of  the data type.It is available even for user-defined data types.

Syntax: variable_name DATA TYPE [];

Now that we know the use and need of Arrays in PostgreSQL, let’s look into some examples. Example 1: First we create a table(say, contacts) where the phones column is defined as an array of text as follows:

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

Now we insert some contacts to our table as follows:

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"}'
    );

Now we query for the contact data as follows:

SELECT
    name,
    phones
FROM
    contacts;

Output: Example 2: 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:

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

Output: