ClickHouse Gets More SQL Compatible in 26.4

Added: 2026-05-16

[00:00:00]ClickHouse 26.4 had a bunch of updates that make it more compatible with standard SQL syntax. So, let's launch ClickHouse local and have a look at some of them. So, first, the extract operator now supports PostgreSQL style units. So, we can do extract epoch, d o w, d o y, and so on. And you can see that comes back with the various values for the for now or today. We also have SQL standard intervals with two range qualifiers. So, we can do interval and pass in 1 colon 30 and that's hour to minute. We can then do day to second and year to month.

[00:00:32]And if we run that, we can see that internally it gets decomposed into a sum of single unit intervals. And we can see the results there underneath. We can also append those intervals to, for example, the current time and you see we then get a bunch of times in the future.

[00:00:45]The SQL standard alias for setting the session time zone is now supported as well. So, we can have a look at the time in the current time zone. So, you can see we're in London. But then we can say, "Okay, we're going to change our time zone to be Istanbul." And then run it again and you see the time has now gone a couple of hours forward. We have the SQL standard overlay with placing from and for keywords. So, for example, here we're saying I want to do an overlay of Hello World placing Alexey in position 1. And then I can also say I want to do that but placing Alexey in position 1 but only to replace him in the first five characters. And you can see we get back Alexey World and then Alexey space world. Values can now be used as a table expression. So, we could do, for example, this query here and you can see it under under the hood it's going into SQL standard values. And then we can also now alias those columns. So, instead of having them named, for example, C1 and C2, we could name them name and amount doing a join between two different values table expressions. And finally, we've got natural join where we join on all columns sharing the same name and deduplicate them in the result.

[00:01:42]So, we're going to create ourselves a couple of dummy tables, so customers and orders, and then we'll put some data in.

[00:01:48]And then if we join them together just saying natural join, you see it comes back customer ID only once and then we've got name and product. And if you want to see more from ClickHouse 26.4, you'll want to check out this video next.