Uday Yadav

Posted on Sep 2, 2021 • Updated on Sep 7, 2021

SQL : Arrays

#sql #programming #database

This guide is for PostgreSQL : https://www.postgresql.org/

Getting Started with PostgreSQL :
https://dev117uday.gitbook.io/databases/sql/getting-started

Loading Sample Data Guide :
https://dev117uday.gitbook.io/databases/sql/getting-started/load-data

Arrays in SQL

Original Documentation : here

Syntax

column_name DATATYPE[] {CONSTRAINT}

CREATE TABLE table_array
(
    id     SERIAL,
    name   varchar(100),
    grades text[]
);

INSERT INTO table_array (name, grades)
VALUES ('person 1', array ['100','45']);
INSERT INTO table_array (name, grades)
VALUES ('person 2', array ['100','90']);
INSERT INTO table_array (name, grades)
VALUES ('person 3', array ['100','97']);
INSERT INTO table_array (name, grades)
VALUES ('person 4', array ['100','94']);


SELECT name, grades[1]
FROM table_array;

   name   | grades 
----------+--------
 person 1 | 100
 person 2 | 100
 person 3 | 100
 person 4 | 100

Array in Tables

Insert

for non text data , use {value1,value2} or array ['value1','value2']
for text data , use {"value1","value2"} or array [value1,value2]

CREATE TABLE teachers
(
    id    serial primary key,
    class text[]
);

CREATE TABLE IF NOT EXISTS teachers
(
    id    serial primary key,
    class text array
);

INSERT INTO teachers (class)
VALUES (array ['english','maths']);

 id |      class      
----+-----------------
  1 | {english,maths}

Query

SELECT class[1]
FROM teachers;

  class  
---------
 english


SELECT *
FROM teachers
WHERE class[1] = 'english';

 id |      class      
----+-----------------
  1 | {english,maths}


SELECT *
FROM teachers
WHERE 'english' = any (class);

 id |      class      
----+-----------------
  1 | {english,maths}

Update

update teachers
set class[1] = 'dutch'
WHERE id = 1;

 id |     class     
----+---------------
  1 | {dutch,maths}

Update teachers
set class[3] = 'science'
WHERE id = 1;

 id |         class         
----+-----------------------
  1 | {dutch,maths,science}

Dimensionless

CREATE TABLE teacher2
(
    id    serial primary key,
    class text array[1]
);

INSERT INTO teacher2 (class)
VALUES (array ['english']);

 id |   class   
----+-----------
  1 | {english}

-- dimensions doesnt matter
INSERT INTO teacher2 (class)
VALUES (array ['english','hindi']);

 id |      class      
----+-----------------
  1 | {english}
  2 | {english,hindi}

Unnest

SELECT id, class, unnest(class)
FROM teacher2;

 id |      class      | unnest  
----+-----------------+---------
  1 | {english}       | english
  2 | {english,hindi} | english
  2 | {english,hindi} | hindi

Multi Dimensional Array

CREATE TABLE students
(
    id    serial primary key,
    name  varchar(50) not null,
    grade integer[][]
);

INSERT INTO students (name, grade)
VALUES ('s1', '{90,2020}'),
       ('s1', '{70,2020}'),
       ('s1', '{60,2020}');

SELECT *
FROM students;

 id | name |   grade   
----+------+-----------
  1 | s1   | {90,2020}
  2 | s1   | {70,2020}
  3 | s1   | {60,2020}

SELECT *
FROM students
WHERE grade @> '{90}';

 id | name |   grade   
----+------+-----------
  1 | s1   | {90,2020}

SELECT *
FROM students
WHERE '2020' = any (grade);

 id | name |   grade   
----+------+-----------
  1 | s1   | {90,2020}
  2 | s1   | {70,2020}
  3 | s1   | {60,2020}

SELECT *
FROM students
WHERE grade[1] < 80;

 id | name |   grade   
----+------+-----------
  2 | s1   | {70,2020}
  3 | s1   | {60,2020}

Array vs JSONB

Advantages to Array

It's pretty easy to setup
Requires less storage than jsonb
It has multi dimensional support
Indexing through GIN, greatly speeds up query
The PostgreSQL planner is likely to make better decisions with PostgreSQL array, as it collects statistics on its content, but not with JSONB.

Disadvantages to Array

Its main advantages is that you are limited to one data type
Have to follow strict order of the array data input.

Advantages to JSONB

Provides additional operators for querying
Support for indexing

Disadvantages to JSONB

Has to parse the json data to binary format
slow in writing, but faster in reading
Doesn't maintain order

Ranges

SELECT INT4RANGE(1, 6)                                                   AS "DEFAULT [(",
       NUMRANGE(1.432, 6.238, '[]')                                      AS "[]",
       DATERANGE('20200101', '20201222', '()')                           AS "DATES ()",
       TSRANGE(LOCALTIMESTAMP, LOCALTIMESTAMP + INTERVAL '8 DAYS', '(]') AS "OPENED CLOSED";

 DEFAULT [( |      []       |        DATES ()         |                       OPENED CLOSED                       
------------+---------------+-------------------------+-----------------------------------------------------------
 [1,6)      | [1.432,6.238] | [2020-01-02,2020-12-22) | ("2021-08-24 05:22:13.03625","2021-09-01 05:22:13.03625"]


SELECT ARRAY [1,2,3]        AS "INT ARRAYS",
       ARRAY [2.123::FLOAT] AS "FLOATING NUMBERS",
       ARRAY [CURRENT_DATE, CURRENT_DATE + 5];

 INT ARRAYS | FLOATING NUMBERS |          array          
------------+------------------+-------------------------
 {1,2,3}    | {2.123}          | {2021-08-24,2021-08-29}


SELECT ARRAY [1,2,3,4] = ARRAY [1,2,3,4],
       ARRAY [1,2,3,4] = ARRAY [1,1,3,4],
       ARRAY [1,2,3,4] <> ARRAY [1,2,3,4],
       ARRAY [1,2,3,4] < ARRAY [1,5,3,4],
       ARRAY [1,2,3,4] <= ARRAY [1,3,3,4],
       ARRAY [1,2,3,4] > ARRAY [1,2,3,4];

 ?column? | ?column? | ?column? | ?column? | ?column? | ?column? 
----------+----------+----------+----------+----------+----------
 t        | f        | f        | t        | t        | f

Inclusion Operators

SELECT ARRAY [1,2,3,4] @> ARRAY [2,3,4]       AS "CONTAINS",
       ARRAY ['A','B'] <@ ARRAY ['A','B','C'] AS "CONTAINED BY",
       ARRAY [1,2,3,4] && ARRAY [2,3,4]       AS "IS OVERLAP";

 CONTAINS | CONTAINED BY | IS OVERLAP 
----------+--------------+------------
 t        | t            | t

Length and Dimensions

SELECT ARRAY [1,2,3] || ARRAY [4,5,6] AS "COMBINED ARRAY";

 COMBINED ARRAY 
----------------
 {1,2,3,4,5,6}

SELECT ARRAY_CAT(ARRAY [1,2,3],
                 ARRAY [4,5,6]) AS "COMBINED ARRAY VIA CAT";

 COMBINED ARRAY VIA CAT 
------------------------
 {1,2,3,4,5,6}

SELECT 4 || ARRAY [1,2,3] AS "ADDING TO ARRAY";

 ADDING TO ARRAY 
-----------------
 {4,1,2,3}

SELECT ARRAY [1,2,3] || 4 AS "ADDING TO ARRAY";

 ADDING TO ARRAY 
-----------------
 {1,2,3,4}

SELECT ARRAY_APPEND(ARRAY [1,2,3], 4) AS "USING APPEND";

 USING APPEND 
--------------
 {1,2,3,4}

SELECT ARRAY_PREPEND(4, ARRAY [1,2,3]) AS "USING APPEND";

 USING APPEND 
--------------
 {4,1,2,3}

SELECT ARRAY_NDIMS(ARRAY [[1,2,3,4],[1,2,3,4],[1,2,3,4]]) AS "DIMENSIONS",
       ARRAY_DIMS(ARRAY [1,2,3,4,2,3,4])                  AS "DIMENSIONS";

 DIMENSIONS | DIMENSIONS 
------------+------------
          2 | [1:7]

SELECT ARRAY_LENGTH(ARRAY [-111,2,3,4], 1);

 array_length 
--------------
            4

SELECT ARRAY_UPPER(ARRAY [1,2,3,4000], 1),
       ARRAY_LOWER(ARRAY [-100,2,3,4], 1);

 array_upper | array_lower 
-------------+-------------
           4 |           1

Positions

SELECT array_position(array ['jan','feb','mar'], 'feb');

 array_position 
----------------
              2

SELECT array_position(array [1,2,2,3,4], 2, 3);

 array_position 
----------------
              3

SELECT array_positions(array [1,2,2,3,4], 2);
 array_positions 
-----------------
 {2,3}

Search, Replace, Remove

SELECT array_cat(array [1,2], array [3,4]);

 array_cat 
-----------
 {1,2,3,4}

SELECT array_append(array [1,2,3], 4);

 array_append 
--------------
 {1,2,3,4}

SELECT array_remove(array [1,2,3,4,4,4], 4);

 array_remove 
--------------
 {1,2,3}

SELECT array_replace(array [1,2,3,4,4,4], 4, 5);

 array_replace 
---------------
 {1,2,3,5,5,5}

IN, NOT IN, ANY

SELECT 20 in (1, 2, 3, 20) as "result";
-- t
SELECT 25 in (1, 2, 3, 20) as "result";
-- f
SELECT 25 not in (1, 2, 3, 20) as "result";
-- t
SELECT 20 = all (Array [20,22]), 20 = all (array [20,20]);
-- f
SELECT 20 = any (Array [1,2,25]) as "result";
-- f

STRING TO Array

SELECT string_to_array('1,2,3,4,5', ',');

 string_to_array 
-----------------
 {1,2,3,4,5}

SELECT string_to_array('1,2,3,4,5,ABC', ',', 'ABC');

 string_to_array  
------------------
 {1,2,3,4,5,NULL}

SELECT string_to_array('1,2,3,4,,6', ',', '');

 string_to_array  
------------------
 {1,2,3,4,NULL,6}

SELECT array_to_string(ARRAY [1,2,3,4], '|');

 array_to_string 
-----------------
 1|2|3|4

SELECT array_to_string(ARRAY [1,2,3,4,NULL], '|', 'EMPTY');

  array_to_string 
-----------------
 1|2|3|4|EMPTY

DEV Community

SQL : Arrays

Arrays in SQL

Syntax

Array in Tables

Insert

Query

Update

Dimensionless

Unnest

Multi Dimensional Array

Array vs JSONB

Advantages to Array

Disadvantages to Array

Advantages to JSONB

Disadvantages to JSONB

Ranges

Inclusion Operators

Length and Dimensions

Positions

Search, Replace, Remove

IN, NOT IN, ANY

STRING TO Array

Top comments (0)

Read next

ChartDB: From Zero to 1.5K GitHub Stars in 3 Days - Here’s How 🚀⭐️

JavaScript’s Fun Twists and How TypeScript Makes It Better

Clear screen at MariaDB CLI

Join us for the Open Source AI Challenge with pgai and Ollama: $3,000 in Prizes!