PostgreSQL: Шпаргалка для работы с базой данных

Эта шпаргалка охватывает основные операции для работы с PostgreSQL.

Если у вас возникают ошибки типа «permission denied», возможно, вам нужно подключиться с правами суперпользователя или получить необходимые разрешения от администратора базы данных.

Подключение к PostgreSQL

Через терминал (psql)

-- Подключение к конкретной базе данных
psql -h hostname -p port -U username -d database_name
-- Пример: 
psql -h localhost -p 5432 -U postgres -d mydb

-- Подключение без указания базы (к пользовательской базе)
psql -U username

Строка подключения для приложений

postgresql://username:password@hostname:port/database_name

Основные команды в psql

Как посмотреть список всех баз данных?

\l или \list

Как подключиться к базе данных?

\c database_name или \connect database_name

Как посмотреть список всех таблиц в текущей базе?

\dt

Список всех таблиц с подробностями

\dt+

Список всех схем

\dn

Описание структуры таблицы

\d table_name

Полное описание структуры таблицы (с типами и ограничениями)

\d+ table_name

Как посмотреть список пользователей?

\du

Получить помощь по командам

-- Помощь по командам psql
\?

-- Помощь по SQL командам
\h

Как выйти из psql?

\q

Основные SQL-запросы

Создание новой базы данных

CREATE DATABASE database_name;

Удаление базы данных

DROP DATABASE database_name;

Работа с таблицами

-- Создание таблицы
CREATE TABLE table_name (
    column1 data_type CONSTRAINTS,
    column2 data_type CONSTRAINTS,
    ...
);

-- Пример создания таблицы
CREATE TABLE users (
    id SERIAL PRIMARY KEY,        -- Автоинкрементное поле с первичным ключом
    username VARCHAR(50) UNIQUE NOT NULL,  -- Строка фиксированной длины с ограничениями
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- Текущая дата/время по умолчанию
);

-- Удаление таблицы
DROP TABLE table_name;

-- Изменение структуры таблицы (добавление столбца)
ALTER TABLE table_name ADD COLUMN column_name data_type;

-- Изменение структуры таблицы (удаление столбца)
ALTER TABLE table_name DROP COLUMN column_name;

-- Переименование таблицы
ALTER TABLE table_name RENAME TO new_table_name;

Работа с данными

-- Выборка всех данных из таблицы
SELECT * FROM table_name;

-- Выборка конкретных столбцов
SELECT column1, column2 FROM table_name;

-- Выборка с условием
SELECT * FROM table_name WHERE condition;

-- Сортировка результатов
SELECT * FROM table_name ORDER BY column_name [ASC|DESC];

-- Ограничение количества результатов
SELECT * FROM table_name LIMIT number [OFFSET number];

-- Вставка данных
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

-- Обновление данных
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

-- Удаление данных
DELETE FROM table_name WHERE condition;

-- Усечение таблицы (удаление всех строк, но сохранение структуры)
TRUNCATE TABLE table_name;

Работа с индексами

-- Создание индекса
CREATE INDEX index_name ON table_name (column_name);

-- Создание составного индекса
CREATE INDEX index_name ON table_name (column1, column2);

-- Удаление индекса
DROP INDEX index_name;

Резервное копирование и восстановление

# Создание резервной копии базы данных
pg_dump -h hostname -p port -U username -d database_name > backup_file.sql

# Создание сжатой резервной копии
pg_dump -h hostname -p port -U username -d database_name | gzip > backup_file.sql.gz

# Восстановление из резервной копии
psql -h hostname -p port -U username -d database_name < backup_file.sql

# Восстановление из сжатой резервной копии
gunzip -c backup_file.sql.gz | psql -h hostname -p port -U username -d database_name