PostgreSQL Advanced Data Types

Introduction

In this lab, you will explore PostgreSQL's advanced data types, focusing on JSON/JSONB, arrays, and UUIDs. You will learn how to store, query, and manipulate data within these types.

The lab begins by demonstrating how to store and query JSON and JSONB data, including creating a table with a JSONB column, inserting JSON data, and using operators like -> and ->> to extract specific values. You will then proceed to learn about array columns and UUIDs.

Store and Query JSON and JSONB Data

In this step, you will learn how to store and query JSON and JSONB data in PostgreSQL. PostgreSQL provides two data types for storing JSON data: JSON and JSONB. The JSON data type stores an exact copy of the JSON input text, while the JSONB data type stores JSON data in a decomposed binary format. JSONB is generally preferred because it offers better performance for querying and indexing.

Let's start by opening the PostgreSQL shell. First, connect to the labex database:

sudo -u postgres psql -d labex

You should see the PostgreSQL prompt:

labex=#

Now, let's create a table to store JSONB data:

CREATE TABLE products (id SERIAL PRIMARY KEY, data JSONB);

This SQL command creates a table named products. The table has two columns: id (an auto-incrementing integer primary key) and data (a JSONB column to store JSON data).

You should see output similar to this:

CREATE TABLE

Now, let's insert some data into the products table:

INSERT INTO products (data) VALUES ('{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}');
INSERT INTO products (data) VALUES ('{"name": "Keyboard", "price": 75, "features": ["Mechanical", "RGB Backlight"]}');

These commands insert two rows into the products table. Each row contains a JSON object with information about a product.

You should see output similar to this for each insert:

INSERT 0 1

To query the JSON data, you can use the -> and ->> operators. The -> operator returns a JSON object, while the ->> operator returns a JSON value as text.

For example, to retrieve the name of the first product, you can use the following query:

SELECT data ->> 'name' FROM products WHERE id = 1;

This command selects the value associated with the key name from the data column of the products table, where the id is 1. The ->> operator ensures that the result is returned as text.

You should see output similar to this:

  ?column?
----------
 Laptop
(1 row)

You can also query nested JSON objects. For example, to retrieve the first feature of the first product, you can use the following query:

SELECT data -> 'features' ->> 0 FROM products WHERE id = 1;

This command first selects the features array from the data column, and then selects the element at index 0 from the array. The ->> operator ensures that the result is returned as text.

You should see output similar to this:

  ?column?
----------
 16GB RAM
(1 row)

You can also use the @> operator to check if a JSON object contains a specific key-value pair. For example, to find all products with a price of 75, you can use the following query:

SELECT data ->> 'name' FROM products WHERE data @> '{"price": 75}';

This command selects the name of all rows from the products table where the data column contains a JSON object with a price key and a value of 75.

You should see output similar to this:

  ?column?
----------
 Keyboard
(1 row)

Finally, let's clean up the table we created before moving to the next step:

DROP TABLE products;

You should see output similar to this:

DROP TABLE

Create and Manipulate Array Columns

In this step, you will learn how to create and manipulate array columns in PostgreSQL. Array columns allow you to store multiple values of the same data type in a single column. This can be useful for storing lists of items, such as tags, categories, or features.

If you're not already in the PostgreSQL shell, connect to the database:

sudo -u postgres psql -d labex

Let's start by creating a table with an array column:

CREATE TABLE products (id SERIAL PRIMARY KEY, name VARCHAR(255), tags TEXT[]);

This command creates a table named products with three columns: id (an auto-incrementing integer primary key), name (a string), and tags (an array of strings). The TEXT[] data type specifies that the tags column is an array of text values.

You should see output similar to this:

CREATE TABLE

Now, let's insert some data into the products table:

INSERT INTO products (name, tags) VALUES ('Laptop', ARRAY['electronics', 'computers', 'portable']);
INSERT INTO products (name, tags) VALUES ('Keyboard', ARRAY['electronics', 'accessories', 'input']);

These commands insert two rows into the products table. The ARRAY[] syntax is used to specify the array values.

You should see output similar to this for each insert:

INSERT 0 1

To query the array data, you can use array indexing. Array indices in PostgreSQL start at 1.

For example, to retrieve the first tag of the first product, you can use the following query:

SELECT tags[1] FROM products WHERE id = 1;

This command selects the element at index 1 from the tags array of the products table, where the id is 1.

You should see output similar to this:

   tags
-----------
 electronics
(1 row)

You can also use the UNNEST function to expand an array into a set of rows.

For example, to retrieve all tags from all products, you can use the following query:

SELECT name, UNNEST(tags) AS tag FROM products;

This command selects the name and each individual tag from the tags array, creating a new row for each tag.

You should see output similar to this:

  name   |     tag
---------+-------------
 Laptop  | electronics
 Laptop  | computers
 Laptop  | portable
 Keyboard| electronics
 Keyboard| accessories
 Keyboard| input
(6 rows)

You can use the @> operator to check if an array contains a specific value.

For example, to find all products with the tag 'electronics', you can use the following query:

SELECT name FROM products WHERE tags @> ARRAY['electronics'];

This command selects the name of all rows from the products table where the tags array contains the value 'electronics'.

You should see output similar to this:

  name
----------
 Laptop
 Keyboard
(2 rows)

You can also use the && operator to check if two arrays have any elements in common.

For example, to find all products that share any tags with the first product, you can use the following query:

SELECT p2.name FROM products p1, products p2 WHERE p1.id = 1 AND p1.tags && p2.tags AND p2.id != 1;

This command selects the name of all rows from the products table (aliased as p2) that have at least one tag in common with the tags of the first product (aliased as p1), excluding the first product itself.

You should see output similar to this:

  name
----------
 Keyboard
(1 row)

Finally, let's clean up the table we created:

DROP TABLE products;

You should see output similar to this:

DROP TABLE

Generate and Use UUIDs as Identifiers

In this step, you will learn how to generate and use UUIDs (Universally Unique Identifiers) as identifiers in PostgreSQL. UUIDs are 128-bit numbers that are designed to be unique across space and time. They are often used as primary keys in database tables to avoid conflicts when merging data from different sources.

If you're not already in the PostgreSQL shell, connect to the database:

sudo -u postgres psql -d labex

PostgreSQL provides a function called uuid_generate_v4() to generate UUIDs. However, this function is part of the uuid-ossp extension, which needs to be enabled first:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

This command creates the uuid-ossp extension if it doesn't already exist.

You should see output similar to this:

CREATE EXTENSION

Now, let's create a table with a UUID column as the primary key:

CREATE TABLE products (id UUID PRIMARY KEY, name VARCHAR(255));

This command creates a table named products with two columns: id (a UUID primary key) and name (a string). The UUID data type specifies that the id column will store UUID values.

You should see output similar to this:

CREATE TABLE

Now we can insert data using the uuid_generate_v4() function:

INSERT INTO products (id, name) VALUES (uuid_generate_v4(), 'Laptop');
INSERT INTO products (id, name) VALUES (uuid_generate_v4(), 'Keyboard');

These commands insert two rows into the products table. The uuid_generate_v4() function generates a new UUID for each row.

You should see output similar to this for each insert:

INSERT 0 1

To query the data, you can use the UUID values in your WHERE clauses. Since UUIDs are randomly generated, you will first need to retrieve one to use in a specific query.

Let's retrieve the UUID of one of the products:

SELECT id FROM products LIMIT 1;

This command selects the id from the products table, limiting the result to 1 row. Note the UUID that is returned (it will be a long string of characters and numbers).

You should see output similar to this (your UUID will be different):

                  id
------------------------------------
 a1b2c3d4-e5f6-7890-1234-567890abcdef
(1 row)

Now, use that UUID in the following command, replacing <YOUR_UUID_HERE> with the actual UUID you retrieved:

SELECT name FROM products WHERE id = '<YOUR_UUID_HERE>';

This command selects the name from the products table where the id matches the specified UUID.

You should see output similar to this (depending on which UUID you picked):

  name
----------
 Laptop
(1 row)

Finally, let's clean up the table and extension we created:

DROP TABLE products;
DROP EXTENSION "uuid-ossp";

You should see output similar to this for each drop command:

DROP TABLE
DROP EXTENSION

Extract Data from Advanced Types

In this step, you will learn how to extract data from advanced data types in PostgreSQL, specifically focusing on JSONB and Array types. We will build upon the concepts introduced in the previous steps.

If you're not already in the PostgreSQL shell, connect to the database:

sudo -u postgres psql -d labex

First, let's create a table that includes both a JSONB column and an array column:

CREATE TABLE products (id SERIAL PRIMARY KEY, name VARCHAR(255), details JSONB, tags TEXT[]);

This command creates a table named products with the following columns: id (an auto-incrementing integer primary key), name (a string), details (a JSONB column), and tags (an array of strings).

You should see output similar to this:

CREATE TABLE

Now, let's insert some data into the products table:

INSERT INTO products (name, details, tags) VALUES ('Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"ram": "16GB", "storage": "512GB SSD"}}', ARRAY['electronics', 'computers', 'portable']);
INSERT INTO products (name, details, tags) VALUES ('Keyboard', '{"brand": "Logitech", "model": "G915", "specs": {"type": "Mechanical", "backlight": "RGB"}}', ARRAY['electronics', 'accessories', 'input']);

These commands insert two rows into the products table, including JSONB data in the details column and array data in the tags column.

You should see output similar to this for each insert:

INSERT 0 1

To extract data from the details (JSONB) column, you can use the -> and ->> operators, as learned in Step 1. For example, to extract the brand of the first product:

SELECT details ->> 'brand' FROM products WHERE id = 1;

This command retrieves the value associated with the key brand from the details column of the products table where the id is 1.

You should see output similar to this:

 ?column?
----------
 Dell
(1 row)

To extract nested data from the details column, you can chain the -> and ->> operators. For example, to extract the RAM specification of the first product:

SELECT details -> 'specs' ->> 'ram' FROM products WHERE id = 1;

This command retrieves the value associated with the key ram within the specs object in the details column.

You should see output similar to this:

 ?column?
----------
 16GB
(1 row)

To extract data from the tags (array) column, you can use array indexing, as learned in Step 2. For example, to extract the first tag of the first product:

SELECT tags[1] FROM products WHERE id = 1;

This command retrieves the element at index 1 from the tags array of the products table where the id is 1.

You should see output similar to this:

   tags
-----------
 electronics
(1 row)

You can also combine these techniques to extract data from both JSONB and array columns in the same query. For example, to retrieve the name, brand, and first tag of all products:

SELECT name, details ->> 'brand', tags[1] FROM products;

This command retrieves the name, the brand from the details JSONB column, and the first element of the tags array for each product.

You should see output similar to this:

   name   |  ?column?  |   tags
----------+------------+-----------
 Laptop   | Dell       | electronics
 Keyboard | Logitech   | electronics
(2 rows)

Finally, let's clean up the table we created:

DROP TABLE products;

You should see output similar to this:

DROP TABLE

When you're done with all the steps, you can exit the PostgreSQL shell by typing:

\q

Summary

In this lab, you learned how to store and query JSON and JSONB data in PostgreSQL. You created a table with a JSONB column to store product information.

You then inserted JSON data into the table and used the -> and ->> operators to query specific values within the JSON objects, including nested elements. The lab demonstrated how to retrieve JSON values as both JSON objects and text.

You also explored array columns, learning how to store lists of values, query them using indexing, and expand them into rows using UNNEST. Furthermore, you learned about UUIDs, how to generate them using the uuid-ossp extension, and how to use them as unique identifiers. Finally, you practiced extracting data from tables combining both JSONB and array data types.

PostgreSQL Advanced Data Types

Introduction

Real coding. Zero setup. Maximum fun.

Store and Query JSON and JSONB Data

Create and Manipulate Array Columns

This isn’t just a tutorial. It’s a playground.

Generate and Use UUIDs as Identifiers

Extract Data from Advanced Types

Your personal coding lab, one click away.

Summary