Пример работы с таблицами через psql

Дарья Барышева

Обзор

Наиболее простой способ работы с PostgreSQL-таблицами — использование терминального клиента psql. Этот клиент позволяет вводить запросы, передавать их в PostgreSQL и отображать возвращаемые результаты. В psql также поддерживается передача запросов из файлов и аргументов командной строки. Psql становится доступен на каждой ноде ADPG-кластера сразу после его установки.

Чтобы начать работу с psql, требуется выполнить следующие команды. В них устанавливается соединение с базой данных по умолчанию (в нашем примере — postgres) от имени пользователя postgres (также создаваемого по умолчанию).

$ sudo su - postgres
$ psql

Приветствие psql завершается символом #. Все последующие команды должны вводиться после этого символа.

psql (14.1)
Type "help" for help.

postgres=#

Чтобы выйти из psql, необходимо выполнить команду:

\q

Основные операции с данными, доступные в PostgreSQL, описаны ниже. Полный перечень этих и других полезных команд можно найти в официальной документации PostgreSQL.

Все примеры основаны на простой базе данных, хранящей информацию о книгах и их авторах.

Шаг 1. Создание базы данных

Чтобы создать новую базу данных, необходимо использовать выражение CREATE DATABASE, затем указать имя базы данных и завершить запрос точкой с запятой ;.

ВАЖНО

Каждый PostgreSQL-запрос необходимо завершать точкой с запятой ;.

В следующем примере создается новая база данных books_store.

CREATE DATABASE books_store;

Результат команды:

CREATE DATABASE

Чтобы подключиться к новой базе данных, можно использовать psql-команду \c:

\c books_store

Результат команды:

You are now connected to database "books_store" as user "postgres".
books_store=#

Шаг 2. Создание таблицы

Чтобы создать новую таблицу, необходимо использовать выражение CREATE TABLE, затем указать имя таблицы, описать все ее столбцы (путем определения их имен, типов данных и модификаторов), определить индексы и прочие ограничения (constraints) и завершить запрос точкой с запятой ;.

Следующий запрос создает таблицу author с двумя столбцами:

  • id — уникальный целочисленный идентификатор автора, выполняющий роль первичного ключа таблицы (и не поддерживающий NULL-значения по умолчанию);

  • name — текстовое поле, не поддерживающее NULL-значения. В нем будут храниться фамилии и инициалы авторов книг.

CREATE TABLE author (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);
РЕКОМЕНДАЦИЯ

Для настройки автоинкрементирования столбца id в примере используется тип данных SERIAL. Но если функционала типов serial недостаточно (например, требуется сделать шаг автоинкремента отличным от 1), можно настроить собственные политики для генерации числовых последовательностей — с использованием SEQUENCE.

Следующий запрос создает таблицу book с четырьмя столбцами:

  • id — уникальный целочисленный идентификатор книги, выполняющий роль первичного ключа таблицы (и не поддерживающий NULL-значения по умолчанию);

  • title — текстовое поле, не поддерживающее NULL-значения. В нем будут храниться названия книг;

  • author_id — целочисленное поле, не поддерживающее NULL-значения. В нем будут храниться уникальные идентификаторы авторов книг. Для явного определения связи между таблицами author и book (и соблюдения целостности их данных) будет добавлен внешний ключ fk_author, основанный на столбце author_id;

  • public_year — целочисленное поле, поддерживающее NULL-значения. В нем будут храниться годы публикации книг.

CREATE TABLE book (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author_id INT NOT NULL,
  public_year SMALLINT NULL,
  CONSTRAINT fk_author FOREIGN KEY(author_id) REFERENCES author(id));

Результат обеих команд приведен ниже:

CREATE TABLE

Шаг 3. Получение информации о таблице

Чтобы проверить существование таблицы и получить информацию о ней, можно использовать следующие команды:

  • \d <table_name>. Эта команда возвращает краткую информацию о столбцах таблицы с именем <table_name>: название, тип, collation, допустимость NULL-значений и значение по умолчанию. Также команда выводит индексы и первичные/внешние ключи таблицы.

    \d author

    Результат команды:

                                        Table "public.author"
 Column |          Type          | Collation | Nullable |              Default
--------+------------------------+-----------+----------+------------------------------------
 id     | integer                |           | not null | nextval('author_id_seq'::regclass)
 name   | character varying(100) |           | not null |
Indexes:
    "author_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "book" CONSTRAINT "fk_author" FOREIGN KEY (author_id) REFERENCES author(id)

    Для несуществующих таблиц команда возвращает следующий результат:

    books_store=# \d not_existed
