Working with SQL Data Types
1. Using Integer Types
| Type | Range | Bytes |
|---|---|---|
| SMALLINT | -32,768 to 32,767 | 2 |
| INTEGER / INT | ±2.1 billion | 4 |
| BIGINT | ±9.2 × 10¹⁸ | 8 |
| SERIAL / IDENTITY | Auto-increment INT | 4 / 8 |
| UNSIGNED (MySQL) | 0 to 2× positive range | same |
Note: Use BIGINT for primary keys in growing systems — INT overflow at 2.1B rows is a real outage scenario.
2. Using Decimal Types
| Type | Precision | Use |
|---|---|---|
| NUMERIC(p,s) / DECIMAL(p,s) | Exact, arbitrary | Money, accounting |
| REAL / FLOAT | ~6 digits, 4 bytes | Scientific, approximate |
| DOUBLE PRECISION | ~15 digits, 8 bytes | Scientific, higher precision |
| MONEY (Postgres) | Fixed 2 decimals | Currency (locale-dependent — prefer NUMERIC) |
Warning: Never use FLOAT/DOUBLE for currency — rounding errors compound. Use NUMERIC(p,s).
3. Using String Types
| Type | Description |
|---|---|
| CHAR(n) | Fixed-length, space-padded |
| VARCHAR(n) | Variable up to n |
| TEXT | Unlimited variable (Postgres same speed as VARCHAR) |
| CITEXT (Postgres) | Case-insensitive text |
| CLOB / LONGTEXT | Very large strings (MySQL, Oracle) |
4. Using Date and Time Types
| Type | Stores |
|---|---|
| DATE | Year-Month-Day |
| TIME [WITH TIME ZONE] | Time of day |
| TIMESTAMP | Date + time (no tz) |
| TIMESTAMPTZ / TIMESTAMP WITH TIME ZONE | UTC-stored, tz-aware |
| INTERVAL | Duration (e.g., '3 days 2 hours') |
Example: Time Zone-Safe Storage
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
starts_at TIMESTAMPTZ NOT NULL,
duration INTERVAL NOT NULL
);
INSERT INTO events(starts_at, duration)
VALUES ('2026-05-20 14:00 America/New_York', '90 minutes');5. Using Boolean Types
| DB | Type | Literals |
|---|---|---|
| Postgres | BOOLEAN | TRUE/FALSE, 't'/'f', 1/0 |
| MySQL | TINYINT(1) alias BOOLEAN | 0/1 |
| SQL Server | BIT | 0/1, NULL |
| Oracle | NUMBER(1) or CHAR(1) | No native boolean prior to 23ai |
6. Using Binary Types
| Type | Description |
|---|---|
| BYTEA (Postgres) | Variable-length byte array |
| BLOB / VARBINARY (MySQL) | Binary large objects |
| VARBINARY(MAX) (SQL Server) | Variable binary up to 2 GB |
| RAW / BLOB (Oracle) | Binary types |
Note: Store large binary (images, PDFs) in object storage (S3, GCS) and keep only references in the DB.
7. Using JSON and XML Types
| Type | Indexable | Notes |
|---|---|---|
| JSON (Postgres) | Limited | Stores raw text, preserves whitespace |
| JSONB (Postgres) | GIN, BTREE on expressions | Binary, faster queries, recommended |
| JSON (MySQL 8+) | Functional indexes | Binary internally |
| XML | XPath/XQuery | Legacy; rarely needed today |
Example: JSONB Query
CREATE TABLE products (id BIGSERIAL PK, data JSONB);
CREATE INDEX idx_data_brand ON products ((data->>'brand'));
SELECT id FROM products WHERE data @> '{"brand":"Acme","in_stock":true}';8. Using Array and Composite Types
| Feature | Postgres Syntax |
|---|---|
| Array column | tags TEXT[] |
| Array literal | ARRAY['a','b'] or '{a,b}' |
| Contains operator | tags @> ARRAY['sale'] |
| Composite type | CREATE TYPE addr AS (street TEXT, zip TEXT); |
| Unnest | SELECT UNNEST(tags) FROM products; |
9. Creating User-Defined Types
| Type | Example |
|---|---|
| ENUM | CREATE TYPE status AS ENUM ('open','closed'); |
| DOMAIN | CREATE DOMAIN email_t AS TEXT CHECK (VALUE ~ '@'); |
| COMPOSITE | CREATE TYPE point AS (x FLOAT, y FLOAT); |
| RANGE | CREATE TYPE pricerange AS RANGE (subtype = NUMERIC); |
10. Choosing Appropriate Data Types
| Data | Recommended |
|---|---|
| Currency | NUMERIC(15,2) or smallest_unit BIGINT (cents) |
| Identifier | BIGSERIAL or UUID v7 |
| Timestamp | TIMESTAMPTZ (always UTC) |
| CITEXT or VARCHAR(254) | |
| Boolean flag | BOOLEAN |
| Enum-like | ENUM type or VARCHAR + CHECK |
| Flexible attrs | JSONB |
| Geographic | PostGIS GEOMETRY/GEOGRAPHY |