Also, I genuinely dislike how loose SQLite is with allowed syntax. Probably it's preference. But even interactively I prefer to know immediately that I messed up a query. SQLite is so forgiving I've often wasted time trying to understand why my results are nonsense (because I typoed in the query and SQLite didn't fail the query).
But I also strongly dislike Python for that reason and I know where you stand there. Maybe SQLite/PostgreSQL is similar to the dynamic/static language preference divide.
- https://til.simonwillison.net/sqlite/sort-by-number-of-json-...
- https://til.simonwillison.net/sqlite/function-list#user-cont...
Sqlite can't do custom format date parsing and regex extract. How do we extend something like this?
If we go beyond a simple function to window function, I imagine it would be even harder.
At this point, we nlmight as well use postgres.
If you need them, you also have a "stdlib" implemented for Go [1] and a pretty extensive collection of extensions [2]
[0]: https://ricardoanderegg.com/posts/extending-sqlite-with-rust...
This seems like I can add a lot more functions to it, not just regex extract.
Came here to complain and learned something useful.
SQLite is an in-process database. You can give it a callback func to execute. So your regex-extract can literally just call a function in your code: https://sqlite.org/appfunc.html
edit: Python's stdlib documentation concisely shows how easy this can be: https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne... Basically every SQLite library should have something similar. This extreme ease of extending is a big part of why SQLite has so little built-in.
I agree that SQLite default functionality is very thin compared to PostgreSQL - especially with respect to things like date manipulation - but you can extend it with more SQL functions (and table-valued functions) very easily.