PostgreSQL — практическая работа и API

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Инженеру

Другие СУБД в этом цикле: SQLite · MySQL · Microsoft SQL Server · SQL — о разделе.

PostgreSQL — серверная объектно-реляционная СУБД. В отличие от SQLite, она работает как отдельный процесс сервера, поддерживает роли, сетевые подключения, сложные типы данных, расширения и масштабируемые продакшен-сценарии.

Как работает PostgreSQL

Запускается сервер postgres.
Клиенты подключаются по TCP или Unix socket.
Данные хранятся в кластере PostgreSQL (data directory).
Транзакции и MVCC дают конкурентный доступ с изоляцией.
WAL обеспечивает восстановление и репликацию.

SHOW server_version;
SHOW data_directory;
SHOW wal_level;

Подключение и запуск

psql

psql -h localhost -p 5432 -U app_user -d app_db

Строка подключения (URI)

postgresql://app_user:secret@localhost:5432/app_db
postgresql://app_user@localhost/app_db?sslmode=require

Базовая настройка

CREATE ROLE app_user LOGIN PASSWORD 'secret';
CREATE DATABASE app_db OWNER app_user;
GRANT CONNECT ON DATABASE app_db TO app_user;

Типы данных и синтаксис

Ключевые типы PostgreSQL:

Числовые: smallint, integer, bigint, numeric, real, double precision
Строки: text, varchar, char
Дата/время: date, timestamp, timestamptz, interval
Булев: boolean
Бинарные: bytea
Расширенные: json, jsonb, uuid, array, hstore, tsvector, tsquery

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    profile JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Возможности PostgreSQL

Полный SQL + богатый диалект (CTE, оконные функции, RETURNING, UPSERT)
Сложные типы (JSONB, массивы, UUID, геоданные через PostGIS)
Материализованные представления
Расширения (pg_trgm, uuid-ossp, postgis, pg_stat_statements)
Полнотекстовый поиск
Роли и права (GRANT/REVOKE)
Репликация и резервное копирование
Партиционирование
Server-side функции и процедуры (PL/pgSQL, и др.)

Доступные и недоступные команды

Категория	Команда / конструкция	Статус в PostgreSQL	Комментарий
DML	`SELECT`, `INSERT`, `UPDATE`, `DELETE`	Доступно	Полноценная поддержка
DDL	`CREATE/ALTER/DROP`	Доступно	Широкий набор возможностей
Транзакции	`BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`	Доступно	Полная поддержка ACID
Права	`CREATE ROLE`, `GRANT`, `REVOKE`	Доступно	Ролевая модель доступа
Серверные объекты	`FUNCTION`, `PROCEDURE`, `TRIGGER`	Доступно	PL/pgSQL и расширения
Репликация/WAL	streaming replication, logical replication	Доступно	Продакшен-масштабирование
Материализованные view	`CREATE MATERIALIZED VIEW`	Доступно	Поддерживается
Подсказки оптимизатору как `/+ hint /`	Частично/ограничено	Нет стандартных Oracle-style hints
`MERGE`	Доступно (современные версии)	Проверять версию кластера
Горизонтальный шардинг "из коробки"	Частично	Реализуется через архитектуру/расширения

Практика SQL в PostgreSQL

CRUD

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  done BOOLEAN NOT NULL DEFAULT FALSE
);

INSERT INTO tasks(title) VALUES ('Пройти PostgreSQL');
SELECT id, title, done FROM tasks ORDER BY id;
UPDATE tasks SET done = TRUE WHERE id = 1;
DELETE FROM tasks WHERE id = 1;

UPSERT

INSERT INTO tasks(id, title, done)
VALUES (1, 'Обновлённая задача', FALSE)
ON CONFLICT(id) DO UPDATE SET
  title = EXCLUDED.title,
  done = EXCLUDED.done;

Транзакция

BEGIN;
INSERT INTO tasks(title) VALUES ('A');
INSERT INTO tasks(title) VALUES ('B');
COMMIT;

Возврат данных сразу после изменения

INSERT INTO tasks(title) VALUES ('C') RETURNING id, title;
UPDATE tasks SET done = TRUE WHERE id = 2 RETURNING id, done;
DELETE FROM tasks WHERE id = 3 RETURNING id;

Клиенты и инструменты

psql (CLI)
pgAdmin
DBeaver
DataGrip
TablePlus
Beekeeper Studio
VS Code + PostgreSQL extensions

API по языкам — подключение, CRUD, библиотеки, классы и функции

Как читать блоки API

