Из сорцов sqlite

here we go again:
https://www.linkedin.com/feed/update/urn:li:activity:7224013497884454912?commentUrn=urn%3Ali%3Acomment%3A%28activity%3A7224013497884454912%2C7224078490243850241%29&dashCommentUrn=urn%3Ali%3Afsd_comment%3A%287224078490243850241%2Curn%3Ali%3Aactivity%3A7224013497884454912%29

(feel free to JOIN this hate speech)

Ребята из Supabase не перестают радовать! 😲

На днях в сети появился новый сервис от Supabase: https://postgres.new. Это data modeling сервис на базе Postgres со встроенным AI ассистентом. Запуск Postgres прямо в браузере возможен благодаря https://pglite.dev/. Это облегчённая версия Postgres, упакованная в WASM.

Целевая аудитория сервиса:

— аналитики данных
— дата инженеры
— студенты, изучающие реляционные базы данных, в частности PostgreSQL
— разработчики, разрабатывающие схемы таблиц

Пример работы с сервисом смотрите на Ютубе.

Eyes Wide Shut Data Lineage №1:
DATA LINEAGE IS A LIE

Suddenly discovered that any data lineage tools are missing one important part... Had to fix it 🙂

https://www.linkedin.com/posts/anton-revyako_snowflake-snowflakedatacloud-datalineage-activity-7232405043776282625--ADs/

Friendly reminder:

The UNION and UNION ALL operators may order the rows in the final result set in an unspecified order. For example, it may place rows from the second result set before/after the row from the first result set.

I have encountered a situation where a query with UNION and UNION ALL resulted in a stable different row order...

UNION is often used for something like this:

SELECT * FROM (
    SELECT id FROM t WHERE name = 'user_input'
    UNION
    SELECT id FROM t WHERE name = 'default_value'
)
LIMIT 1

It is important that if a record with 'user_input' is found, it should be the first record in the result.
In such a case, add an ORDER BY of this form to the query:

ORDER BY name = 'default_value'

https://wafris.org/blog/rearchitecting-for-sqlite?

mysql 9.0

https://dev.mysql.com/doc/refman/9.0/en/mysql-nutshell.html

What do you think they never did? modifying CTE and RETURNING

https://masteringpostgres.com/types

thx https://x.com/samokhvalov/status/1846674221419966485?s=46

btw, don’t forget about https://wiki.postgresql.org/wiki/Don't_Do_This