Comparison & conversion (Greatest, Least, NullIf, Cast, Collate, KT)

# ======================================================================
# `Greatest`/`Least` pick across columns/values, `NullIf(a, b)` returns NULL
# when a==b, `Cast` changes a value's type, `Collate` applies a collation to one
# expression, and `KT` extracts a JSON path as text.
# ======================================================================

# ----------------------------------------------------------------------
# Greatest / Least / Cast / KT over 'Harry Potter'
# ----------------------------------------------------------------------

# Django:
money = DecimalField(max_digits=8, decimal_places=2)
Book.objects.filter(title="Harry Potter").annotate(
    greatest=Greatest("price", Value(25), output_field=money),
    least=Least("price", Value(25), output_field=money),
    as_int=Cast("price", output_field=IntegerField()),
    stock=KT("data__specs__stock"),
).values("greatest", "least", "as_int", "stock")

# SQL:
#   SELECT GREATEST("examples_book"."price", 25) AS "greatest", LEAST("examples_book"."price", 25) AS "least", ("examples_book"."price")::integer AS "as_int", ("examples_book"."data" #>> '{specs,stock}') AS "stock"
#   FROM "examples_book"
#   WHERE "examples_book"."title" = 'Harry Potter'
#   ORDER BY "examples_book"."id" ASC
#   LIMIT 1

# Result:
#   Greatest(price, 25) -> 25
#   Least(price, 25)    -> 19.99
#   Cast(price -> int)  -> 20
#   KT(data.specs.stock)-> '12'  (json value as text)

# ----------------------------------------------------------------------
# NullIf(bio, 'British author') -> NULL when equal
# ----------------------------------------------------------------------

# Django:
Author.objects.annotate(x=NullIf("bio", Value("British author"), output_field=TextField())).values_list("name", "x")

# SQL:
#   SELECT "examples_author"."name" AS "name", NULLIF("examples_author"."bio", 'British author') AS "x"
#   FROM "examples_author"

# Result:
#   J. K. Rowling: None
#   J. R. R. Tolkien: None
#   Karl Müller: 'Schriftsteller'

# ----------------------------------------------------------------------
# Collate(name, 'C') then order by it
# ----------------------------------------------------------------------

# Django:
Author.objects.order_by(Collate("name", "C")).values_list("name", flat=True)

# SQL:
#   SELECT "examples_author"."name" AS "name"
#   FROM "examples_author"
#   ORDER BY "examples_author"."name" COLLATE "C" ASC

# Result:
#   J. K. Rowling
#   J. R. R. Tolkien
#   Karl Müller