codedamn

Working with arrays in PostgreSQL

Working with arrays in PostgreSQL

Introduction

SQL stands for Structured Query Language, Sequel for short, a language that allows us to communicate with relational database management systems such as MySQL, Postgres, Microsoft SQL Server, and Oracle. These are based on SQL with their own little variations making changes by using a series of keywords, all SQL databases are tables, and containers for data made of columns and rows which is more like an excel spreadsheet having a series of columns containing attributes or types of data. While each row represents an individual record with its unique id known as the primary key.

Define PostgreSQL

Postgres is a relational database that allows data to be related to an open-source database that powers content management systems such as WordPress and eCommerce platforms. It is open-source, highly performant, and supports scalable SQL. One can establish relationships between data points by taking a unique ID from one row and storing it on a different row in a different table in a special column known as a foreign key.

The main role of SQL is to read, create, update and delete the data and also to join the data together based on the relationships embedded within it. So this reduces duplication and redundancy.

The syntax is that after we create a table, we put the parenthesis followed by a semicolon, so for every statement end, we put a semicolon inside each line representing columns, making sure we have an id column.
Note: In other programming languages like javascript, C++, and Java, camel casing is used, but in SQL, it doesn’t follow casing, which means to create is the same as CREATE. But to get it in a structured way, we write the keywords in uppercase.

CREATE TABLE CodedamnProfile(
   id INTEGER PRIMARY KEY,
   name VARCHAR(255),
   age INT,
   email VARCHAR(255),
   password TEXT,
);
-- inserting
INSERT INTO CodedmanProfile
 VALUES(0001,'ALEX',20,'[email protected]','code123');

INSERT INTO CodedamnProfile
 VALUES(0002,'Bob',22,'[email protected]','bob23');

INSERT INTO CodedamnProfile
 VALUES(0003,'Clarke',19,'[email protected]','clarke12');

--fetch
SELECT * FROM CodedmanProfile
Code language: SQL (Structured Query Language) (sql)
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
 id |  name  | age |       email        | password 
----+--------+-----+--------------------+----------
  1 | ALEX   |  20 | [email protected]   | code123
  2 | Bob    |  22 | [email protected]    | bob23
  3 | Clarke |  19 | [email protected] | clarke12
(3 rows)
Code language: Bash (bash)

The SELECT keyword can be used to make a query for the column that we want from a table. Here we have data types, i.e., what constraints we place on the columns. VARCHAR means this is a text-based column, and its upper bound is 255 characters. We can also reduce the limit to a maximum of 30 characters; this is an excellent way to optimize our database and avoid consuming more data than necessary. “*” indicates that all of the column’s data should be retrieved from the CodedamnProfile. We will also use the where keyword to filter, which includes records that meet a certain condition. In addition, the join keyword can be used to map with a different table by matching the primary table to a foreign key on another table. 

How to insert array type in POSTGRESQL

Array types are collections of data that can be single or mixed types; they can be either integer values or a collection of integer values, or a combination of both string and integer values.

We can declare an array with curly brackets as well, using the ARRAY keyword.

CREATE TABLE College(
  st_id int,
  st_name character varying(10),
  st_sub_fav_int integer[],
  st_sub_name text[][]
  );

-- Inserting an array using curly brackets and quotes
 INSERT INTO College(st_id,st_name,st_sub_fav_int,st_sub_name)
 VALUES (1,'Alex','{1,2}','{{"Math","Physics"},{"English","Chemsitry"}}');
  
--directly using array keyword 
insert into College(st_id,st_name,st_sub_fav_int)
values (2,'Bob',ARRAY['13',64]);
select * from College
Code language: SQL (Structured Query Language) (sql)

Remember that in PostgreSQL, the array starts with index 1.

CREATE TABLE
INSERT 0 1
INSERT 0 1
 st_id | st_name | st_sub_fav_int |             st_sub_name              
-------+---------+----------------+--------------------------------------
     1 | Alex    | {1,2}          | {{Math,Physics},{English,Chemsitry}}
     2 | Bob     | {13,64}        | 
(2 rows)Code language: Bash (bash)

Update in an array

The syntax is:

--UPDATE College SET st_sub_fav_int = '{10,20}'
 WHERE st_id = 1;
--SELECT * FROM College;Code language: SQL (Structured Query Language) (sql)

 st_id | st_name | st_sub_fav_int |             st_sub_name              
-------+---------+----------------+--------------------------------------
     2 | Bob     | {13,64}        | 
     1 | Alex    | {10,20}        | {{Math,Physics},{English,Chemsitry}}
(2 rows)Code language: Bash (bash)

Now we can see updated version where the st_sub_fav_int from {1,2} to {10,20}

Also, to update particular specific elements in an array i.e,

UPDATE College SET st_sub_fav_int[1] = 31
WHERE st_id = 2;
--output gonna be:
 st_id | st_name | st_sub_fav_int |             st_sub_name              
-------+---------+----------------+--------------------------------------
     1 | Alex    | {10,20}        | {{Math,Physics},{English,Chemsitry}}
     2 | Bob     | {31,64}        | 
(2 rows)Code language: SQL (Structured Query Language) (sql)

Deletion in arrays

In Postgres, we can delete an array of elements from the specified column using the array_method () function, so the syntax is:

UPDATE Table_name
SET column_name = array_remove(column_name, element_to_delete)
WHERE condition;
Code language: SQL (Structured Query Language) (sql)
INSERT INTO College(st_id,st_name,st_sub_fav_int,st_sub_name)
 VALUES (1,'Alex','{1,2}','{{"Math","Physics"},{"English","Chemsitry"}}');
  
insert into College(st_id,st_name,st_sub_fav_int)
values (2,'Bob',ARRAY['13',64]);

UPDATE College
SET st_sub_fav_int = array_remove(st_sub_fav_int, 1)
WHERE st_id = 1;

SELECT * FROM College;Code language: SQL (Structured Query Language) (sql)

Output:

 st_id | st_name | st_sub_fav_int |             st_sub_name              
-------+---------+----------------+--------------------------------------
     2 | Bob     | {13,64}        | 
     1 | Alex    | {2}            | {{Math,Physics},{English,Chemsitry}}Code language: Bash (bash)

Array range selection process

So in an array of elements, we can select a range of elements by using []. So the syntax will be as:

SELECT column_name[start_index:end_index] FROM table_name;
Code language: SQL (Structured Query Language) (sql)

SELECT st_sub_fav_int[1:2] FROM College WHERE st_id = 1;Code language: SQL (Structured Query Language) (sql)

Output:

 {1,2}Code language: Bash (bash)

Unnest function

In Postgres, the unnest() function is used to expand an array into a set of rows, so the syntax is

SELECT unnest(array_column) FROM table_name;
Code language: SQL (Structured Query Language) (sql)
SELECT unnest(st_sub_name)
FROM College
WHERE st_id = 1;Code language: SQL (Structured Query Language) (sql)

output:

1
2Code language: Bash (bash)

Similarly, it can be done in multidimensional arrays.

SELECT unnest(st_sub_name)
FROM College
WHERE st_id = 1;
--output gonna be
 Math
 Physics
 English
 ChemsitryCode language: SQL (Structured Query Language) (sql)
SELECT C.st_name,U.unnested_elements
FROM College C, unnest(C.st_sub_fav_int) U(unnested_elements)
--output gonna be
 st_name | unnested_elements 
---------+-------------------
 Alex    |                 1
 Alex    |                 2
 Bob     |                13
 Bob     |                64Code language: SQL (Structured Query Language) (sql)

This will join the table “College” with the unnested elements of the “st_sub_fav_int” array and return a result set with the student name and the unnested elements.

Array Functions

Postgresql, on the other hand, provides a plethora of array functions for manipulating data. We will have a look at a few of them:

Appending an element to the end of an array

UPDATE College
SET st_sub_fav_int = array_append(st_sub_fav_int, 7)
WHERE st_id = 1;

output:
 Alex    | {1,2,7}     Code language: SQL (Structured Query Language) (sql)

Replacing an element in an array

UPDATE College
SET st_sub_fav_int = array_replace(st_sub_fav_int,2,4)
WHERE st_id = 1;  --second index will be replaced with 4 number
output :
 Alex    | {1,4}     Code language: SQL (Structured Query Language) (sql)

Removing an element in an array.

UPDATE College
SET st_sub_fav_int = array_remove(st_sub_fav_int, 2)
WHERE st_id = 1;
--removing second element in {1,2} which is 2
output
 Alex    | {1}  Code language: SQL (Structured Query Language) (sql)

Concatenating two arrays so it is, array_cat(e1,e2)

UPDATE College
SET st_sub_fav_int = array_cat(st_sub_fav_int, ARRAY[4, 5])
WHERE st_id = 1;

ouput gonna be
 Alex    | {1,2,4,5}  
Code language: SQL (Structured Query Language) (sql)

There are also methods like array_upper(arr,dimension), array_dims(arr), array_lower(arr,demsion), array_position(ele,arr),etc. More information is available in the documentation.

Some best practices while using arrays in PostgreSQL

  • Avoid arrays if they can be replaced with a simple table with one-to-many relations. By doing so, we can improve our performance and make our query very simple.
  • Use unnest functions very carefully because they can cause memory issues if a large number of rows are returned.
  • Avoid arrays If the data is large, it is better to use other data types like JSON.
  • Use array_length to check the length before performing any operation to avoid using null or empty arrays.
  • Also, having an array with proper and fixed dimensions can help make our query understandable and predictable.

Sharing is caring

Did you like what Nithin Reddy wrote? Thank them for their work by sharing it on social media.

0/10000

No comments so far