CREATE TABLE в PostgreSQL — типы данных, ограничения, автоинкремент

CREATE TABLE — базовая команда PostgreSQL. Разбираем синтаксис, все типы данных, ограничения (constraints) и автоинкремент через SERIAL и современный GENERATED ALWAYS AS IDENTITY.

Базовый синтаксис CREATE TABLE

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  username   VARCHAR(100) NOT NULL,
  age        INTEGER CHECK (age >= 0 AND age < 150),
  bio        TEXT,
  score      NUMERIC(5, 2) DEFAULT 0.0,
  active     BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Создать если не существует:
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL
);

Типы данных PostgreSQL

PRIMARY KEY и автоинкремент

-- Старый способ: SERIAL (создаёт SEQUENCE автоматически)
CREATE TABLE posts (
  id    SERIAL PRIMARY KEY,
  title TEXT NOT NULL
);

-- Современный способ (PostgreSQL 10+): IDENTITY
CREATE TABLE posts (
  id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL
);
-- GENERATED ALWAYS — нельзя вставить свой ID без OVERRIDING SYSTEM VALUE
-- GENERATED BY DEFAULT — можно указать свой ID

-- Составной PRIMARY KEY (несколько полей):
CREATE TABLE order_items (
  order_id   INTEGER,
  product_id INTEGER,
  quantity   INTEGER NOT NULL DEFAULT 1,
  PRIMARY KEY (order_id, product_id)
);

-- UUID вместо SERIAL:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE sessions (
  id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id    INTEGER NOT NULL,
  expires_at TIMESTAMP NOT NULL
);

Ограничения (Constraints)

CREATE TABLE products (
  id       SERIAL PRIMARY KEY,
  name     VARCHAR(200) NOT NULL,              -- обязательное поле
  sku      VARCHAR(50) UNIQUE,                 -- уникальное значение (или NULL)
  price    NUMERIC(10,2) CHECK (price > 0),    -- проверка условия
  stock    INTEGER DEFAULT 0 CHECK (stock >= 0),
  status   VARCHAR(20) DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'deleted'))
);

-- Именованные ограничения (удобнее в сообщениях об ошибках):
CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER NOT NULL,
  amount     NUMERIC(12,2),
  status     TEXT NOT NULL DEFAULT 'pending',
  CONSTRAINT orders_amount_positive CHECK (amount > 0),
  CONSTRAINT orders_status_valid
    CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  CONSTRAINT orders_user_fk
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Изменение структуры таблицы (ALTER TABLE)

-- Добавить колонку:
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Добавить колонку NOT NULL с DEFAULT (для существующих строк):
ALTER TABLE users ADD COLUMN verified BOOLEAN NOT NULL DEFAULT FALSE;

-- Изменить тип колонки:
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;

-- Установить DEFAULT:
ALTER TABLE users ALTER COLUMN active SET DEFAULT TRUE;
ALTER TABLE users ALTER COLUMN active DROP DEFAULT;

-- Добавить NOT NULL:
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

-- Переименовать колонку:
ALTER TABLE users RENAME COLUMN username TO login;

-- Удалить колонку:
ALTER TABLE users DROP COLUMN bio;
ALTER TABLE users DROP COLUMN IF EXISTS bio;

-- Переименовать таблицу:
ALTER TABLE users RENAME TO app_users;

-- Удалить таблицу:
DROP TABLE users;
DROP TABLE IF EXISTS users;
DROP TABLE users CASCADE;  -- удалить зависимые объекты (FK, views)

SEQUENCE — управление счётчиками

-- Создать SEQUENCE вручную:
CREATE SEQUENCE user_id_seq START 1000 INCREMENT 1;

-- Использовать в таблице:
CREATE TABLE users (
  id INTEGER DEFAULT nextval('user_id_seq') PRIMARY KEY
);

-- Получить текущее и следующее значение:
SELECT currval('user_id_seq');   -- текущее (после nextval)
SELECT nextval('user_id_seq');   -- следующее (увеличивает счётчик)

-- Сбросить счётчик SERIAL/SEQUENCE:
ALTER SEQUENCE users_id_seq RESTART WITH 1;

-- Посмотреть все sequences:
\ds
SELECT * FROM information_schema.sequences;

-- SERIAL под капотом создаёт sequence:
-- users_id_seq для таблицы users, колонки id

Часто задаваемые вопросы

Как сделать автоинкремент в PostgreSQL?

Два способа: старый — SERIAL (или BIGSERIAL): id SERIAL PRIMARY KEY. Современный (PostgreSQL 10+) — IDENTITY: id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY. Оба автоматически увеличивают значение при каждом INSERT. IDENTITY предпочтительнее: это стандарт SQL, лучше контролирует вставку своих значений, не создаёт неявную зависимость между таблицей и sequence.

SERIAL vs IDENTITY — что выбрать?

Для новых проектов — GENERATED ALWAYS AS IDENTITY. Это стандарт SQL:2003, в отличие от SERIAL (PostgreSQL-специфичный синтаксис). IDENTITY явно запрещает случайную вставку своих значений (GENERATED ALWAYS). SERIAL создаёт sequence с именем tablename_column_seq — которую можно случайно удалить или изменить отдельно от таблицы. Существующий код с SERIAL менять не нужно — работает корректно.

Как узнать структуру таблицы в PostgreSQL?

Через psql: \d users — покажет колонки, типы, nullable, значения по умолчанию, индексы и ограничения. \d+ users — расширенная информация с комментариями. Через SQL: SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = 'users';. В pgAdmin: раскрыть таблицу в дереве объектов → Columns, Constraints, Indexes.