israeli-postgres-toolkit

Israeli Postgres Toolkit

Best practices, patterns, and scripts for building PostgreSQL databases tailored to Israeli applications. Covers Hebrew text handling, NIS currency, Israeli timezones, Supabase integration, and common Israeli data types.

Instructions

Follow this workflow when setting up or reviewing a PostgreSQL database for an Israeli app:

1. Verify encoding and timezone first. Run SHOW server_encoding; (must be UTF8, never SQL_ASCII or LATIN1) and SHOW timezone;. Set the database timezone with ALTER DATABASE your_db SET timezone = 'Asia/Jerusalem';. Getting these wrong corrupts Hebrew and offsets every timestamp, and fixing it later means a data migration.
2. Pick the collation strategy. Decide per column whether you need Hebrew display ordering (non-deterministic ICU collation he-IL-x-icu) or uniqueness/btree indexing (deterministic collation). You usually need both, on different columns or via separate indexes, because a non-deterministic collation cannot back a UNIQUE constraint or a plain btree index.
3. Choose the search approach. For exact and prefix matching use btree. For fuzzy/typo-tolerant Hebrew search use pg_trgm. For multi-field ranked search use full-text search with the simple configuration (see "Full-Text Search with Hebrew" below). Combine unaccent when you need nikud-insensitive matching.
4. Apply Israeli data-type constraints. Use the CHECK constraints and helper functions from scripts/israeli-data-types.sql (teudat zehut, phone, postal code, business number, IBAN) and call validate_teudat_zehut() for the ID check digit rather than reimplementing it in application code.

Hebrew Text Indexing

ICU Collation for Hebrew

PostgreSQL supports ICU collations for proper Hebrew text sorting. Always create a Hebrew collation for columns that store Hebrew text:

-- Create Hebrew ICU collation
CREATE COLLATION IF NOT EXISTS hebrew_icu (
  provider = icu,
  locale = 'he-IL-x-icu',
  deterministic = false
);

-- Use on columns
CREATE TABLE products (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name_he text COLLATE hebrew_icu NOT NULL,
  name_en text NOT NULL
);

-- Or apply in queries
SELECT * FROM products ORDER BY name_he COLLATE hebrew_icu;