PostgreSQL

​

Overview

​

Supported Versions

• PostgreSQL 10+
• PostgreSQL 11+
• PostgreSQL 12+
• PostgreSQL 13+
• PostgreSQL 14+
• PostgreSQL 15+
• PostgreSQL 16+

​

Connection

​

Basic Connection

Host: localhost
Port: 5432
Database: postgres
Username: postgres
Password: your_password

​

Connection String Formats

postgresql://user:password@localhost:5432/database
postgres://user:password@localhost/database
psql://user:password@localhost:5432/database

​

SSL/TLS Configuration

• Disable: No SSL encryption
• Prefer: Try SSL first, fallback to non-SSL
• Require: Require SSL (don’t verify certificates)
• Verify-CA: Require SSL and verify certificate authority
• Verify-Full: Require SSL and verify certificate authority and hostname

// SSL with custom certificates
{
  ssl: true,
  sslConfig: {
    mode: 'verify-full',
    ca: fs.readFileSync('ca.pem'),
    cert: fs.readFileSync('client-cert.pem'),
    key: fs.readFileSync('client-key.pem')
  }
}

​

Features

​

Schemas

• Create, browse, and switch between schemas
• Default schema: public
• System schemas: pg_catalog, information_schema

​

Tables & Views

• Tables: Full DDL support with CREATE, ALTER, DROP
• Views: Create and manage standard views
• Materialized Views: Create, refresh, and drop materialized views
• Table Comments: Add descriptions to tables and columns

​

Data Types

​

Indexes

• B-tree (default)
• Hash
• GiST (Generalized Search Tree)
• GIN (Generalized Inverted Index)
• BRIN (Block Range Index)
• Unique and multi-column indexes
• Partial indexes with WHERE clauses

​

Constraints

• Primary keys (single and composite)
• Foreign keys with ON UPDATE/ON DELETE actions
• Unique constraints
• Check constraints
• NOT NULL constraints

​

Sequences

• Create and manage sequences
• Auto-increment columns using SERIAL/BIGSERIAL
• Custom sequence definitions with START, INCREMENT, MINVALUE, MAXVALUE
• CYCLE and CACHE options

​

Extensions

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "hstore";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

​

Stored Procedures & Functions

• View and execute functions and procedures
• Support for PL/pgSQL, SQL, and other languages
• Parameter modes: IN, OUT, INOUT
• Return types including SETOF and TABLE

​

Triggers

• BEFORE and AFTER triggers
• FOR EACH ROW and FOR EACH STATEMENT
• Trigger events: INSERT, UPDATE, DELETE, TRUNCATE

​

Users & Roles

• Create and manage database users
• Role attributes: SUPERUSER, CREATEDB, CREATEROLE, LOGIN
• Password management
• Connection limits and expiration dates

​

Enum Types

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

​

Advanced Features

​

Transactions

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

​

Query Streaming

​

Query Cancellation

​

Limitations

​

Docker Development

services:
  postgres:
    image: postgres:16-alpine
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: postgres
    volumes:
      - ./docker/postgres/init.sql:/docker-entrypoint-initdb.d/init.sql

• Tables with various data types
• Views and materialized views
• Functions and procedures
• Triggers and sequences
• Foreign key relationships
• Extensions (uuid-ossp, pg_trgm)

​

Best Practices

1. Use schemas to organize related tables
2. Enable SSL for production connections
3. Use connection pooling for better performance (handled automatically)
4. Set appropriate timeouts for long-running queries
5. Use EXPLAIN ANALYZE to optimize query performance