Israeli Postgres Toolkit

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

Assumes PostgreSQL 13+ (the patterns use gen_random_uuid() from core, non-deterministic ICU collations need 12+, and generated columns need 12+). Current stable is PostgreSQL 18.

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). A non-deterministic collation does support UNIQUE constraints and btree indexes (verified on PostgreSQL 17), but it cannot be used with LIKE/pattern matching, so keep a deterministic column or expression around for prefix search.
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). For nikud-insensitive Hebrew matching use the strip_nikud() function shown below; unaccent only strips Latin diacritics, not Hebrew nikud.
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