В каждом блоке указаны библиотека, ключевые классы/типы, основные свойства и методы, затем минимальный рабочий пример подключения и CRUD.

C# — Npgsql

Библиотека

Npgsql (NuGet)

Ключевые классы

NpgsqlConnection
NpgsqlCommand
NpgsqlTransaction
NpgsqlDataReader
NpgsqlParameter
NpgsqlDataSource

Свойства и методы

Open(), BeginTransaction()
CommandText, Parameters.AddWithValue()
ExecuteNonQuery(), ExecuteScalar(), ExecuteReader()
Read(), GetFieldValue<T>()

using Npgsql;

var cs = "Host=localhost;Port=5432;Username=app_user;Password=secret;Database=app_db";
await using var conn = new NpgsqlConnection(cs);
await conn.OpenAsync();

await using (var cmd = new NpgsqlCommand("""
    CREATE TABLE IF NOT EXISTS users(
      id BIGSERIAL PRIMARY KEY,
      name TEXT NOT NULL
    )
""", conn))
{
    await cmd.ExecuteNonQueryAsync();
}

await using (var ins = new NpgsqlCommand("INSERT INTO users(name) VALUES (@name)", conn))
{
    ins.Parameters.AddWithValue("name", "Anna");
    await ins.ExecuteNonQueryAsync();
}

await using (var sel = new NpgsqlCommand("SELECT id, name FROM users", conn))
await using (var reader = await sel.ExecuteReaderAsync())
{
    while (await reader.ReadAsync())
        Console.WriteLine($"{reader.GetInt64(0)} {reader.GetString(1)}");
}

Python — psycopg

Библиотека

psycopg (v3)

Ключевые классы

Connection
Cursor
AsyncConnection/AsyncCursor

Методы

psycopg.connect()
cursor(), execute(), executemany()
fetchone(), fetchall()
commit(), rollback()

import psycopg

conn = psycopg.connect("host=localhost dbname=app_db user=app_user password=secret")
cur = conn.cursor()

cur.execute("""
CREATE TABLE IF NOT EXISTS users(
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL
)
""")
cur.execute("INSERT INTO users(name) VALUES (%s)", ("Anna",))
conn.commit()

cur.execute("SELECT id, name FROM users")
for row in cur.fetchall():
    print(row[0], row[1])

cur.execute("UPDATE users SET name=%s WHERE id=%s", ("Ann", 1))
cur.execute("DELETE FROM users WHERE id=%s", (1,))
conn.commit()
conn.close()

Java — JDBC PostgreSQL Driver

Библиотека

org.postgresql:postgresql

Ключевые классы

Connection
PreparedStatement
ResultSet
DriverManager

Методы

DriverManager.getConnection(...)
prepareStatement(), setAutoCommit(false), commit(), rollback()
setString(), executeUpdate(), executeQuery()
next(), getLong(), getString()

import java.sql.*;

try (Connection conn = DriverManager.getConnection(
        "jdbc:postgresql://localhost:5432/app_db", "app_user", "secret")) {

    try (Statement st = conn.createStatement()) {
        st.execute("""
            CREATE TABLE IF NOT EXISTS users(
                id BIGSERIAL PRIMARY KEY,
                name TEXT NOT NULL
            )
        """);
    }

    try (PreparedStatement ins = conn.prepareStatement("INSERT INTO users(name) VALUES (?)")) {
        ins.setString(1, "Anna");
        ins.executeUpdate();
    }

    try (PreparedStatement sel = conn.prepareStatement("SELECT id, name FROM users");
         ResultSet rs = sel.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getLong("id") + " " + rs.getString("name"));
        }
    }
}

C — libpq

Библиотека

libpq

Ключевые типы

PGconn
PGresult

Ключевые функции

PQconnectdb(), PQfinish()
PQexec(), PQexecParams()
PQresultStatus(), PQntuples(), PQgetvalue()
PQclear(), PQerrorMessage()

#include <libpq-fe.h>
#include <stdio.h>

int main(void) {
    PGconn *conn = PQconnectdb("host=localhost dbname=app_db user=app_user password=secret");
    if (PQstatus(conn) != CONNECTION_OK) return 1;

    PQexec(conn, "CREATE TABLE IF NOT EXISTS users(id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL)");
    PQexecParams(conn, "INSERT INTO users(name) VALUES ($1)", 1, NULL, (const char*[]){"Anna"}, NULL, NULL, 0);

    PGresult *res = PQexec(conn, "SELECT id, name FROM users");
    int rows = PQntuples(res);
    for (int i = 0; i < rows; i++) {
        printf("%s %s\n", PQgetvalue(res, i, 0), PQgetvalue(res, i, 1));
    }
    PQclear(res);
    PQfinish(conn);
    return 0;
}

C++ — libpqxx

Библиотека

libpqxx

Ключевые классы

pqxx::connection
pqxx::work
pqxx::result
pqxx::row

Методы

connection(conninfo)
work txn(conn);
txn.exec(), txn.exec_params(), txn.commit()
row["col"].as<T>()

#include <pqxx/pqxx>
#include <iostream>

int main() {
    pqxx::connection conn("host=localhost port=5432 dbname=app_db user=app_user password=secret");
    pqxx::work tx(conn);

    tx.exec("CREATE TABLE IF NOT EXISTS users(id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL)");
    tx.exec_params("INSERT INTO users(name) VALUES ($1)", "Anna");

    pqxx::result r = tx.exec("SELECT id, name FROM users");
    for (auto row : r) {
        std::cout << row["id"].as<long>() << " " << row["name"].as<std::string>() << "\n";
    }
    tx.commit();
}

Go — pgx или database/sql + pq

Библиотеки

github.com/jackc/pgx/v5 (предпочтительно)
database/sql + github.com/lib/pq (классический вариант)

Ключевые типы pgx

*pgx.Conn
pgx.Rows
pgx.Tx

Методы

pgx.Connect(), Close()
Exec(), Query(), QueryRow()
Begin(), Commit(), Rollback()

package main

import (
	"context"
	"fmt"
	"github.com/jackc/pgx/v5"
)

func main() {
	ctx := context.Background()
	conn, _ := pgx.Connect(ctx, "postgres://app_user:secret@localhost:5432/app_db")
	defer conn.Close(ctx)

	conn.Exec(ctx, `CREATE TABLE IF NOT EXISTS users(id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL)`)
	conn.Exec(ctx, `INSERT INTO users(name) VALUES ($1)`, "Anna")

	rows, _ := conn.Query(ctx, `SELECT id, name FROM users`)
	defer rows.Close()
	for rows.Next() {
		var id int64
		var name string
		rows.Scan(&id, &name)
		fmt.Println(id, name)
	}
}

PHP — PDO_PGSQL

Библиотека

PDO с драйвером pgsql

Ключевые классы

PDO
PDOStatement

Методы

new PDO("pgsql:host=...;port=...;dbname=...")
prepare(), execute(), fetchAll()
транзакции: beginTransaction(), commit(), rollBack()

<?php
$db = new PDO("pgsql:host=localhost;port=5432;dbname=app_db", "app_user", "secret");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->exec("CREATE TABLE IF NOT EXISTS users(id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL)");

$stmt = $db->prepare("INSERT INTO users(name) VALUES (:name)");
$stmt->execute([':name' => 'Anna']);

$rows = $db->query("SELECT id, name FROM users")->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    echo $row['id'] . " " . $row['name'] . PHP_EOL;
}

Kotlin — JDBC (PostgreSQL)

Библиотека

JDBC + org.postgresql:postgresql

Ключевые классы

Connection
PreparedStatement
ResultSet

Методы

DriverManager.getConnection()
prepareStatement(), executeUpdate(), executeQuery()
setString(), next(), getLong(), getString()

import java.sql.DriverManager

fun main() {
    DriverManager.getConnection(
        "jdbc:postgresql://localhost:5432/app_db", "app_user", "secret"
    ).use { conn ->
        conn.createStatement().use { st ->
            st.execute("CREATE TABLE IF NOT EXISTS users(id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL)")
        }
        conn.prepareStatement("INSERT INTO users(name) VALUES (?)").use { ps ->
            ps.setString(1, "Anna")
            ps.executeUpdate()
        }
        conn.prepareStatement("SELECT id, name FROM users").use { ps ->
            ps.executeQuery().use { rs ->
                while (rs.next()) println("${rs.getLong("id")} ${rs.getString("name")}")
            }
        }
    }
}

Rust — tokio-postgres и sqlx

Библиотеки

tokio-postgres (низкоуровневый async)
sqlx (высокоуровневый async, compile-time checks)

Ключевые типы tokio-postgres

Client
Row
Transaction

Методы

tokio_postgres::connect()
execute(), query(), query_one()
transaction(), commit(), rollback()

