Skip to content
Related Articles

Related Articles

PostgreSQL – Array Data Type
  • Last Updated : 22 Feb, 2021
GeeksforGeeks - Summer Carnival Banner

PostgreSQL Supports the concept of Arrays. All data type has a companion array associated with it irrespective of the properties of the 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:

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :