PostgreSQL Query Editor

Learn basic PostgreSQL syntax and operations:

-- Basic SELECT query
SELECT name, email, created_at
FROM users
WHERE active = true
ORDER BY created_at DESC
LIMIT 10;

-- INSERT data
INSERT INTO users (name, email, password_hash)
VALUES ('John Doe', 'john@example.com', 'hashed_password');

-- UPDATE records
UPDATE users
SET last_login = NOW()
WHERE email = 'john@example.com';

-- DELETE records
DELETE FROM users
WHERE active = false AND created_at < NOW() - INTERVAL '1 year';

Our editor provides syntax highlighting and intelligent code completion.

Learn table creation and schema management:

-- Create table with constraints
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    active BOOLEAN DEFAULT true
);

-- Add indexes for performance
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

-- Add foreign key relationships
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Alter table structure
ALTER TABLE users ADD COLUMN profile_image_url VARCHAR(500);
ALTER TABLE posts ADD CONSTRAINT check_title_length CHECK (LENGTH(title) > 0);

Practice database design with proper constraints and relationships.

Master different types of JOIN operations:

-- INNER JOIN - get users with posts
SELECT u.username, u.email, p.title, p.created_at
FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE p.published_at IS NOT NULL
ORDER BY p.created_at DESC;

-- LEFT JOIN - get all users with their post count
SELECT u.username, u.email, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username, u.email
ORDER BY post_count DESC;

-- Multiple JOINs with aggregation
SELECT 
    u.username,
    COUNT(DISTINCT p.id) as post_count,
    COUNT(DISTINCT c.id) as comment_count,
    MAX(p.created_at) as latest_post
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
LEFT JOIN comments c ON p.id = c.post_id
GROUP BY u.id, u.username
HAVING COUNT(DISTINCT p.id) > 0;

Understanding JOINs is essential for relational database queries.

Explore powerful PostgreSQL-specific features:

-- Common Table Expressions (CTEs)
WITH user_stats AS (
    SELECT 
        user_id,
        COUNT(*) as post_count,
        AVG(LENGTH(content)) as avg_content_length
    FROM posts
    GROUP BY user_id
),
top_users AS (
    SELECT user_id, post_count
    FROM user_stats
    WHERE post_count >= 5
)
SELECT u.username, us.post_count, us.avg_content_length
FROM users u
JOIN user_stats us ON u.id = us.user_id
JOIN top_users tu ON u.id = tu.user_id
ORDER BY us.post_count DESC;

-- Window Functions
SELECT 
    username,
    title,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as post_rank,
    COUNT(*) OVER (PARTITION BY user_id) as user_total_posts,
    LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) as prev_post_date
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.published_at IS NOT NULL;

-- JSON Operations
SELECT 
    id,
    metadata->>'category' as category,
    metadata->'tags' as tags,
    jsonb_array_length(metadata->'tags') as tag_count
FROM posts
WHERE metadata ? 'featured'
AND (metadata->>'featured')::boolean = true;

These advanced features make PostgreSQL incredibly powerful.

Learn performance optimization techniques:

-- Create indexes for better performance
CREATE INDEX CONCURRENTLY idx_posts_user_published 
ON posts(user_id, published_at) 
WHERE published_at IS NOT NULL;

-- Partial index for specific conditions
CREATE INDEX idx_active_users ON users(created_at) WHERE active = true;

-- Composite index for multiple columns
CREATE INDEX idx_posts_search ON posts(title, content) USING gin(to_tsvector('english', title || ' ' || content));

-- Use EXPLAIN to analyze query performance
EXPLAIN (ANALYZE, BUFFERS) 
SELECT u.username, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.created_at > NOW() - INTERVAL '1 month'
GROUP BY u.id, u.username
ORDER BY post_count DESC
LIMIT 10;

-- Efficient pagination with cursors
SELECT id, title, created_at
FROM posts
WHERE created_at < '2023-01-01'::timestamp
ORDER BY created_at DESC
LIMIT 20;

-- Use EXISTS instead of IN for better performance
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM posts p 
    WHERE p.user_id = u.id 
    AND p.published_at > NOW() - INTERVAL '1 week'
);

Always analyze query plans and create appropriate indexes.

Explore PostgreSQL's rich data types and built-in functions:

-- Working with dates and times
SELECT 
    NOW() as current_timestamp,
    CURRENT_DATE as today,
    EXTRACT(YEAR FROM created_at) as year_created,
    AGE(created_at) as account_age,
    DATE_TRUNC('month', created_at) as month_created
FROM users
WHERE created_at > NOW() - INTERVAL '1 year';

-- String operations
SELECT 
    username,
    UPPER(username) as username_upper,
    LENGTH(username) as username_length,
    SUBSTRING(email FROM '@(.*)') as domain,
    REGEXP_REPLACE(title, '[^a-zA-Z0-9 ]', '', 'g') as clean_title
FROM users u
JOIN posts p ON u.id = p.user_id;

-- Array operations
SELECT 
    title,
    tags,
    array_length(tags, 1) as tag_count,
    'postgresql' = ANY(tags) as has_postgresql_tag,
    array_to_string(tags, ', ') as tags_string
FROM posts
WHERE tags IS NOT NULL
AND array_length(tags, 1) > 0;

-- Conditional logic
SELECT 
    username,
    CASE 
        WHEN created_at > NOW() - INTERVAL '1 month' THEN 'New'
        WHEN created_at > NOW() - INTERVAL '1 year' THEN 'Regular'
        ELSE 'Veteran'
    END as user_type,
    COALESCE(profile_image_url, '/default-avatar.png') as avatar
FROM users;

PostgreSQL offers extensive built-in functions for data manipulation.

Our PostgreSQL editor runs PostgreSQL 16+ via PGlite with specific constraints:

Version & Environment:

• PostgreSQL 16+ via PGlite (single-user mode)
• Embedded WASM database running in browser
• Memory limit: ~1GB database size

Key Limitations:

• No client-server architecture (embedded only)
• No connections/sessions (single connection)
• No file-based operations or COPY commands
• Extensions: Most extensions unavailable (no uuid-ossp, pgcrypto, PostGIS)
• Full-text search: No text search configurations (use LIKE patterns instead)
• Functions: No uuid_generate_v4(), crypt(), gen_salt(), to_tsvector()
• No background processes or autovacuum
• No replication or clustering features
• No custom user-defined functions or procedures

Available Features:

-- Core PostgreSQL features work perfectly:
SELECT, INSERT, UPDATE, DELETE, CTEs, Window Functions
JSONB operations, Arrays, Recursive queries
Advanced aggregation (FILTER, GROUPING SETS)
Indexes (B-tree, GIN for JSONB/Arrays)
Constraints, Foreign Keys, Check Constraints

-- Alternative approaches for missing features:
SERIAL PRIMARY KEY -- instead of UUID
md5(random()::text) -- instead of uuid_generate_v4()
LIKE patterns -- instead of full-text search

Perfect for learning SQL, database design, query optimization, and most PostgreSQL features!

Explore these official resources and learning materials:

Official Documentation:

• PostgreSQL 16 Documentation - Complete reference
• PostgreSQL Tutorial - Official getting started guide
• PostgreSQL SQL Reference - SQL command reference
• PGlite Documentation - WASM PostgreSQL used in this tool

Learning Resources:

• Postgres Guide - Comprehensive PostgreSQL guide
• PostgreSQL Performance Tips - Optimization techniques
• PostgreSQL Best Practices - What not to do
• SQL Style Guide - Writing clean SQL code

Academic Resources:

• PostgreSQL research papers on JSONB indexing and performance
• Database design and normalization theory

These resources will help you master PostgreSQL development, performance tuning, and best practices!