Data Types & DDL

DDL — Data Definition Language — is the part of SQL that creates and changes schema. This is also where SQLite and PostgreSQL diverge the most, because they disagree fundamentally about what a “type” is.

CREATE TABLE

The basic form is identical in both engines:

CREATE TABLE authors (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  birth_year INTEGER,
  country    TEXT DEFAULT 'unknown'
);

Each column is name type [constraints...]. Constraints written on a column are column constraints; constraints written on their own line apply to the table and can span multiple columns.

CREATE TABLE order_items (
  order_id INTEGER NOT NULL,
  book_id  INTEGER NOT NULL,
  qty      INTEGER NOT NULL,
  PRIMARY KEY (order_id, book_id)        -- table constraint, composite key
);

Guard against re-running scripts:

CREATE TABLE IF NOT EXISTS authors ( ... );

Note

PostgreSQL folds unquoted identifiers to lowercase. CREATE TABLE Authors creates a table named authors; CREATE TABLE "Authors" creates one you must always write as "Authors". SQLite preserves the case you typed but compares identifiers case-insensitively. Use snake_case everywhere and the problem disappears.

Types in SQLite: dynamic typing

SQLite does not enforce column types by default. A column’s declared type is a hint, and any value can go in any column. There are only five storage classes — the actual runtime types of values:

Storage class	Holds
NULL	The null value
INTEGER	Signed integer, 1–8 bytes depending on magnitude
REAL	8-byte IEEE float
TEXT	String, in the database encoding (UTF-8 by default)
BLOB	Bytes, stored exactly as given

Notice what’s missing: no boolean, no date, no time, no decimal, no UUID. Booleans are stored as 0/1 integers. Dates are stored as TEXT ('2026-08-09 12:00:00'), INTEGER (Unix seconds), or REAL (Julian day).

Type affinity

Every column gets an affinity — a preferred storage class SQLite tries to convert to on insert. The affinity is derived from the declared type name by these rules, in order:

Declared type contains	Affinity
INT	INTEGER
CHAR, CLOB, or TEXT	TEXT
BLOB, or type omitted	BLOB (i.e. none)
REAL, FLOA, or DOUB	REAL
anything else	NUMERIC

So VARCHAR(255) → TEXT affinity, BIGINT → INTEGER, DOUBLE PRECISION → REAL, DECIMAL(10,2) and BOOLEAN and DATETIME → NUMERIC. This is why you can paste a PostgreSQL schema into SQLite and it “works”: unknown type names silently get NUMERIC affinity.

Affinity converts losslessly or not at all:

CREATE TABLE t (a INTEGER, b TEXT, c BLOB);
INSERT INTO t VALUES ('42', 42, 42);
SELECT typeof(a), typeof(b), typeof(c) FROM t;
-- => integer|text|integer

'42' went into an INTEGER-affinity column and became the integer 42. 42 went into a TEXT column and became '42'. The BLOB column has no affinity, so 42 stayed an integer.

INSERT INTO t (a) VALUES ('hello');
SELECT typeof(a) FROM t WHERE a = 'hello';
-- => text

'hello' cannot become an integer, so it is stored as text — in a column declared INTEGER. No error.

STRICT tables (SQLite 3.37+)

Since 3.37.0 you can opt into real type checking:

CREATE TABLE authors (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  birth_year INTEGER
) STRICT;

In a STRICT table every column must be declared as one of INT, INTEGER, REAL, TEXT, BLOB, or ANY, and inserting a value of the wrong type raises an error instead of coercing.

INSERT INTO authors (id, name) VALUES (1, 42);
-- Error: cannot store INTEGER value in TEXT column authors.name

Tip

Use STRICT on every new SQLite table. It costs nothing, catches real bugs, and makes an eventual move to PostgreSQL far less painful.

Types in PostgreSQL: static and rich

PostgreSQL types are enforced at insert time and drive operator and function resolution. The ones you actually need:

Type	Notes
smallint / integer / bigint	2/4/8-byte signed integers. integer is the default choice.
numeric(p, s)	Exact decimal, arbitrary precision. Use this for money.
real / double precision	4/8-byte floats. Fast, inexact — never for money.
text	Unlimited-length string.
varchar(n) / char(n)	Length-limited. varchar(n) is text plus a length check.
boolean	True true/false/null. Accepts 't', 'yes', 1 on input.
date, time, timestamp	Calendar/clock values without a zone.
timestamptz	Instant in time. The one you almost always want.
interval	A duration: interval '3 days'.
uuid	16-byte UUID, not a string.
json / jsonb	JSON text vs parsed binary JSON. Use jsonb.
bytea	Binary blob.
arrays	Any type followed by []: text[], integer[].
enums	User-defined via CREATE TYPE.

CREATE TABLE books (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id  integer NOT NULL REFERENCES authors(id),
  title      text NOT NULL,
  price      numeric(6, 2) NOT NULL CHECK (price >= 0),
  tags       text[] NOT NULL DEFAULT '{}',
  metadata   jsonb NOT NULL DEFAULT '{}'::jsonb,
  in_print   boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

timestamptz vs timestamp

timestamp stores wall-clock digits with no zone — 2026-08-09 14:00:00 is ambiguous, it could be any of 30 instants worldwide. timestamptz stores an absolute instant (internally UTC) and converts to the session’s TimeZone on output.

SET TimeZone = 'UTC';
SELECT now();                 -- 2026-08-09 12:00:00+00
SET TimeZone = 'Europe/Paris';
SELECT now();                 -- 2026-08-09 14:00:00+02  (same instant)

Caution

Default to timestamptz for anything representing “when something happened”. Use plain timestamp or date only for zone-free concepts like a birthday or a store’s opening hour.

Auto-incrementing keys

Three spellings, one recommendation:

-- Legacy, still very common: creates a sequence behind the scenes
id serial PRIMARY KEY
id bigserial PRIMARY KEY

-- SQL-standard, PostgreSQL 10+, preferred
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
id bigint  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

GENERATED ALWAYS rejects a user-supplied value unless you write OVERRIDING SYSTEM VALUE. GENERATED BY DEFAULT lets you supply one — convenient for data loads, but then the sequence can fall behind and later inserts collide.

The SQLite equivalent is just INTEGER PRIMARY KEY, which aliases the hidden rowid and auto-assigns “one more than the current maximum”. Adding AUTOINCREMENT additionally guarantees IDs are never reused after deletion, at the cost of an extra sqlite_sequence table lookup per insert.

-- SQLite
CREATE TABLE books (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL);

Caution

INTEGER PRIMARY KEY auto-assigns only when the type is written exactly as INTEGER. INT PRIMARY KEY or BIGINT PRIMARY KEY is not a rowid alias and will not auto-fill.

Enums and arrays (PostgreSQL only)

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');

CREATE TABLE orders (
  id     integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status order_status NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (status) VALUES ('shipped');
INSERT INTO orders (status) VALUES ('lost');
-- ERROR: invalid input value for enum order_status: "lost"

Enum values sort in declaration order, which is often exactly what you want. Adding a value is easy (ALTER TYPE order_status ADD VALUE 'refunded';); removing or reordering one is not. If the value set changes often, a lookup table with a foreign key is the better design.

Arrays:

INSERT INTO books (title, tags) VALUES ('Kindred', ARRAY['sf', 'classic']);
SELECT title FROM books WHERE 'sf' = ANY(tags);
SELECT title, array_length(tags, 1) FROM books;

SQLite has neither. The portable pattern is a join table, which is usually the better model anyway.

Constraints

Constraints are enforced by the database, so they hold no matter which application, script, or human writes the data. That is their entire point — application-level validation is bypassable, a CHECK constraint is not.

PRIMARY KEY

Uniquely identifies a row. Implies UNIQUE, and in PostgreSQL implies NOT NULL.

PRIMARY KEY (order_id, book_id)   -- composite

Caution

A long-standing SQLite quirk: PRIMARY KEY columns are not automatically NOT NULL (except a rowid-alias INTEGER PRIMARY KEY), so nulls can sneak into a primary key. Write NOT NULL explicitly, or use a STRICT table — STRICT tables enforce NOT NULL on primary keys.

FOREIGN KEY and ON DELETE

A foreign key says “this value must exist in that table’s key column”.

CREATE TABLE books (
  id        INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
  title     TEXT NOT NULL
);

The referential actions, both engines:

Action	On delete of the parent row
NO ACTION (default)	Error if children exist
RESTRICT	Error, checked immediately (cannot be deferred)
CASCADE	Delete the children too
SET NULL	Null out the child’s FK column
SET DEFAULT	Set the child’s FK column to its default

ON UPDATE takes the same actions and fires when the parent key value changes.

Danger

SQLite does not enforce foreign keys unless you turn them on, per connection, every connection:

PRAGMA foreign_keys = ON;

The default is OFF for backwards compatibility. If you have never run this, your REFERENCES clauses are decorative and you may already have orphaned rows. Most ORMs and drivers set it for you — verify with PRAGMA foreign_keys; (returns 1 when on).

PostgreSQL enforces foreign keys always. It also supports deferring the check to commit time:

ALTER TABLE books
  ADD CONSTRAINT books_author_fk FOREIGN KEY (author_id)
  REFERENCES authors(id) DEFERRABLE INITIALLY DEFERRED;

That lets you insert mutually-referencing rows inside one transaction. SQLite supports DEFERRABLE INITIALLY DEFERRED too, but only when foreign keys are enabled.

UNIQUE, NOT NULL, CHECK, DEFAULT

CREATE TABLE customers (
  id      INTEGER PRIMARY KEY,
  email   TEXT NOT NULL UNIQUE,
  age     INTEGER CHECK (age IS NULL OR age >= 13),
  credit  NUMERIC(8,2) NOT NULL DEFAULT 0,
  UNIQUE (email)                       -- table-level form of the same thing
);

• UNIQUE creates a unique index. In both engines, multiple NULLs are allowed in a unique column — NULL is not equal to anything, including itself. This is standard behaviour and surprises nearly everyone.
• CHECK evaluates a boolean expression per row; the row is rejected if it evaluates to false. If it evaluates to NULL (unknown), the row is accepted — hence the age IS NULL OR ... above is redundant but explicit.
• DEFAULT supplies a value when the column is omitted from INSERT. It does not fire when you explicitly insert NULL.

CREATE TABLE events (
  id       INTEGER PRIMARY KEY,
  kind     TEXT NOT NULL CHECK (kind IN ('view', 'click', 'purchase')),
  amount   NUMERIC(8,2) CHECK (amount > 0)
);

CHECK (kind IN (...)) is the portable substitute for a PostgreSQL enum, and works in SQLite too.

Note

PostgreSQL 12+ supports generated columns, computed from other columns and stored:

ALTER TABLE books ADD COLUMN price_with_tax numeric(8,2)
  GENERATED ALWAYS AS (price * 1.2) STORED;

SQLite 3.31+ supports the same syntax and additionally VIRTUAL (computed on read, the default). PostgreSQL added VIRTUAL generated columns in 18; on 12–17 only STORED is available.

ALTER TABLE

PostgreSQL’s ALTER TABLE is close to complete:

ALTER TABLE books ADD COLUMN isbn text;
ALTER TABLE books DROP COLUMN isbn;
ALTER TABLE books RENAME COLUMN title TO name;
ALTER TABLE books RENAME TO publications;

ALTER TABLE books ALTER COLUMN price TYPE numeric(10,2);
ALTER TABLE books ALTER COLUMN price SET NOT NULL;
ALTER TABLE books ALTER COLUMN price SET DEFAULT 0;
ALTER TABLE books ALTER COLUMN price DROP DEFAULT;

ALTER TABLE books ADD CONSTRAINT price_positive CHECK (price >= 0);
ALTER TABLE books DROP CONSTRAINT price_positive;

When a type change isn’t automatically castable, supply the conversion:

ALTER TABLE books ALTER COLUMN year TYPE integer USING year::integer;

Caution

Most PostgreSQL ALTER TABLE forms take an ACCESS EXCLUSIVE lock on the table, blocking reads and writes for the duration. ADD COLUMN with a constant DEFAULT is fast (metadata-only) since PostgreSQL 11, but ALTER COLUMN TYPE rewrites the whole table. On a large production table, plan for it.

SQLite’s ALTER TABLE is deliberately minimal:

Operation	SQLite support
ADD COLUMN	Yes, always
RENAME TO	Yes, always
RENAME COLUMN	3.25+
DROP COLUMN	3.35+, with restrictions
Change a column’s type, default, or nullability	Not supported
Add or drop a constraint	Not supported

ADD COLUMN in SQLite also refuses PRIMARY KEY, UNIQUE, a non-constant DEFAULT (like CURRENT_TIMESTAMP), and NOT NULL without a non-null default.

For anything else, use the 12-step rebuild — the official recipe, simplified:

PRAGMA foreign_keys = OFF;
BEGIN;
CREATE TABLE books_new (
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES authors(id),
  title TEXT NOT NULL,
  price NUMERIC NOT NULL DEFAULT 0     -- the change we wanted
) STRICT;
INSERT INTO books_new (id, author_id, title, price)
  SELECT id, author_id, title, COALESCE(price, 0) FROM books;
DROP TABLE books;
ALTER TABLE books_new RENAME TO books;
-- recreate indexes, triggers and views here
COMMIT;
PRAGMA foreign_key_check;
PRAGMA foreign_keys = ON;

Do the whole thing inside a transaction so a failure leaves the old table intact.

DROP

DROP TABLE books;
DROP TABLE IF EXISTS books;

PostgreSQL additionally offers dependency handling and truncation:

DROP TABLE authors CASCADE;   -- also drops FKs and views that depend on it
TRUNCATE TABLE books;         -- delete all rows, fast, reclaims space
TRUNCATE books RESTART IDENTITY CASCADE;

SQLite has neither CASCADE on DROP TABLE nor TRUNCATE. Use DELETE FROM books; — SQLite optimises a DELETE with no WHERE into a fast table truncation internally.

Note

DROP TABLE and CREATE TABLE are transactional in both engines: run them inside BEGIN ... ROLLBACK and the schema change is undone. That is unusual among databases (MySQL, for instance, commits implicitly on DDL) and makes migrations much safer.

Inspecting a schema

# SQLite
sqlite> .tables
sqlite> .schema books
sqlite> PRAGMA table_info(books);

# PostgreSQL
bookstore=# \dt
bookstore=# \d books
bookstore=# \d+ books

Key points

• SQLite has five storage classes and dynamic typing; declared types only set an affinity. Use STRICT tables to get real enforcement.
• PostgreSQL has a rich, enforced type system — reach for numeric for money, timestamptz for instants, jsonb for documents, text over varchar(n).
• INTEGER PRIMARY KEY auto-numbers in SQLite; PostgreSQL needs GENERATED ALWAYS AS IDENTITY (or legacy serial).
• SQLite ignores foreign keys unless PRAGMA foreign_keys = ON on every connection.
• UNIQUE allows multiple NULLs in both engines; a CHECK that evaluates to NULL passes.
• SQLite’s ALTER TABLE only adds/renames/drops columns — everything else needs a table rebuild.
• DDL is transactional in both engines; wrap migrations in BEGIN/COMMIT.