Processed in your browser. Your data never leaves your device.

SQLite vs PostgreSQL — Key Differences

SQLite and PostgreSQL are both powerful SQL databases, but they serve different purposes. SQLite is an embedded, serverless, zero-config database perfect for prototyping and learning. PostgreSQL is a full client-server RDBMS for production workloads. Here's what's different and what transfers between them.

Samples:

Loading SQLite engine…

SQL Editor

SQLite dialect • Multiple statements supported508 chars

Results

Run a query to see results

SQLite Quick Reference

Data Definition

CREATE TABLE t (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  val REAL DEFAULT 0
);
ALTER TABLE t ADD col TEXT;
DROP TABLE IF EXISTS t;

Queries

SELECT * FROM t WHERE x > 5;
SELECT a, COUNT(*) FROM t
  GROUP BY a HAVING COUNT(*) > 1;
SELECT * FROM t1
  JOIN t2 ON t1.id = t2.fk;

Data Manipulation

INSERT INTO t (a, b)
  VALUES (1, 'x'), (2, 'y');
UPDATE t SET a = 10 WHERE id = 1;
DELETE FROM t WHERE a IS NULL;

Functions

COUNT, SUM, AVG, MIN, MAX
LENGTH, UPPER, LOWER, TRIM
SUBSTR, REPLACE, INSTR
COALESCE, NULLIF, IIF
date, time, datetime, strftime

Window Functions

ROW_NUMBER() OVER (
  PARTITION BY col
  ORDER BY col2
)
RANK, DENSE_RANK, NTILE
LAG, LEAD, FIRST_VALUE

CTEs & Subqueries

WITH cte AS (
  SELECT a, COUNT(*) AS cnt
  FROM t GROUP BY a
)
SELECT * FROM cte
  WHERE cnt > 1;

How It Works

In-Browser SQLite

This playground runs a complete SQLite database engine in your browser using WebAssembly. No server required — your queries and data never leave your device.

Full SQL Support

Supports CREATE, INSERT, UPDATE, DELETE, SELECT with JOINs, subqueries, CTEs, window functions, aggregations, indexes, views, triggers, and more.

Multiple Statements

Run multiple SQL statements in sequence separated by semicolons. Each statement's results display separately with execution timing.

Schema Inspector

Click "Schema" to view all tables in your database with their CREATE TABLE definitions. Useful for checking column names and types.

Powered by sql.js (SQLite compiled to WebAssembly). Your SQL and data are processed entirely in your browser — nothing is sent to any server. Database resets when you refresh the page.

Type System Differences

PostgreSQL enforces strict column types — inserting a string into an INTEGER column fails. SQLite uses dynamic typing with type affinities: any column can store any type. SQLite's INTEGER PRIMARY KEY is special (auto-incrementing alias for rowid). PostgreSQL has rich types: JSONB, UUID, ARRAY, INET, INTERVAL, ENUM, composite types. SQLite stores everything as INTEGER, REAL, TEXT, BLOB, or NULL.

Syntax That Works in Both

Core SQL is identical: SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, JOINs, GROUP BY, HAVING, ORDER BY, LIMIT, subqueries, CTEs (WITH), and window functions all work in both. CASE expressions, COALESCE, NULLIF, CAST, and common string functions (UPPER, LOWER, LENGTH, SUBSTR, REPLACE) are compatible. If you learn SQL with SQLite, 90% transfers directly to PostgreSQL.

-- Works in both SQLite and PostgreSQL
WITH ranked AS (
  SELECT name, salary,
    RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees
)
SELECT * FROM ranked WHERE r <= 5;

PostgreSQL-Only Features

PostgreSQL supports features SQLite lacks: FULL OUTER JOIN (SQLite added in 3.39.0), LATERAL joins, RETURNING clause on INSERT/UPDATE/DELETE, ARRAY types and operators, JSONB with indexing and operators (@>, ->, #>>), ENUM types, table inheritance, materialized views, stored procedures (PL/pgSQL), and advanced indexing (GIN, GiST, BRIN). For production applications, PostgreSQL's ACID compliance, concurrent connections, and extensions (PostGIS, pg_trgm, pgvector) are critical.

When to Use SQLite

SQLite excels for: local development and prototyping, embedded databases in mobile/desktop apps, learning and practicing SQL, unit testing (in-memory databases), CLI tools, and read-heavy workloads under moderate traffic. SQLite is the most deployed database engine in the world — it's in every smartphone, browser, and operating system. For web applications with concurrent writes, use PostgreSQL or MySQL instead.

Frequently Asked Questions

Can I practice PostgreSQL queries in this playground?

This playground runs SQLite, but most standard SQL is identical between SQLite and PostgreSQL. JOINs, CTEs, window functions, aggregations, and subqueries work the same way. PostgreSQL-specific features like JSONB operators, ARRAY types, RETURNING clause, and PL/pgSQL stored procedures are not available in SQLite.

Is SQLite good for learning SQL?

Yes — SQLite is excellent for learning SQL. It supports all core SQL features (JOINs, CTEs, window functions, triggers, views, indexes) and runs anywhere without installation. The vast majority of SQL syntax you learn with SQLite transfers directly to PostgreSQL, MySQL, and SQL Server.

What are the main limitations of SQLite?

SQLite limitations vs PostgreSQL: no concurrent write access (single-writer), no RIGHT JOIN/FULL OUTER JOIN before 3.39.0, no ALTER TABLE DROP COLUMN before 3.35.0, no stored procedures, no GRANT/REVOKE permissions, limited ALTER TABLE support, no native JSONB indexing, and dynamic typing instead of strict type enforcement.

Related Inspect Tools

LLM

LLM Token Counter

Count tokens and estimate API costs for GPT-4o, Claude, Gemini, and other LLMs with BPE tokenization

AI Model Comparison

Compare 23 AI models (Claude 4.6, GPT-4.1, Gemini 3) + 5 coding IDEs (Cursor, Copilot, Windsurf, Kiro, Antigravity) — pricing, context windows, capabilities

{ }

JSON Visualizer

Visualize JSON as an interactive tree — collapsible nodes, search, path copy, depth controls, and data statistics

GDV

Git Diff Viewer

Paste unified diff output from git diff and view it with syntax highlighting, line numbers, and side-by-side or inline display