use tokio_postgres::NoTls;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let (client, connection) =
        tokio_postgres::connect("host=localhost user=app_user password=secret dbname=app_db", NoTls).await?;
    tokio::spawn(async move { let _ = connection.await; });

    client.execute(
        "CREATE TABLE IF NOT EXISTS users(id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL)",
        &[],
    ).await?;
    client.execute("INSERT INTO users(name) VALUES ($1)", &[&"Anna"]).await?;

    for row in client.query("SELECT id, name FROM users", &[]).await? {
        let id: i64 = row.get(0);
        let name: String = row.get(1);
        println!("{id} {name}");
    }
    Ok(())
}

JavaScript — node-postgres (pg)

Библиотека

pg

Ключевые классы

Client
Pool
QueryResult

Методы

connect(), end()
query(text, params)
транзакции через BEGIN/COMMIT/ROLLBACK

import pg from "pg";
const { Client } = pg;

const client = new Client({
  host: "localhost",
  port: 5432,
  user: "app_user",
  password: "secret",
  database: "app_db",
});

await client.connect();
await client.query("CREATE TABLE IF NOT EXISTS users(id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL)");
await client.query("INSERT INTO users(name) VALUES ($1)", ["Anna"]);

const res = await client.query("SELECT id, name FROM users");
for (const row of res.rows) {
  console.log(row.id, row.name);
}

await client.query("UPDATE users SET name=$1 WHERE id=$2", ["Ann", 1]);
await client.query("DELETE FROM users WHERE id=$1", [1]);
await client.end();

Минимальный шаблон безопасной работы

Используйте параметризованные запросы ($1, $2, именованные параметры в ORM).
Включайте TLS для внешних соединений.
Ограничивайте права ролей по принципу minimum privilege.
Используйте миграции с откатом.
Мониторьте медленные запросы и планы выполнения.

SET statement_timeout = '5s';
SET lock_timeout = '3s';
SHOW transaction_isolation;

Когда PostgreSQL подходит и когда выбрать другой путь

PostgreSQL подходит:

многопользовательские серверные системы;
аналитические и транзакционные сценарии в одном кластере;
сложные типы данных и поиск;
строгие требования к целостности и правам доступа.

Другой путь выбирают:

для сверхпростого локального файла в приложении удобнее SQLite;
для экстремального горизонтального масштаба нужен отдельный архитектурный слой с шардингом;
для узкоспециализированного OLAP-хранилища используют колоночные СУБД.

См. также

Другие статьи этого же раздела в боковом меню (как на странице "О разделе").

Как работает PostgreSQL​

Подключение и запуск​

psql​

Строка подключения (URI)​

Базовая настройка​

Типы данных и синтаксис​

Возможности PostgreSQL​

Доступные и недоступные команды​

Практика SQL в PostgreSQL​

CRUD​

UPSERT​

Транзакция​

Возврат данных сразу после изменения​

Клиенты и инструменты​

API по языкам — подключение, CRUD, библиотеки, классы и функции​

C# — Npgsql​

Python — psycopg​

Java — JDBC PostgreSQL Driver​

C — libpq​

C++ — libpqxx​

Go — pgx или database/sql + pq​

PHP — PDO_PGSQL​

Kotlin — JDBC (PostgreSQL)​

Rust — tokio-postgres и sqlx​

JavaScript — node-postgres (pg)​

Минимальный шаблон безопасной работы​

Когда PostgreSQL подходит и когда выбрать другой путь​

См. также

📄️SQL - язык структурированных запросов

📄️Первые шаги с SQL

📄️Эволюция систем хранения данных

📄️Реляционная модель данных

📄️Нормализация данных

📄️Словарь данных и системные каталоги

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

📄️Оператор SELECT — синтаксис и стиль

📄️Подзапросы, EXISTS и IN

📄️Фильтрация и трёхзначная логика

📄️Блокировки и конкурентный доступ в PostgreSQL

📄️Практикум shop_data

Как работает PostgreSQL

Подключение и запуск

psql

Строка подключения (URI)

Базовая настройка

Типы данных и синтаксис

Возможности PostgreSQL

Доступные и недоступные команды

Практика SQL в PostgreSQL

CRUD

UPSERT

Транзакция

Возврат данных сразу после изменения

Клиенты и инструменты

API по языкам — подключение, CRUD, библиотеки, классы и функции

C# — Npgsql

Python — psycopg

Java — JDBC PostgreSQL Driver

C — libpq

C++ — libpqxx

Go — pgx или database/sql + pq

PHP — PDO_PGSQL

Kotlin — JDBC (PostgreSQL)

Rust — tokio-postgres и sqlx

JavaScript — node-postgres (pg)

Минимальный шаблон безопасной работы

Когда PostgreSQL подходит и когда выбрать другой путь