Three PostgreSQL Features That Will Be Useful for Every Beginner

These features have already helped me out more than once in complex projects, and I am sure that they will become reliable assistants in your development as well.

Feature #1: Arrays and Working with JSON

PostgreSQL stands out among relational databases due to its support for arrays and JSON formats. This functionality allows you to store and manipulate complex data structures without the need for additional tables.

Arrays in PostgreSQL allow you to store multiple values ​​of the same data type in a single table cell.

Creating a table with arrays:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[] -- массив текстовых значений для тегов
);

Inserting data into a table with arrays:

INSERT INTO products (name, tags)
VALUES ('Продукт 1', ARRAY['новинка', 'распродажа']),
       ('Продукт 2', ARRAY['популярное', 'скидка']);

Extracting data from an array:

-- найти все продукты, содержащие тег 'новинка'
SELECT * FROM products
WHERE 'новинка' = ANY(tags);

JSON provides the ability to store and manipulate semi-structured data.

Creating a table with JSON:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    profile JSONB -- бинарное представление JSON
);

Inserting JSON data:

INSERT INTO users (name, profile)
VALUES ('Иван Иванов', '{"age": 30, "city": "Москва", "interests": ["футбол", "чтение"]}'),
       ('Мария Смирнова', '{"age": 25, "city": "Санкт-Петербург", "interests": ["музыка", "путешествия"]}');

Extracting data from JSON:

-- извлечь возраст и город пользователя
SELECT 
    name,
    profile->>'age' AS age,
    profile->>'city' AS city
FROM users;

-- Найти пользователей с интересом "музыка"
SELECT * FROM users
WHERE 'музыка' = ANY(profile->'interests');

Where to use?

  • Store lists of preferences, such as favorite products or tags.

  • Storing API responses.

  • Store aggregated data such as statistics and analytics in JSON format for easy processing.

Let's say there is a social networking app. Each user has a profile that may include various attributes: name, age, city, and interests. Using arrays and JSON in PostgreSQL, you can store and retrieve this data:

-- создание таблицы
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100),
    attributes JSONB,
    tags TEXT[]
);

-- вставка данных
INSERT INTO user_profiles (username, attributes, tags)
VALUES ('user1', '{"age": 28, "location": "NY", "preferences": {"newsletter": true}}', ARRAY['active', 'premium']),
       ('user2', '{"age": 34, "location": "LA", "preferences": {"newsletter": false}}', ARRAY['inactive']);

-- запрос для извлечения данных
SELECT 
    username,
    attributes->>'age' AS age,
    attributes->>'location' AS location
FROM user_profiles
WHERE 'active' = ANY(tags);

Feature #2: Extensions

Extensions in PostgreSQL are a way to add additional features and functions to a database. They can be used to extend functionality without having to make changes to the core of the database itself. P

One of the most popular extensions in PostgreSQL ispg_trgmwhich allows you to implement full-text search. It is also worth noting PostGISwhich adds support for geographic data and features.

Installation and usage of the pg_trgm extension:

-- установка расширения
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- создание индекса для полнотекстового поиска
CREATE INDEX trgm_idx ON articles USING gin (content gin_trgm_ops);

-- поиск похожих записей
SELECT * FROM articles
WHERE content % 'поиск';

Let's say you have a database of articles or a blog, and you want to add the ability to search by content:

-- установка расширения
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- создание таблицы статей
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);

-- вставка данных
INSERT INTO articles (title, content)
VALUES ('Статья 1', 'Это пример текста для полнотекстового поиска.'),
       ('Статья 2', 'Поиск похожих текстов в базе данных.');

-- создание индекса
CREATE INDEX content_trgm_idx ON articles USING gin (content gin_trgm_ops);

-- поиск статьи с использованием триграммного поиска
SELECT * FROM articles
WHERE content % 'поиск';

Installing the PostGIS extension:

-- установка PostGIS
CREATE EXTENSION IF NOT EXISTS postgis;

-- создание таблицы с географическими данными
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    coordinates GEOGRAPHY(POINT)
);

-- вставка данных
INSERT INTO locations (name, coordinates)
VALUES ('Place 1', ST_GeographyFromText('SRID=4326;POINT(-122.4194 37.7749)')),
       ('Place 2', ST_GeographyFromText('SRID=4326;POINT(-118.2437 34.0522)'));

With the list of extensions you can read here.

Feature #3: CTE and Recursive Queries

Common table expressions and recursive queries in PostgreSQL provide the ability to simplify and organize complex SQL queries.

Advantages:

  • CTE allows you to break down complex queries into simpler, more understandable parts.

  • The ability to create temporary result sets of data that can be used in the main query.

  • Allows you to reduce repetitive code and improve performance by breaking down operations into subqueries.

An example of using CTE to break down complex queries:

WITH top_products AS (
    SELECT id, name, sales
    FROM products
    WHERE sales > 1000
),
top_customers AS (
    SELECT id, name, purchases
    FROM customers
    WHERE purchases > 500
)
SELECT tp.name AS product_name, tc.name AS customer_name
FROM top_products tp
JOIN top_customers tc ON tp.id = tc.id;

Recursive queries allow you to work with hierarchical structures, such as product categories or organizational structure.

Recursive query to create a category hierarchy:

WITH RECURSIVE category_hierarchy AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL


    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN category_hierarchy ch ON c.parent_id = ch.id
)
SELECT * FROM category_hierarchy;

Let's look at an example of creating a hierarchical structure for a company, where each employee can have subordinates:

-- создание таблицы сотрудников
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(id)
);

-- вставка данных
INSERT INTO employees (name, manager_id)
VALUES ('CEO', NULL),
       ('Manager 1', 1),
       ('Manager 2', 1),
       ('Employee 1', 2),
       ('Employee 2', 2),
       ('Employee 3', 3);

-- рекурсивный запрос для иерархии сотрудников
WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

Conclusion

I hope this article will help you deepen your knowledge of PostgreSQL and inspire you to use its capabilities in your projects.

What features do you know?

Come to the next open lessons dedicated to databases:

  • August 15: «PostgreSQL Failover Cluster”. Let's talk about availability, how it is achieved and how it is related to the architecture. Let's look at the failover implementation in PostgreSQL and set up a cluster so that the failure of one of the nodes of our system does not lead to failures of the entire system as a whole. Sign up via link

  • August 21: «MongoDB Cluster Capabilities”. Let's look at MongoDB replication options, how to shard a MongoDB cluster, and how to choose a sharding key. Sign up via link

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *