Is it the SQL dialect there lacking or is it the built-in functions?
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.
I like SQLite (qualifying not for you, simonw, but for others). But I hate that I can't be lazy by using arrays in SQLite... because they don't exist. group_concat is a poor approximation.
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.
I'm increasingly finding myself using SQLite's JSON features for array stuff - they're surprisingly convenient once you get into the habit of using them. A couple of recent examples:
Adding user-defined functions to SQLite is not difficult, and the mechanism is quite flexible. You can create extensions and load them when you create the SQLite connection to have the functions available in queries. I wrote a blog post explaining how to do that using Rust, and the example is precisely a `regex_extract` function [0].
If you need them, you also have a "stdlib" implemented for Go [1] and a pretty extensive collection of extensions [2]
Probably also worth noting: you don't need to build (many kinds of) extensions as C-compatible code and separate .so files that you load.
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.