Did not find any relation named "not_existed".

  • \d+ <table_name>. По сравнению с командой \d, команда \d+ возвращает дополнительные данные о столбцах таблицы с именем <table_name>: тип хранения и сжатия данных, показатель для сбора статистики stats target, описание. Команда также выводит способ доступа (access method), применяемый к таблице.

    \d+ author

    Результат команды:

                                                                   Table "public.author"
 Column |          Type          | Collation | Nullable |              Default               | Storage  | Compression | Stats target | Description
--------+------------------------+-----------+----------+------------------------------------+----------+-------------+--------------+-------------
 id     | integer                |           | not null | nextval('author_id_seq'::regclass) | plain    |             |              |
 name   | character varying(100) |           | not null |                                    | extended |             |              |
Indexes:
    "author_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "book" CONSTRAINT "fk_author" FOREIGN KEY (author_id) REFERENCES author(id)
Access method: heap

    Для несуществующих таблиц команда возвращает следующий результат:

    books_store=# \d+ not_existed
Did not find any relation named "not_existed".

  • \dt. Эта команда возвращает список всех отношений (relations), хранимых в текущей базе данных. По каждому объекту БД выводится следующая информация: имя схемы, название, тип и владелец.

    \dt

    Результат команды:

             List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | author | table | postgres
 public | book   | table | postgres
(2 rows)

  • \dt+. По сравнению с командой \dt, команда \dt+ возвращает дополнительную информацию по отношениям, хранимым в БД: persistence, способ доступа, размер, описание.

    \dt+

    Результат команды:

                                    List of relations
 Schema |  Name  | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+--------+-------+----------+-------------+---------------+---------+-------------
 public | author | table | postgres | permanent   | heap          | 0 bytes |
 public | book   | table | postgres | permanent   | heap          | 0 bytes |
(2 rows)

Шаг 4. Добавление новых данных в таблицу

Чтобы добавить новые данные в таблицу, необходимо использовать выражение INSERT INTO, затем указать название таблицы, имена столбцов, перечислить значения столбцов после ключевого слова VALUES и завершить запрос точкой с запятой ;. Названия столбцов и их значения заключаются в круглые скобки. Значения столбцов указываются в том же порядке, что и их названия.

Следующий пример показывает, как добавить пять новых строк в таблицу author с использованием пяти различных запросов.

ПРИМЕЧАНИЕ
Значение столбца id не указывается явно, так как оно заполняется автоматически. 
INSERT INTO author(name) VALUES('Virginia Woolf');
INSERT INTO author(name) VALUES('Harper Lee');
INSERT INTO author(name) VALUES('F. Scott Fitzgerald');
INSERT INTO author(name) VALUES('J.R.R. Tolkien');
INSERT INTO author(name) VALUES('George Orwell');

Результат каждой из приведенных выше команд:

INSERT 0 1

Для добавления более одной строки в рамках одного запроса INSERT необходимо перечислить все кортежи данных через запятую после ключевого слова VALUES.

INSERT INTO book(title, author_id, public_year) VALUES
('Mrs. Dalloway',1,1925),
('To the Lighthouse',1,1927),
('To Kill a Mockingbird',2,1960),
('The Great Gatsby',3,1925),
('The Lord of the Rings',4,1955);

Результат команды:

INSERT 0 5

Команда INSERT допускает использование подзапросов. Например, в следующем коде используются подзапросы для получения идентификатора автора по его имени.

INSERT INTO book(title, author_id, public_year) VALUES
('1984',(SELECT id FROM author WHERE name = 'George Orwell'),1949),
('Animal Farm',(SELECT id FROM author WHERE name = 'George Orwell'),1945);

Результат команды:

INSERT 0 2

Шаг 5. Чтение данных из таблицы

Для чтения данных из таблицы необходимо использовать выражение SELECT, затем перечислить имена столбцов (или указать символ * для извлечения всех столбцов), указать имя таблицы после ключевого слова FROM, описать условия отбора строк в секции WHERE и завершить запрос точкой с запятой ;. Дополнительно можно использовать выражения ORDER, GROUP BY, HAVING и другие стандартные SQL-конструкции. Примеры различных SELECT-запросов приведены ниже.

ПРИМЕЧАНИЕ
Для получения дополнительной информации по PostgreSQL-запросам можно обратиться к официальной документации PostgreSQL.

Чтение всех данных

Следующий запрос возвращает все данные, хранящиеся в таблице author.

SELECT * FROM author;

Так как в примере используется символ * вместо названий столбцов и не заполнен блок WHERE, в выборку попадают все строки и столбцы таблицы.

 id |        name
----+---------------------
  1 | Virginia Woolf
  2 | Harper Lee
  3 | F. Scott Fitzgerald
  4 | J.R.R. Tolkien
  5 | George Orwell
