next year in postgres:

https://twitter.com/nori_shinoda/status/1549683794780622848

SQL-WTF SQL-TIL

A few words about this.
At this moment, you must specify an alias for all sub-selects in FROM clause.
It's valid for PostgreSQL and MySQL.

In Snowflake you can skip it. But under the hood Snowflake name it automatically as "values". So, you can't use more than one unnamed sub-select in FROM clause, because "values" will conflict. And yes, you can address it as "values".*

Btw, there are some more "automatic aliases" in Snowflake:
- "VALUES" (capital letters, not the same) for values()
- "STAGE" for stages
- "LIST_DIR_TABLE" for directory tables

Absolutely insane behavior with sub-selects (named or unnamed) in the best and most popular DB in the world (he-he) - SQLite!

select * from (select 1) a, (select 1) b
Try to guess without tests (post your version in the comments)

All issues of SQL-WTF SQL-TIL magazine
(so you can share it with friends and colleagues):

postgresql edition:
1) https://news.1rj.ru/str/nosingularity/535 (ru)
2) https://news.1rj.ru/str/nosingularity/541 (ru)
3) https://news.1rj.ru/str/nosingularity/548 (ru)
4) https://news.1rj.ru/str/nosingularity/572 (ru)

snowflake edition:
5) https://news.1rj.ru/str/nosingularity/582 (ru)
6) https://news.1rj.ru/str/nosingularity/602 (ru)
7) https://news.1rj.ru/str/nosingularity/753 (ru) + https://news.1rj.ru/str/nosingularity/754 (ru)
8) https://news.1rj.ru/str/nosingularity/762 (ru)
9) https://news.1rj.ru/str/nosingularity/826 (ru)

mix:
10) https://news.1rj.ru/str/nosingularity/755 (ru)
11) https://news.1rj.ru/str/nosingularity/803 (ru) + https://news.1rj.ru/str/nosingularity/804 (ru)
12) https://news.1rj.ru/str/nosingularity/808 (ru) + https://news.1rj.ru/str/nosingularity/809 (ru)
13) https://news.1rj.ru/str/nosingularity/857 (ru)
14) https://news.1rj.ru/str/nosingularity/1024 (en)

HAXE in a new way:
https://github.com/pfusik/cito

SQL-WTF SQL-TIL snowflake edition.

In Snowflake, you can create tables, stages, and functions temporarily.
Temporary objects remain visible in the current session only and disappear after the session ends.

The unusual behavior is that you can create a temporary object with the same name as an already existing object.

So, all existing objects linked with it will be relinked to the "new" temporary object. For e.g. all views dependent on the original table will depend on the temporary table.

More than it!

The permanent table created after the temporary table with the same name stays invisible in the current session too :)

OMG
https://github.com/oguimbal/pg-mem

Well ... then it's time to remind you about my collection of open source SQL-tools:
https://github.com/antonrevyako/useful-links/blob/master/opensource-sql-tools.md

JUG Ru Group's SmartData is back!

The conference for data engineers will take place in October.

Would you like to give a talk there? If you have interesting cases or you want to share your experience of nontrivial solutions, apply for participation.

You will confirm your expertise, get to know other experts and get feedback from the participants. The Program Committee will help to prepare for the talk: schedule a personal curator, conduct a review of the material and organize rehearsals.

On the website, you will find a list of topics you can talk about. If you have another topic, send your suggestions; they will be considered.

And if you just want to participate in SmartData 2022, tickets are already on the website.

no time to explain, just follow dwh.dev on linkedin!

https://www.linkedin.com/company/dwhcorp

One optimization story:
- AssemblyScript (WASM) with recursive calls: 40 sec
- JavaScript with recursive calls: 20 sec
- JavaScript with stack instead of recursive calls: 2 sec

By the way, Postgres developers say that v8 is the fastest engine for UDF

Final stop!
Posgresql. WASM. Browser.

https://www.crunchydata.com/blog/learn-postgres-at-the-playground

When you want to make sure your primary key is really really really unique (valid in PostgreSQL) by Lukas Eder