https://postgr.es/p/7c5

Case-insensitive search is one of the most common issues I encounter when helping customers migrate from Oracle or SQL Server to PostgreSQL. Unlike Oracle (with NLS_SORT) or SQL Server (with case-insensitive collations), PostgreSQL does not natively support case-insensitive search. Developers typically rely on workarounds such as:

Developers typically rely on workarounds such as:

• Wrapping comparisons with LOWER() or UPPER()
• leverage citext data type (not recommended)
• Leveraging ICU-based nondeterministic collations

Why citext is not recommended.
Reference – https://www.postgresql.org/docs/current/citext.html

In this post, I’ll focus on nondeterministic collations and their behavior with LIKE or ILIKE operators.

NonDeterministic Collations

In PostgreSQL, a nondeterministic collation lets text comparisons ignore case/accents, enabling flexible searches like case-insensitive equality.

creating sample collation to support case insensitive search.

CREATE COLLATION collate_ci (provider = icu, deterministic = false, locale = 'en-US-u-ks-level2');

Let’s us create a sample tables with columns having collate collate_ci.

create table testcollation(col1 text collate collate_ci); 
insert into testcollation values ('a');
insert into testcollation values ('A');

select * from testcollation where col1 = 'a';
 col1 
------
 a
 A
(2 rows)

using nondeterministic collation help’s us implement case insensitive comparison with equality operator’s. But let’s see how it will works for like or ilike operators for wild card based searches.

→ Handle Wild Operators Failure (Like).

Using nondeterministic collations help us achieve case-sensitives search but when used with wildcards operators (like or ilike) it fails as it is not supported till PostgreSQL 17 version.

postgres=> select * from testcollation where col1 like 'a%';
ERROR:  nondeterministic collations are not supported for LIKE

PostgreSQL 17 does not support LIKE/ILIK

https://postgr.es/p/7c5

https://postgr.es/p/7c4

PGDay Austria took place on September 4 in Vienna.

Organised by

• Patricia Horvath
• Ilya Kosmodemiansky
• Hans-Jürgen Schönig
• Cornelia Biascics

CfP Committee

• Rafia Sabih
• Oleksii Vasiliev
• Ryan Booz
• Pavlo Golub (non voting member)

Speakers

• Marko Coha
• Jan Karremans
• Tanmay Sinha
• Álvaro Herrera
• Teresa Lopes
• Peter Hofer
• Dirk Krautschick
• Ants Aasma
• Federico Campoli
• Emre Baransel
• Utku Demir
• Gabriele Quaresima
• Gülçin Yıldırım Jelinek
• Christoph Berg
• Teresa Lopes
• Derk van Veen
• Mayuresh Bagayatkar
• Patrick Lauer
• Ryan Booz
• Floor Drees

Lightning Talk Speakers

• Pavlo Golub
• Frederico Campoli
• Sovenath Shaw
• Klaus Aschenbrenner

https://postgr.es/p/7c4

https://postgr.es/p/7c3

https://postgr.es/p/7c3

https://postgr.es/p/7c2

Postgres Ibiza 2025: October 15-17th

Postgres Ibiza 2025 is back in October. A three-day event split into:

• Conference: 2 days, October 15th and 16th.
• Unconference: 1 day, October 17th, following the spirit of the best open spaces.

The event will be held in the same conference venue as the last editions, the spectacular Palacio de Congresos de Ibiza which happens to be a few steps away from many hotels… and the beach!

Postgres Ibiza 2025 builds on the success of the last editions (2024, 2023 and 2019, before the pandemic) and brings a refreshing, open and diverse Postgres conference. Where all ideas can be shared, the future of Postgres discussed and stronger connections can be made in a truly unique environment.

Call For Papers

Call For Papers is still open, until September 10th. The topic selection is quite open, but here are a few suggested themes to consider:

• Core Innovations: Cutting-edge advancements in Postgres core development and key technologies.
• Creative Extensions: New and innovative extensions that enhance Postgres.
• Containers & Kubernetes: Best practices for running Postgres in containerized environments.
• Compatible Forks: Insights into fully Postgres-compatible variants and forks.
• Cloud Databases: Exploring the benefits of cloud databases that support Postgres.
• Protocol Compatibility: Databases that utilize Postgres-compatible protocols.
• Innovative Projects: Any unique and novel projects related to Postgres.
• Database Comparisons: Demonstrate what other databases do better and how they compare to Postgres.

If you have any questions about the CFP or simply want to get in touch with the Committee, please contact us. If you are unsure about submitting a talk, it’s your first time, or just need some help, we will be happy to assist and provide guidance.

Call for Sponsors

Postgres Ibiza is organized by the non-profit Fundación PostgreSQL, and is a PostgreSQL Community Event. Every sponsorship or donation is key for th

https://postgr.es/p/7c2