Mastering PostgreSQL Databases: From Basics to Advanced/

...

/

Working with Complex Data Types

Working with Complex Data Types

Learn to work with various complex data types such as arrays and JSON objects.

We'll cover the following...

As we’ve seen, PostgreSQL allows us to store and manipulate traditional data types such as integers, strings, and booleans. But it also offers more complex data types for more specialized storage and manipulation.

One example is the array data type, which allows us to store an ordered list of elements within a single column. This can be useful for storing multiple choices in a single field, such as a list of hobbies or past job titles.

Array data type

We can declare a column to be of type array followed by the type of elements it will contain in square brackets. For example, we use the syntax hobbies TEXT[] to create a column for storing a list of text elements.

Press + to interact
CREATE TABLE Person (
   id SERIAL PRIMARY KEY,
   hobbies TEXT[]
);

Once our array column is created, we can insert elements into it using the curly brace notation, such as {'painting', 'hiking', 'cooking'}. To insert data into an array column, we use the syntax ARRAY['<element 1>', '<element 2>', ...]. Here, <element 1> is the first element in the array, and so on. For example:

Press + to interact
INSERT INTO
  Person (hobbies)
VALUES
  (ARRAY ['reading', 'hiking', 'cooking']);
SELECT hobbies[1] FROM Person;

To access elements within an array, we can use the square bracket notation with the index of the element we want to access. For example, to return to the first hobby in our list above, we use the following:

Press + to interact
SELECT hobbies FROM Person WHERE id=1;

Once our array column is created, we can insert elements into it using the curly brace notation, such as {'reading', 'hiking', 'cooking'}. We can access individual elements using the square bracket notation, such as SELECT hobbies['1'] to retrieve the second item in the list.

We can also use the built-in functions and operators to manipulate our array data.

A list of built-in functions and operators to manipulate arrays in PostgreSQL is given below:

Array Operators and Functions

Operators/Functions

Description

ARRAY[]

Creates an array with the given elements

||

Concatenates two arrays

&&

Intersects two arrays

=

Checks if two arrays are equal

array_append(anyarray, anyelement):



Adds a new element to the end of an array

array_prepend(anyarray, anyelement)

Adds a new element to the beginning of an array




array_remove(anyarray, anyelement)

Removes all occurrences of a specific element from an array


array_length(anyarray)

Returns the number of elements in an array


...