Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

IntermediateString Functions

Generate slugs from product names

The query

SQL
SELECT
  name,
  LOWER(
    REGEXP_REPLACE(
      REGEXP_REPLACE(
        TRIM(name),
        '[^a-zA-Z0-9\s-]', '', 'g'  -- remove special chars
      ),
      '\s+', '-', 'g'  -- spaces to hyphens
    )
  ) AS slug
FROM products;
Tested against PostgreSQL 16

Note

“URL slug generation: strip special chars, replace spaces with hyphens, lowercase. Classic ETL transformation.

Tables referenced