Introduction to PostgreSQL

PostgreSQL is a powerful, open-source object-relational database management system (ORDBMS). It has earned a strong reputation for reliability, feature robustness, and performance. PostgreSQL supports advanced data types including JSON, arrays, and custom types.

PostgreSQL is used by companies like Apple, Twitch, Instagram, and Reddit.

Example

-- PostgreSQL basics
-- Connect: psql -U postgres

-- Create database
CREATE DATABASE myapp;
\c myapp  -- connect to database

-- Create table with PostgreSQL-specific features
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(255) UNIQUE NOT NULL,
    age        INTEGER CHECK (age >= 0 AND age <= 150),
    tags       TEXT[],           -- arrays!
    metadata   JSONB,            -- JSON!
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert with RETURNING
INSERT INTO users (name, email, tags)
VALUES ('Alice', 'alice@example.com', ARRAY['admin', 'user'])
RETURNING id, name, created_at;

-- Query JSON data
SELECT name, metadata->>'city' AS city
FROM users
WHERE metadata @> '{"country": "USA"}'::jsonb;

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned