LLM Best Practices

citext

Last updated 1 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

citext (CREATE EXTENSION citext) keeps the original case but compares as if lower() were applied to both sides. Use it for emails, usernames, and slugs so Bob@x.com and bob@x.com collide under a plain unique constraint, with no lower(email) in queries and no functional index.

Rules

  • Index it with an ordinary B-tree and query WHERE email = $1. Equality, LIKE, ILIKE, and ORDER BY are all case-insensitive.
  • Folding follows the database LC_CTYPE and is not true Unicode case folding. The PostgreSQL docs recommend nondeterministic ICU collations instead; PostgreSQL 18 added LIKE support for them, which was a common reason to pick citext.
  • Do not use it where case is meaningful (tokens, code identifiers).
CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE users (id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY, email citext NOT NULL UNIQUE);
INSERT INTO users (email) VALUES ('Alice@Example.com');
INSERT INTO users (email) VALUES ('alice@example.com'); -- unique violation

Backlinks