Values and Types in SQL

SQL engines provide a set of scalar and compound types. Exact names differ by engine; concepts are similar.

Common scalar types

• Integers: SMALLINT, INT, BIGINT
• Real numbers: DECIMAL(p,s), NUMERIC, FLOAT
• Text: TEXT, VARCHAR(n), CHAR(n)
• Dates/times: DATE, TIME, TIMESTAMP [WITH TIME ZONE]
• Boolean: BOOLEAN
• JSON: JSON, JSONB (Postgres), JSON (MySQL)
• Binary: BYTEA (Postgres), BLOB (SQLite/MySQL)

NULL semantics

NULL means unknown/missing; comparisons with NULL yield UNKNOWN.

WHERE col = NULL      -- never true
WHERE col IS NULL     -- correct null check
WHERE col IS NOT NULL

Literals and casting

SELECT 42::INT, CAST('2024-01-01' AS DATE);

Use engine-appropriate casting (:: in Postgres, CAST() portable).

Constraints and defaults

CREATE TABLE accounts (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Collation and case sensitivity

String comparisons depend on collation. Normalize or specify collations explicitly for predictable sorting and filtering.

Summary

• Choose appropriate types, prefer exact types over generic TEXT/INT
• Handle NULL with IS [NOT] NULL; use explicit casts when needed