On: PostgreSQL 12 Beta 1 Released

[jakobegger]

The biggest news for me is that PostgreSQL 12 now really supports case-insensitive collations!

    CREATE COLLATION case_insensitive (
      provider = icu,
      locale = 'und-u-ks-level2',
      deterministic = false
    );
    
    CREATE TABLE names(
      first_name text,
      last_name text
    );
    
    insert into names values
      ('Anton','Egger'),
      ('Berta','egger'),
      ('Conrad','Egger');
    
    select * from names
      order by
        last_name collate case_insensitive,
        first_name collate case_insensitive;

And you'll get them in the correct order, despite uppercase/lowercase differences in the last name column.

[gmac → jakobegger]

I think the citext extension[1] already made much of this straightforward?

What I'm most excited about is the fact that WITH clauses will no longer necessarily be optimization fences — I have some big queries that are really only manageable this way, and now they may well be very much faster.

[1] https://www.postgresql.org/docs/11/citext.html

[pilif → gmac]

>I think the citext extension[1] already made much of this straightforward?

sort of. It was doing case-folding based on LC_CTYPE set at initdb time, so if you have data of different locales to deal with, it would only work for one locale and be (subtly) broken for others (Turkish comes to mind)

It's also based on glibc's interpretation of case-folding rather than what the Unicode consortium has agreed upon.

Depending on locale and tolerance to weirdness that can be anywhere between a non-issue and a show-stopper.

Case-folding is very tricky business.

[ako → gmac]

Why are you not using views instead of with clauses?

[jeffdavis → ako]

Creating views makes more sense sometimes and CTEs make more sense other times.

Creating views just for one query is kind of like creating temporary variables for each subexpression. Sure, it works, but not a great solution.

Also, other databases do optimize through a CTE. So if you port to PG 11, you might see a lot of regressions in plans unless you rewrite to use views instead. PG 12 will work a lot better for existing queries.