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);