Your seed script inserts 10,000 user records. The uniqueness check passes locally. In CI — running Postgres 15 on a Linux container with a different locale — half your foreign-key lookups return zero rows, and three integration tests fail with cryptic NOT NULL constraint violations that have nothing to do with nulls. The real culprit is a collation mismatch: the same string, compared under two different collation rules, produces a different sort key, a different equality result, and a different index hit. Like timezone offsets corrupting date-range seeds, collation bugs are environment-sensitive and nearly invisible in application logs.

The problem compounds at seed time because that's when you're doing the most bulk string comparison: deduplication, lookup joins, parent-record resolution, and constraint validation all fire in sequence. A mismatch at any step silently skews your fixture state before a single test runs.

By the end of this article you'll know how to detect collation drift between environments, pin collation at the column and query level, and write seed pipelines that fail loudly instead of producing subtly wrong data.

What Collation Actually Controls in a Seeded Database

Collation governs three things: how strings are sorted, how they're compared for equality, and how they're matched by index scans. Most engineers think of it as a display concern — "accents and case sensitivity." At seed time it's a data-integrity concern. A UNIQUE index on an email column backed by en_US.UTF-8 will accept both Alice@example.com and alice@example.com as distinct values; the same column under und-x-icu with DETERMINISTIC = false will reject the second as a duplicate. Your seed either silently drops a row or raises a constraint error depending on which environment runs it.

In Postgres, collation is set at the database level (LC_COLLATE), can be overridden at the column level (COLLATE "C"), and can be further overridden per-expression (col COLLATE "en-US-x-icu"). MySQL/MariaDB separates character set from collation entirely — utf8mb4_unicode_ci vs. utf8mb4_bin behave differently on Turkish dotted-i comparisons, emoji ordering, and accent folding. SQLite defaults to BINARY collation unless you register a custom one at connection time. A seed pipeline that works on one engine often silently misbehaves on another, which matters when your test data management strategy spans multiple environments or database vendors.

Detecting and Pinning Collation Across Seed Pipelines

Start by making collation observable. Add a pre-seed assertion that reads the actual database and column collations and compares them against a manifest your team owns. In Postgres:

-- Check database-level collation
SELECT datname, datcollate, datctype
FROM pg_database
WHERE datname = current_database();

-- Check column-level collation for seeded tables
SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND collation_name IS NOT NULL
ORDER BY table_name, ordinal_position;

Run this in a pytest fixture before your seed loader fires. If the output doesn't match your collation_manifest.yaml, fail immediately — don't let a mismatched environment corrupt 10,000 rows and then wonder why lookups miss.

# collation_manifest.yaml
database:
  datcollate: "en_US.UTF-8"
  datctype:   "en_US.UTF-8"
columns:
  users.email:      "und-x-icu"
  users.username:   "C"
  products.sku:     "C"

import psycopg2, yaml, pytest

@pytest.fixture(scope="session", autouse=True)
def assert_collation(db_conn):
    manifest = yaml.safe_load(open("collation_manifest.yaml"))
    cur = db_conn.cursor()
    cur.execute(
        "SELECT datcollate FROM pg_database WHERE datname = current_database()"
    )
    actual_collate = cur.fetchone()[0]
    expected = manifest["database"]["datcollate"]
    assert actual_collate == expected, (
        f"Collation drift: expected {expected!r}, got {actual_collate!r}. "
        "Seed aborted — check your Docker base image locale."
    )

This fixture ran in a real project where switching from postgres:15-alpine (locale C) to postgres:15-bullseye (en_US.UTF-8) caused 340 seed-time deduplication failures that looked like FK violations. The assertion surfaced the root cause in 2 seconds instead of 40 minutes of log archaeology. The fix was pinning the Docker image and explicitly declaring column collations in migrations rather than inheriting the database default.

For columns where case-insensitive matching is intentional (email lookups, username searches), use ICU collations with explicit determinism settings rather than relying on LOWER() tricks:

ALTER TABLE users
  ALTER COLUMN email TYPE text
  COLLATE "und-x-icu";

-- For truly case-insensitive unique constraint in PG 15+:
CREATE UNIQUE INDEX users_email_ci_idx
  ON users (email COLLATE "und-x-icu");

When seeding via factory_boy or Faker, normalize string values before insert rather than trusting the database to reconcile them. A one-liner in your factory catches drift at the Python layer, where the error message is actually readable:

import factory
from faker import Faker

fake = Faker()

class UserFactory(factory.Factory):
    class Meta:
        model = dict

    email = factory.LazyFunction(lambda: fake.email().lower().strip())
    username = factory.LazyFunction(lambda: fake.user_name().lower().strip())

Normalizing at generation time also makes your seed data deterministic across locales — a property worth having when you're resolving parent records before child inserts and the lookup join is string-keyed.

Seed-Time Mistakes Even Senior Engineers Repeat

Inheriting collation from the database default. Migrations that create tables without explicit COLLATE clauses silently inherit whatever the database was initialized with. On a developer's Mac running Postgres via Homebrew that's often en_US.UTF-8; in a CI container built from alpine it's C. The schema looks identical in both environments, but string comparison semantics differ. The fix is codifying collation in every CREATE TABLE and enforcing it in migration review checklists — not relying on environment parity you don't actually have.

Using ILIKE or LOWER() as a collation substitute. Teams reach for ILIKE in seed-time lookup queries to avoid case sensitivity problems without understanding that ILIKE is locale-aware and still collation-dependent in ICU mode. Worse, it bypasses indexes. A seed loader that resolves 50,000 parent-record lookups via ILIKE instead of a properly collated index will be slow enough to time out in CI — one real project saw lookup time drop from 12 minutes to 9 seconds after replacing ILIKE with a collated unique index and exact-match lookups on pre-normalized strings.

Myths About Collation That Cause Silent Seed Corruption

"UTF-8 means the same collation everywhere." Character encoding and collation are orthogonal. Two databases can both be UTF-8 encoded and still sort and compare strings differently depending on their LC_COLLATE locale. utf8mb4_unicode_ci in MySQL and en_US.UTF-8 in Postgres are not equivalent, and neither behaves the same as utf8mb4_bin or C. Engineers who conflate encoding with collation write seed scripts that work in one environment and corrupt data silently in another. "Production data clones are safe to seed with because they already have the right collation." Even if the production collation is correct, cloning prod data introduces PII risk and encoding edge cases your test schema may not handle — a concern that intersects directly with safe PII masking practices.

"Collation only matters for non-ASCII characters." This one bites teams working in English-only domains. C collation sorts uppercase before lowercase (Z before a), which means a seed deduplication step that sorts and compares emails can produce different results than en_US.UTF-8 even for pure ASCII strings. Unique constraint checks, ON CONFLICT DO NOTHING logic, and ORDER BY-dependent seed sequences all behave differently. The safe assumption is: collation affects every string operation, regardless of character range.

Collation mismatches are fixable once you make them visible. Add a collation assertion fixture to your session setup, codify collation in every migration, and normalize strings at generation time in your factories. If you're generating bulk seed data with AI tooling or Faker, run schema-level validation on the output before insert — the same discipline that applies to validating AI-generated test data applies here. The Postgres docs on ICU collation support and the pg_collation catalog are the right next stop.

Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.