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:
Contact Us