С чего начать
Подключение через psql
psql — клиент командной строки PostgreSQL. Мета-команды начинаются с обратного слэша (\). \l перечисляет базы, \d описывает таблицы, \? показывает справку. Строки подключения (postgresql://) удобны и поддерживаются большинством драйверов. \i filename.sql выполняет SQL-файл. Задайте переменную окружения PGPASSWORD или файл ~/.pgpass, чтобы не вводить пароли.
# connect to a database
psql -h localhost -p 5432 -U postgres -d mydb
# -h host, -p port, -U user, -d database
# connect via connection string
psql "postgresql://user:pass@localhost:5432/mydb"
# common psql meta-commands
\l # list databases
\c mydb # connect to mydb
\dt # list tables
\d users # describe table
\dn # list schemas
\df # list functions
\du # list roles/users
\q # quit
\? # help on meta-commands
\timing # toggle query timingСоздание и удаление баз
CREATE DATABASE создаёт новую базу; можно указать владельца, кодировку, локаль и шаблон. template0 — чистый шаблон без данных, зависящих от локали; template1 (по умолчанию) можно настраивать. DROP DATABASE требует отсутствия активных соединений — WITH (FORCE) (13+) сначала отключает их. Нельзя удалить базу, к которой вы подключены. ALTER DATABASE RENAME меняет имя, но не каталог на диске.
# create a database
CREATE DATABASE mydb
WITH OWNER alice
ENCODING 'UTF8'
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TEMPLATE template0
CONNECTION LIMIT 100;
# create from a template (clone)
CREATE DATABASE mydb_copy TEMPLATE mydb;
# drop a database (must disconnect first)
DROP DATABASE IF EXISTS mydb;
# drop with force (PostgreSQL 13+)
DROP DATABASE IF EXISTS mydb WITH (FORCE);
# rename a database
ALTER DATABASE mydb RENAME TO newdb;Схемы и search_path
Схемы — пространства имён для объектов базы, как папки для таблиц. Схема по умолчанию — 'public'. search_path определяет, какие схемы просматриваются при обращении к неквалифицированным именам. Используйте схемы для организации мультитенантных приложений, разделения ответственности или управления версиями. CASCADE удаляет зависимые объекты; RESTRICT (по умолчанию) отказывает, если объекты существуют. Схемы гибче баз для мультитенантности в рамках одного соединения.
# create a schema
CREATE SCHEMA IF NOT EXISTS app_schema AUTHORIZATION alice;
# set the search path (schema lookup order)
SET search_path TO app_schema, public;
SHOW search_path;
# create a table in a specific schema
CREATE TABLE app_schema.users (id serial PRIMARY KEY);
# move a table between schemas
ALTER TABLE public.users SET SCHEMA app_schema;
# list objects in a schema
SELECT * FROM information_schema.tables
WHERE table_schema = 'app_schema';
# drop a schema (and its objects)
DROP SCHEMA IF EXISTS app_schema CASCADE;Конфигурация и настройки
Настройки PostgreSQL живут в postgresql.conf. SHOW читает текущее значение; SET меняет его для сессии; SET LOCAL — только для текущей транзакции; ALTER DATABASE/ROLE задаёт сохраняемые значения по умолчанию. Некоторые настройки (shared_buffers, max_connections) требуют перезапуска сервера; другие (work_mem, statement_timeout) применяются сразу. pg_reload_conf() применяет изменения конфигурационного файла без перезапуска. Следите за log_min_duration_statement для медленных запросов.
# view a setting
SHOW shared_buffers;
SHOW max_connections;
# set a parameter for the session
SET work_mem = '64MB';
SET statement_timeout = '30s';
# set a parameter for a transaction
BEGIN;
SET LOCAL work_mem = '256MB';
-- heavy sort here
COMMIT; -- reverts to previous value
# set at database or role level (persists)
ALTER DATABASE mydb SET log_min_duration_statement = 100;
ALTER ROLE alice SET search_path TO app_schema, public;
# reload config without restart
SELECT pg_reload_conf();
# view config file location
SHOW config_file;Скрипты и переменные psql
psql поддерживает скрипты с переменными (\set), включением файлов (\i), перенаправлением вывода (\o) и выполнением команд оболочки (\!). Переменные подставляются через :varname. Используйте \set для циклов и условий в скриптах. Флаг -f выполняет файл и выходит — удобно для пакетных задач и миграций. Для сложных сценариев рассмотрите функции PL/pgSQL или внешние инструменты вроде sqitch или Flyway для управления миграциями.
# run a SQL file
psql -d mydb -f script.sql
psql -d mydb < script.sql
# inside psql
\i /path/to/script.sql
# use psql variables
\set table_name 'users'
SELECT * FROM :table_name;
# prompt for input
\echo -n 'Enter user ID: ' \set user_id `read x && echo $x`
SELECT * FROM users WHERE id = :user_id;
# output to file
\o results.txt
SELECT * FROM users;
\o
# execute shell command
\! dateТаблицы и ограничения
Создание таблиц
serial автоматически создаёт последовательность для автоинкрементных целых; для новых таблиц используйте GENERATED ALWAYS AS IDENTITY (стандарт SQL, предпочтителен с PG10). Ограничения: PRIMARY KEY (уникальность + not null), UNIQUE, NOT NULL, CHECK, REFERENCES (внешний ключ). ON DELETE CASCADE удаляет зависимые строки при удалении родителя; SET NULL обнуляет FK. TEMP-таблицы исчезают после сессии — отлично для промежуточных результатов.
CREATE TABLE users (
id serial PRIMARY KEY,
username varchar(50) UNIQUE NOT NULL,
email varchar(255) UNIQUE NOT NULL,
password text NOT NULL,
age integer CHECK (age >= 0 AND age <= 150),
role varchar(20) NOT NULL DEFAULT 'user',
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
# create with a foreign key
CREATE TABLE posts (
id serial PRIMARY KEY,
user_id integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title text NOT NULL,
body text,
published boolean DEFAULT false
);
# create a temporary table (session-scoped)
CREATE TEMP TABLE temp_stats AS
SELECT user_id, count(*) FROM posts GROUP BY user_id;Identity-колонки (PG10+)
GENERATED ALWAYS AS IDENTITY — стандартная замена serial в SQL: последовательность привязана к колонке, поэтому удаление колонки удаляет и последовательность (serial оставляет осиротевшие последовательности). ALWAYS запрещает ручную вставку ID (для принудительной используйте OVERRIDING SYSTEM VALUE). BY DEFAULT ведёт себя как serial (разрешает ручную вставку). Для новых таблиц берите identity-колонки; serial допустим для существующих. Оба варианта под капотом используют последовательности.
# preferred over serial (SQL standard)
CREATE TABLE products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
# allow manual override (like serial behavior)
CREATE TABLE products (
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
# restart a sequence
ALTER TABLE products ALTER COLUMN id RESTART WITH 1000;
# view identity info
SELECT * FROM pg_sequences WHERE sequencename = 'products_id_seq';Изменение таблиц
ALTER TABLE — способ развивать схему без пересоздания таблиц. Добавление колонки со значением по умолчанию (не волатильным) быстрое начиная с PG11 (без полной перезаписи таблицы). Смена типа колонки может потребовать полной перезаписи и предложения USING для преобразования. CASCADE удаляет зависимые объекты (представления, внешние ключи) — используйте осторожно. Задавайте ограничениям имена для удобного управления в будущем. Применяйте IF EXISTS/IF NOT EXISTS, чтобы скрипты были идемпотентными.
# add a column
ALTER TABLE users ADD COLUMN bio text;
ALTER TABLE users ADD COLUMN score numeric DEFAULT 0;
# drop a column
ALTER TABLE users DROP COLUMN IF EXISTS bio;
ALTER TABLE users DROP COLUMN bio CASCADE; -- drop dependent objects
# rename a column
ALTER TABLE users RENAME COLUMN username TO handle;
# change column type
ALTER TABLE users ALTER COLUMN age TYPE smallint USING age::smallint;
# set/drop default
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'member';
ALTER TABLE users ALTER COLUMN role DROP DEFAULT;
# add constraint
ALTER TABLE users ADD CONSTRAINT email_check
CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$');Ограничения
Ограничения обеспечивают целостность данных на уровне базы — всегда предпочитайте их проверкам в приложении. Составные ключи работают для связующих таблиц. ON UPDATE CASCADE распространяет изменения PK на FK; ON DELETE SET NULL обнуляет FK (колонка FK должна допускать NULL). DEFERRABLE-ограничения проверяются при коммите (а не на каждом операторе), полезны для циклических ссылок. Давайте ограничениям имена для удобного управления — автогенерируемые имена уродливы и различаются между базами.
# named constraint for easy management
ALTER TABLE users
ADD CONSTRAINT positive_age CHECK (age >= 0);
# composite primary key
CREATE TABLE enrollments (
student_id integer REFERENCES students(id),
course_id integer REFERENCES courses(id),
PRIMARY KEY (student_id, course_id)
);
# unique constraint on multiple columns
ALTER TABLE users ADD CONSTRAINT unique_email_domain
UNIQUE (email, domain);
# foreign key with actions
ALTER TABLE posts
ADD FOREIGN KEY (user_id) REFERENCES users(id)
ON UPDATE CASCADE ON DELETE SET NULL;
# drop a constraint
ALTER TABLE users DROP CONSTRAINT positive_age;
# defer a constraint until commit
SET CONSTRAINTS unique_email_domain DEFERRED;Наследование таблиц
Наследование таблиц в PostgreSQL уникально: дочерние таблицы наследуют колонки родителей. Запрос к родителю возвращает строки всех детей, если не указать ONLY. Это полезно для секционирования (до декларативного секционирования) и моделирования таксономий. Однако у наследования есть особенности: внешние ключи не распространяются, а UNIQUE-ограничения действуют только в пределах таблицы. Для новых задач секционирования используйте декларативное секционирование (PARTITION BY) — оно надёжнее и лучше поддерживается.
# PostgreSQL supports table inheritance
CREATE TABLE people (
id serial PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE employees (
salary numeric,
dept text
) INHERITS (people);
CREATE TABLE customers (
credit_limit numeric
) INHERITS (people);
# query parent sees all children
SELECT * FROM people; -- includes employees and customers
SELECT * FROM ONLY people; -- only people, not children
# insert into a child
INSERT INTO employees (name, salary, dept)
VALUES ('Alice', 50000, 'Engineering');Типы данных
Числовые типы
Выбирайте целочисленные типы по размеру данных: smallint для малых диапазонов, bigint для больших. numeric/decimal точны (необходимы для денег), но медленнее real/double. Никогда не используйте float для денег: ошибки округления накапливаются. serial устарел; предпочитайте GENERATED AS IDENTITY. Тип money зависит от локали и негибок — используйте numeric(10,2). В автоинкременте последовательности могут пропускать числа при откате или сбое (так задумано ради производительности).
# integer types (use the smallest that fits)
smallint -- 2 bytes, -32768 to 32767
integer -- 4 bytes, -2B to 2B
bigint -- 8 bytes, very large
# auto-incrementing (use identity, not serial)
id integer GENERATED ALWAYS AS IDENTITY
# decimal/numeric (exact)
numeric(precision, scale) -- e.g. numeric(10,2) for currency
decimal -- alias for numeric
# floating point (inexact, faster)
real -- 4 bytes, 6 decimal digits
double precision -- 8 bytes, 15 decimal digits
# special
serial / bigserial -- legacy auto-increment (prefer identity)
money -- currency (prefer numeric)
# examples
CREATE TABLE products (
price numeric(10,2), -- $99999999.99
weight real, -- 1.5 (kg)
quantity integer DEFAULT 0
);Текстовые типы
В PostgreSQL varchar, char и text имеют одинаковую производительность: text предпочтителен для неограниченного текста, varchar(n) — когда нужна проверка длины. char(n) дополняет пробелами и нужен редко. Изменение длины varchar — операция только с метаданными (быстро). LIKE использует шаблоны % и _, ILIKE не учитывает регистр, ~ — POSIX regex. Для полнотекстового поиска используйте tsvector/tsquery (ниже). Всегда храните текст как text/varchar, а не как bytea.
# three text types (all variable-length)
varchar(n) -- varchar with max length
char(n) -- fixed-length, padded with spaces
text -- unlimited length (preferred)
# in practice, all three perform the same
# varchar(n) adds a length check; char(n) pads (rarely useful)
CREATE TABLE articles (
title varchar(200) NOT NULL,
slug varchar(100) UNIQUE,
body text,
summary text DEFAULT ''
);
# string functions
SELECT length(body), char_length(title), octet_length(title)
FROM articles;
# change length limit (fast, no rewrite)
ALTER TABLE articles ALTER COLUMN title TYPE varchar(500);
# pattern matching
SELECT * FROM articles WHERE title LIKE '%postgres%';
SELECT * FROM articles WHERE title ILIKE '%postgres%'; -- case-insensitive
SELECT * FROM articles WHERE slug ~ '^[a-z0-9-]+$'; -- regexТипы даты и времени
Всегда используйте timestamptz (а не timestamp) для меток времени: он хранит UTC и при выводе конвертирует в часовой пояс сессии. timestamp (без tz) наивен и вызывает баги в приложениях с несколькими часовыми поясами. interval мощно работает в арифметике ('1 day', '2 hours', '3 months'). date_trunc округляет вниз до единицы. to_char/to_timestamp дают гибкие форматы. Задавайте timezone на сессию или роль — данные всё равно хранятся в UTC.