Thursday, September 12, 2024
One operation I find myself occasionally doing with anything involving a database is “get or create”: return an object with a particular ID or unique field, and if it doesn’t exist, create it. Yesterday, in the project I’m working on now, I saw a need for this. Being backed by a PostgreSQL database, I guess I could’ve just opened up a transaction, ran a SELECT and if it was empty, run an INSERT.
Thursday, May 30, 2024
Subtitle: if you know what these four words mean, then this post is for you This is a quick one as I’m not really in a blogging mood. But I couldn’t for the life of me find a way to decode a PostgreSQL bytea value (which is what PostgreSQL uses for BLOB values) using pgx and sqlc so that it would match what I actually stored. The documentation of pgx and sqlc, along with various web searches, yielded nothing.
Monday, May 13, 2024
The same person that taught me about LATERIAL SQL queries also showed me the BIGSERIAL type, which is an automatically incrementing integer column. I don’t know why I didn’t see this before. The AUTOINCREMENT option in MySQL was one of the things I missed when I started using PostgreSQL. I guess I just assumed that one had to explicitly create a sequence and include calls to nextval() when inserting things into a PostgreSQL table, and that was “just how it was done.
Tuesday, April 23, 2024
Someone shared with me the LATERAL join type supported by PostgreSQL. He described it as a “for each” built into SQL: When a FROM item contains LATERAL cross-references, evaluation proceeds as follows: for each row of the FROM item providing the cross-referenced column(s), or set of rows of multiple FROM items providing the columns, the LATERAL item is evaluated using that row or row set’s values of the columns. The resulting row(s) are joined as usual with the rows they were computed from.