Array in Postgre SQL

You would have seen Array in multiple programming languages but today we are going to see Array in Postgre SQL with different data types.

Atishay Kshetra, Ramtek, Nagpur

Let’s talk about Array constructor which is being use to form the array value.

Array Constructor:- Array constructor is an expression that builds an array value.

Example:-

SELECT Array[1,2,3,4,5];

Output:-

{1,2,3,4,5}

As you can see in the above query we have used Array constructor to build an array of integer type. We can cast above array as given in example to the text array by using :: operator.

Casting to text[]:-

SELECT Array[1,2,3,4,5]::text[];

Output:-

{1,2,3,4,5}

Multidimensional Array:-

In the below example you can see that we can create multidimensional array as well.

Example:-

SELECT Array[Array[‘India’,’China’],Array[‘Pakistan’,’Bangladesh’]];

Output:-

{{India,China},{Pakistan,Bangladesh}}

Now I will create an Employee table with TechStack as text array column and then will insert some data into it.

Create Query:-

CREATE TABLE IF NOT EXISTS public.”Employee”
(
“Id” integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 200000 CACHE 1 ),
“Name” text COLLATE pg_catalog.”default” NOT NULL,
“TechStack” text[] COLLATE pg_catalog.”default” NOT NULL,
CONSTRAINT “Employee_pkey” PRIMARY KEY (“Id”)
)

Insert Query:-

INSERT INTO public.”Employee”(
“Name”, “TechStack”)
VALUES ( ‘Sanjay’, Array[‘JavaScript’,’ASP.NET’,’Python’]);

As you can see in the above examples we have created Employee table with TechStack column having data type as text[] and then we inserted one row where we are adding employee Sanjay with Tech Stack as ‘JavaScript, ASP.NET, Python’.

There are three ways to query into the array data.

Any(<ColumnName>):- Any function compares column values with the single value. It works as equal(=) operator. Below is the example where we are checking who all employees are having JavaScript as their tech stack.

Example:-
select * from public.”Employee” where ‘JavaScript’= ANY(“TechStack”);

Output

@> Operator :- This operator is being used to compare multiple values unlike Any function. If you want to search an array that contains multiple values together, you can use @> operator . Below is the example where we are again comparing TechStack column where employees should have PostgreSQl and ASP.NET as their tech stack.

Example:-

select * from public.”Employee” where “TechStack” @> ‘{“ASP.NET”,”PostgreSQl”}’;

Output

&& Operator :- This operator also is being used to compare multiple values unlike Any function. If you want to search an array that contains one of some values, you can use && operator. Below is the example where we are retrieving the employee details where TechStack column is having any of the values.

Example:-

select * from public.”Employee” where “TechStack” && ‘{“ASP.NET”,”JavaScript”}’;

Output

We can even convert multiple row values into an array as well. To convert the rows into a single array, we have to pass select query inside Array constructor as given in the below example.

Example:-

SELECT Array(SELECT emp.”Name” FROM public.”Employee” as emp);

Output:-

{Sanjay,Arjun}

Thank you for reading please comment your suggestions, share the article, follow me and Abhima Database Technology publication.

Bhagavad Gita Verse of the Day

सहस्रयुगपर्यन्तमहर्यद्ब्रह्मणो विदु: |
रात्रिं युगसहस्रान्तां तेऽहोरात्रविदो जना: || 17||

sahasra-yuga-paryantam ahar yad brahmaṇo viduḥ
rātriṁ yuga-sahasrāntāṁ te ’ho-rātra-vido janāḥ

BG 8.17: One day of Brahma (kalp) lasts a thousand cycles of the four ages (mahā yug) and his night also extends for the same span of time. The wise who know this understand the reality about day and night.