Types, Constraints & Database Objects

Text Types, Collations, and Encoding

Text correctness spans character encoding, length semantics, normalization, and collation; comparison rules affect ordering, equality, indexes, and whether a unique constraint rejects two spellings.

Unicode is a character repertoire, while UTF-8 is an encoding.
CHAR, VARCHAR, and large text differ in constraints more than meaning.
A collation defines comparison and sort behavior.
Canonical-equivalent Unicode can have different code sequences.
Uniqueness inherits comparison semantics.
Changing collation can require rebuilding indexes.

Comparison policy and consequence
Policy	May compare equal	Unique-key consequence
Binary/code-unit	`Résumé` only to identical encoding	Case/accent variants coexist
Case-insensitive	`SKU-7`, `sku-7`	Only one spelling can be inserted
Case- and accent-insensitive	`resume`, `résumé`	Both collapse to one key
Normalized application key	Canonical-equivalent sequences	Stable only if every write uses the same normalization

CREATE TABLE product_aliases (
  alias_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id BIGINT NOT NULL REFERENCES products(product_id),
  display_name TEXT NOT NULL,
  search_name TEXT COLLATE "und-x-icu" NOT NULL,
  UNIQUE (search_name)
);

The added product_aliases object separates preserved display text from an identity/search key. The shown collations illustrate vendor syntax, not a portable promise: test actual case, accent, punctuation, normalization, and language behavior for the chosen collation.