(5 rows)

Использование WHERE

Следующий запрос возвращает названия книг, опубликованных в 1925 году.

SELECT
  title
FROM book
WHERE public_year = 1925;

Результат команды:

      title
------------------
 Mrs. Dalloway
 The Great Gatsby
(2 rows)

Сортировка результатов

Следующий запрос возвращает названия и годы публикации для всех книг, хранимых в таблице book. В запросе применяется сортировка по году.

SELECT
  title,
  public_year
FROM book
ORDER BY public_year;

Результат команды:

         title         | public_year
-----------------------+-------------
 The Great Gatsby      |        1925
 Mrs. Dalloway         |        1925
 To the Lighthouse     |        1927
 Animal Farm           |        1945
 1984                  |        1949
 The Lord of the Rings |        1955
 To Kill a Mockingbird |        1960
(7 rows)

Группировка результатов

Следующий запрос группирует все записи о книгах, хранящиеся в таблице book, по году публикации и возвращает количество книг, опубликованных за каждый год. Сортировка по году применяется по умолчанию.

SELECT
  public_year,
  COUNT(*) AS books_count
FROM book
GROUP BY public_year;

Результат команды:

 public_year | books_count
-------------+-------------
        1925 |           2
        1960 |           1
        1945 |           1
        1949 |           1
        1927 |           1
        1955 |           1
(6 rows)

Соединение таблиц

Следующий запрос использует соединение (INNER JOIN) двух таблиц author и book — чтобы вывести имена авторов вместо их идентификаторов, хранимых в таблице book.

SELECT
  author.name,
  book.title,
  book.public_year
FROM book INNER JOIN author ON author.id = book.author_id
ORDER BY public_year;

Результат команды:

        name         |         title         | public_year
---------------------+-----------------------+-------------
 F. Scott Fitzgerald | The Great Gatsby      |        1925
 Virginia Woolf      | Mrs. Dalloway         |        1925
 Virginia Woolf      | To the Lighthouse     |        1927
 George Orwell       | Animal Farm           |        1945
 George Orwell       | 1984                  |        1949
 J.R.R. Tolkien      | The Lord of the Rings |        1955
 Harper Lee          | To Kill a Mockingbird |        1960
(7 rows)

Комбинация SELECT с CREATE и INSERT

Команду SELECT можно использовать для копирования данных из одной таблицы в другую — в комбинации с командами CREATE и INSERT.

Приведенный ниже запрос создает новую таблицу book_copy с той же структурой, что у таблицы book. Запрос также копирует первые три строки из исходной таблицы в новую — с использованием команды SELECT.

ВНИМАНИЕ
Команда CREATE TABLE AS не копирует индексы и другие ограничения на уровне таблицы (table constraints). Если этот функционал необходим, рекомендуем использовать стандартную команду CREATE TABLE с выражением LIKE. 
CREATE TABLE book_copy
AS SELECT * FROM book WHERE id <= 3;

Результат команды:

SELECT 3

Следующий запрос копирует оставшиеся строки из исходной таблицы в новую — с помощью команды INSERT.

INSERT INTO book_copy
SELECT * FROM book WHERE id > 3;

Результат команды:

INSERT 0 4

Чтобы проверить результаты запросов, можно выполнить следующие команды. Содержимое двух таблиц идентично.

books_store=# SELECT * FROM book_copy;
 id |         title         | author_id | public_year
----+-----------------------+-----------+-------------
  1 | Mrs. Dalloway         |         1 |        1925
  2 | To the Lighthouse     |         1 |        1927
  3 | To Kill a Mockingbird |         2 |        1960
  4 | The Great Gatsby      |         3 |        1925
  5 | The Lord of the Rings |         4 |        1955
  6 | 1984                  |         5 |        1949
  7 | Animal Farm           |         5 |        1945
(7 rows)
books_store=# SELECT * FROM book;
 id |         title         | author_id | public_year
----+-----------------------+-----------+-------------
  1 | Mrs. Dalloway         |         1 |        1925
  2 | To the Lighthouse     |         1 |        1927
  3 | To Kill a Mockingbird |         2 |        1960
  4 | The Great Gatsby      |         3 |        1925
  5 | The Lord of the Rings |         4 |        1955
  6 | 1984                  |         5 |        1949
  7 | Animal Farm           |         5 |        1945
(7 rows)

Шаг 6. Обновление данных в таблице

Чтобы обновить значение одного или нескольких столбцов в выбранных строках таблицы, необходимо использовать выражение UPDATE, затем указать имя таблицы, перечислить через запятую список новых значений столбцов (используя формат <имя столбца> = <новое значение>[, …​] в секции SET), описать условия отбора строк в секции WHERE и завершить запрос точкой с запятой ;.

