--- title: SQL Improvements in PostgreSQL 11–18: A Personal Selection description: A curated tour of SQL-level improvements across PostgreSQL versions 11 through 18, written by a PostgreSQL major contributor while revising The Art of... canonical: https://daily.dev/posts/sql-improvements-in-postgresql-11-18-a-personal-selection-hujlsgrj1 twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: SQL Improvements in PostgreSQL 11–18: A Personal Selection | daily.dev og:description: A curated tour of SQL-level improvements across PostgreSQL versions 11 through 18, written by a PostgreSQL major contributor while revising The Art of... og:url: https://daily.dev/posts/sql-improvements-in-postgresql-11-18-a-personal-selection-hujlsgrj1 og:image: https://api.daily.dev/og/posts/hUJLsgrj1.png og:image:alt: SQL Improvements in PostgreSQL 11–18: A Personal Selection og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # SQL Improvements in PostgreSQL 11–18: A Personal Selection **[Planet PostgreSQL](https://daily.dev/sources/planet-postgresql)** · 25 min read · 0 upvotes · 0 comments ## Summary A curated tour of SQL-level improvements across PostgreSQL versions 11 through 18, written by a PostgreSQL major contributor while revising The Art of PostgreSQL. Covers window frame GROUPS mode and EXCLUDE clauses, FETCH FIRST WITH TIES, ANY_VALUE(), CTE materialization hints, SEARCH/CYCLE in recursive CTEs, multirange types and range_agg(), virtual generated columns, NULLS NOT DISTINCT, MERGE with WHEN NOT MATCHED BY SOURCE, RETURNING OLD/NEW, JSON_TABLE, uuidv7(), COPY ON_ERROR IGNORE, and the built-in C.UTF-8 collation. Each feature is illustrated with working SQL examples using real datasets (F1 standings, band membership). Operational improvements like REINDEX CONCURRENTLY, pg_stat_io, and incremental backups are also briefly covered. Ends with a note on pg_trgm index rebuilding required after upgrading to PG 18. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### Why are my trigram index searches missing matches after upgrading to PostgreSQL 18? PostgreSQL 18 changed how pg_trgm generates trigrams, applying lower-casing consistently before splitting strings into three-character windows, whereas earlier versions had inconsistent lower-casing across code paths. GiST or GIN trigram indexes built on PostgreSQL 17 or earlier still encode trigrams using the old scheme, so queries using %, <->, or ~* can silently miss matches. Rebuild all trigram indexes with REINDEX CONCURRENTLY after upgrading. _daily.dev surfaces upgrade gotchas like this so trigram search doesn't silently break after a PostgreSQL upgrade._ ### What is the difference between uuidv7() and gen_random_uuid() in PostgreSQL 18? gen_random_uuid(), built into PostgreSQL since version 13, generates a random UUID v4 with no ordering guarantees. uuidv7(), added in PostgreSQL 18, encodes the current millisecond timestamp in the first 48 bits, so UUIDs generated in sequence also sort lexicographically, improving B-tree index locality and clustered writes. For new schemas with heavy insert loads or range scans, uuidv7() is the better primary-key choice. _developers choosing a UUID primary key strategy can track feature landings like uuidv7() on daily.dev._ ### What does the WHEN NOT MATCHED BY SOURCE clause do in PostgreSQL MERGE? WHEN NOT MATCHED BY SOURCE, added in PostgreSQL 17, is a third MERGE branch covering target rows that have no matching row in the source dataset, allowing actions like deletion for a complete full-table synchronization. It complements the WHEN MATCHED and WHEN NOT MATCHED branches introduced with SQL MERGE in PostgreSQL 15, and PostgreSQL 17 also added RETURNING support with merge_action() to report which branch fired per row. _anyone building sync logic in SQL can follow MERGE's evolving capabilities via daily.dev._ ## Similar posts on daily.dev - [9 Advanced PostgreSQL Features I Wish I Knew Sooner](https://daily.dev/posts/9-advanced-postgresql-features-i-wish-i-knew-sooner-nezidnbnu) · marmelab · 115 upvotes · 0 comments - [PostgreSQL 18 on Amazon Aurora and Amazon RDS: Performance enhancements](https://daily.dev/posts/postgresql-18-on-amazon-aurora-and-amazon-rds-performance-enhancements-lag8ehltp) · AWS Database Blog · 0 upvotes · 0 comments - [10 Rare SQL Features Every Developer Should Know](https://daily.dev/posts/10-rare-sql-features-every-developer-should-know-fvu4qwczx) · Medium · 1 upvotes · 0 comments --- Tags: [#sql](https://daily.dev/tags/sql), [#postgresql](https://daily.dev/tags/postgresql) [View this post on daily.dev](https://daily.dev/posts/sql-improvements-in-postgresql-11-18-a-personal-selection-hujlsgrj1) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"SQL Improvements in PostgreSQL 11–18: A Personal Selection","url":"https://daily.dev/posts/sql-improvements-in-postgresql-11-18-a-personal-selection-hujlsgrj1","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/sql-improvements-in-postgresql-11-18-a-personal-selection-hujlsgrj1"},"datePublished":"2026-07-28T16:18:27.459Z","dateModified":"2026-09-14T08:42:49.611Z","description":"A curated tour of SQL-level improvements across PostgreSQL versions 11 through 18, written by a PostgreSQL major contributor while revising The Art of...","image":"https://media.daily.dev/image/upload/s--foaA6JGU--/f_auto/v1722860399/public/Placeholder%2004","thumbnailUrl":"https://media.daily.dev/image/upload/s--foaA6JGU--/f_auto/v1722860399/public/Placeholder%2004","isAccessibleForFree":true,"articleSection":"Planet PostgreSQL","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"Planet PostgreSQL","logo":"https://media.daily.dev/image/upload/logos/placeholder.jpg","url":"https://daily.dev/sources/planet-postgresql"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/sql-improvements-in-postgresql-11-18-a-personal-selection-hujlsgrj1","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"sql,postgresql","timeRequired":"PT25M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"Planet PostgreSQL","item":"https://daily.dev/sources/planet-postgresql"},{"@type":"ListItem","position":3,"name":"SQL Improvements in PostgreSQL 11–18: A Personal Selection"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/sql-improvements-in-postgresql-11-18-a-personal-selection-hujlsgrj1#faq","mainEntity":[{"@type":"Question","name":"Why are my trigram index searches missing matches after upgrading to PostgreSQL 18?","acceptedAnswer":{"@type":"Answer","text":"PostgreSQL 18 changed how pg_trgm generates trigrams, applying lower-casing consistently before splitting strings into three-character windows, whereas earlier versions had inconsistent lower-casing across code paths. GiST or GIN trigram indexes built on PostgreSQL 17 or earlier still encode trigrams using the old scheme, so queries using %, <->, or ~* can silently miss matches. Rebuild all trigram indexes with REINDEX CONCURRENTLY after upgrading. daily.dev surfaces upgrade gotchas like this so trigram search doesn't silently break after a PostgreSQL upgrade."}},{"@type":"Question","name":"What is the difference between uuidv7() and gen_random_uuid() in PostgreSQL 18?","acceptedAnswer":{"@type":"Answer","text":"gen_random_uuid(), built into PostgreSQL since version 13, generates a random UUID v4 with no ordering guarantees. uuidv7(), added in PostgreSQL 18, encodes the current millisecond timestamp in the first 48 bits, so UUIDs generated in sequence also sort lexicographically, improving B-tree index locality and clustered writes. For new schemas with heavy insert loads or range scans, uuidv7() is the better primary-key choice. developers choosing a UUID primary key strategy can track feature landings like uuidv7() on daily.dev."}},{"@type":"Question","name":"What does the WHEN NOT MATCHED BY SOURCE clause do in PostgreSQL MERGE?","acceptedAnswer":{"@type":"Answer","text":"WHEN NOT MATCHED BY SOURCE, added in PostgreSQL 17, is a third MERGE branch covering target rows that have no matching row in the source dataset, allowing actions like deletion for a complete full-table synchronization. It complements the WHEN MATCHED and WHEN NOT MATCHED branches introduced with SQL MERGE in PostgreSQL 15, and PostgreSQL 17 also added RETURNING support with merge_action() to report which branch fired per row. anyone building sync logic in SQL can follow MERGE's evolving capabilities via daily.dev."}}]} ```