Приведенный ниже запрос добавляет новую строку в таблицу author.

INSERT INTO author(name) VALUES('Test author');

Результат команды:

INSERT 0 1

Следующий запрос обновляет добавленную строку — путем присоединения постфикса _updated к значению столбца name.

UPDATE author
SET name = CONCAT(name, '_updated')
WHERE name = 'Test author';

Результат команды:

UPDATE 1

Чтобы проверить результаты запроса, можно вывести все данные таблицы, используя команду SELECT. Значение столбца успешно обновлено.

books_store=# SELECT * FROM author;
 id |        name
----+---------------------
  1 | Virginia Woolf
  2 | Harper Lee
  3 | F. Scott Fitzgerald
  4 | J.R.R. Tolkien
  5 | George Orwell
  6 | Test author_updated
(6 rows)
ВНИМАНИЕ
Будьте осторожны с использованием команды UPDATE. Пропуск условия WHERE может привести к обновлению всех строк в таблице.

Шаг 7. Удаление данных из таблицы

Чтобы удалить одну или несколько строк из таблицы, необходимо использовать выражение DELETE FROM, затем указать имя таблицы, описать условия отбора строк в секции WHERE и завершить запрос точкой с запятой ;.

Следующий запрос удаляет из таблицы author все строки, у которых в значении столбца name присутствует постфикс _updated.

DELETE FROM author
WHERE name LIKE '%_updated';

Результат команды:

DELETE 1

Чтобы проверить результаты запроса, можно вывести все данные таблицы, используя команду SELECT. Одна строка успешно удалена.

books_store=# SELECT * FROM author;
 id |        name
----+---------------------
  1 | Virginia Woolf
  2 | Harper Lee
  3 | F. Scott Fitzgerald
  4 | J.R.R. Tolkien
  5 | George Orwell
(5 rows)
ВНИМАНИЕ
Будьте осторожны с использованием команды DELETE. Пропуск условия WHERE может привести к удалению всех строк в таблице.

Шаг 8. Изменение структуры таблицы

Чтобы изменить структуру таблицы, следует использовать выражение ALTER TABLE, затем указать имя таблицы, описать все необходимые изменения (используя доступные действия) и завершить запрос точкой с запятой ;. Существует несколько форм команды ALTER TABLE. Некоторые из них представлены ниже.

Переименование таблицы

Приведенный ниже запрос изменяет имя таблицы book_copy на book_deleted.

ALTER TABLE book_copy RENAME TO book_deleted;

Результат команды:

ALTER TABLE

Чтобы проверить результаты запроса, можно запустить psql-команду \dt. Таблица успешно переименована.

books_store=# \dt
            List of relations
 Schema |     Name     | Type  |  Owner
--------+--------------+-------+----------
 public | author       | table | postgres
 public | book         | table | postgres
 public | book_deleted | table | postgres
(3 rows)

Переименование столбца

Следующий запрос изменяет имя столбца title в таблице book_deleted на book_name.

ALTER TABLE book_deleted
RENAME COLUMN title TO book_name;

Результат команды:

ALTER TABLE

Чтобы проверить результаты запроса, можно запустить psql-команду \d. Столбец успешно переименован.

books_store=# \d book_deleted
                      Table "public.book_deleted"
   Column    |          Type          | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
 id          | integer                |           |          |
 book_name   | character varying(255) |           |          |
 author_id   | integer                |           |          |
 public_year | smallint               |           |          |

Добавление индекса

Последний пример показывает, как добавить новый уникальный индекс в таблицу book_deleted. Индекс строится на основе двух столбцов: author_id и book_name.

ALTER TABLE book_deleted
ADD CONSTRAINT full_name UNIQUE (author_id, book_name);

Результат команды:

ALTER TABLE

Чтобы проверить результаты запроса, можно запустить psql-команду \d. Новый индекс успешно добавлен.

books_store=# \d book_deleted
                      Table "public.book_deleted"
   Column    |          Type          | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
 id          | integer                |           |          |
 book_name   | character varying(255) |           |          |
 author_id   | integer                |           |          |
 public_year | smallint               |           |          |
Indexes:
    "full_name" UNIQUE CONSTRAINT, btree (author_id, book_name)

Шаг 9. Удаление таблицы

Чтобы удалить таблицу, необходимо использовать выражение DROP TABLE, затем указать имя таблицы и завершить запрос точкой с запятой ;.

DROP TABLE book_deleted;

Результат команды:

DROP TABLE

Чтобы проверить результаты запроса, можно запустить psql-команду \dt. Таблица успешно удалена.

books_store=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | author | table | postgres
 public | book   | table | postgres
(2 rows)
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней