#sql

• Sqitch Update: The Plan

  https://justatheory.com/2012/05/sqitch-plan

  I gave my first presentation on Sqitch at PGCon last week. The slides are on Slideshare and the PGCon site. It came together at the last minute, naturally. I was not able to pay as close attention to PGCon sessions as I would have liked, as I was doing last minute hacking to get the deploy command working on PostgreSQL, and then writing the slides (which are based on the tutorial). I was pleased with the response, given that this is very much a project that is still under heavy development and available only as a very very early alpha. There was great discussion and feedback afterward, which I appreciate.

  Indexed 11/25/2025

• Doomed To Reinvent

  https://justatheory.com/2009/06/doomed-to-reinvent

  There’s an old saying, “Whoever doesn’t understand X is doomed to reinvent it.”X can stand for any number of things. The other day, I was pointing out that such is the case for ORM developers. Take ActiveRecord, for example. As I demonstrated in a 2007 Presentation, because ActiveRecord doesn’t support simple things like aggregates or querying against functions or changing how objects are identified, you have to fall back on using its find_by_sql() method to actually run the SQL, or using fuck typing to force ActiveRecord to do what you want. There are only two ways to get around this: Abandon the ORM and just use SQL, or keep improving the ORM until it has, in effect, reinvented SQL. Which would you choose?

  Indexed 2/19/2026

• Dynamic OFFSETs and LIMITs

  https://justatheory.com/2009/01/postgres-dynamic-limit

  Indexed 4/15/2026

• Sqitch: Where to Define Dependencies?

  https://justatheory.com/2012/05/sqitch-dependencies

  Indexed 3/24/2026

• How to Determine Your Transaction ID

  https://justatheory.com/2004/10/what-postgres-transaction

  Indexed 4/11/2026

• Learn Mad Database Skillz at YAPC::NA 2009

  https://justatheory.com/2009/05/celko-at-yapc

  A few weeks ago, I twittered that, in my opinion, application developers should really learn how to use databases. And by that I mean SQL, really. I know that a lot of app developers like to use ORMs to access the database, so that you don’t have to really think about it, but most ORMs are written by people who don’t like databases, don’t like SQL, haven’t taken the time to learn it in any depth, and thus don’t write very good SQL. And even if they do like SQL, that usually means they provide a way for you to execute SQL queries directly. The reason? Because the ORM does not really understand how building more and more complex queries can have negative performance issues, and that there is more than one way to do it. It’s pretty common to have to go back to custom SQL to solve performance issues. So to solve those problems, you gotta understand SQL.

  Indexed 8/25/2025

• SQL Change Management Sans Duplication

  https://justatheory.com/2012/01/sql-change-management-sans-redundancy

  Here’s how I propose to eliminate the duplication of code between deploy and revert SQL change management scripts.

  Indexed 3/12/2026

• New in PostgreSQL 9.2: format()

  https://justatheory.com/2012/11/postgres-format-function

  Indexed 4/8/2026

• Sqitch: Back to the VCS

  https://justatheory.com/2012/06/sqitch-vcs-again

  Indexed 2/4/2026

• The Ever Evolving Sqitch Plan

  https://justatheory.com/2012/05/evolving-sqitch-plan

  Indexed 4/1/2026

• Sqitch Symbolism

  https://justatheory.com/2012/09/sqitch-symbolism

  Indexed 4/13/2026

• Committed: pgTAP Result Set Assertion Functions

  https://justatheory.com/2009/07/pgtap-results-eq

  Regular readers will know that I’ve been thinking a lot about testing SQL result sets and how to how to name result testing functions, and various implementation issues. I am very happy to say that I’ve now committed the first three such test functions to the Git repository. They’ve been tested on 8.4 and 8.3. Here’s what I came up with.

  Indexed 4/15/2026

• Fixing Foreign Key Deadlocks in PostgreSQL

  https://justatheory.com/2010/11/postgres-fk-locks-project

  Indexed 4/15/2026

• Sqitch Status: A Step at a Time

  https://justatheory.com/2012/05/sqitch-steps

  Indexed 1/6/2026

• pgTAP Best Practices Slides Available

  https://justatheory.com/2009/11/pgtap-best-practices

  Last month I gave two presentations at the PostgreSQL Conference West. The first was a variation on Unit Test Your Database!, which I’ve now given about six times (and will at least two more times, including tomorrow night for Portland PLUG and in two weeks at the JPUG 10th Anniversary Conference). The second was a new talk, a 90-minute tutorial, called “pgTAP Best Practices.” And here it is (download and Slideshare). Enjoy!

  Indexed 10/7/2025

• MySQL's REPLACE Considered Harmful

  https://justatheory.com/2005/03/mysql-replace-considered-harmful

  Indexed 8/28/2025

• Sqitch: Depend On It!

  https://justatheory.com/2012/08/sqitch-depend-on-it

  Indexed 2/5/2026

• Mocking Serialization Failures

  https://justatheory.com/2012/11/mock-postgres-serialization-failures

  Indexed 4/11/2026

• Unicode Normalization in SQL

  https://justatheory.com/2009/09/postgres-unicode-normalization

  Indexed 3/24/2026

• Managing Key/Value Pairs in PostgreSQL

  https://justatheory.com/2010/08/postgres-key-value-pairs

  Let’s say that you’ve been following the latest research in key/value data storage and are interested in managing such data in a PostgreSQL database. You want to have functions to store and retrieve pairs, but there is no natural way to represent pairs in SQL. Many languages have hashes or or data dictionaries to fulfill this role, and you can pass them to functional interfaces. SQL’s got nothin’. In PostgreSQL, have two options: use nested arrays (simple, fast) or use a custom composite data type (sugary, legible).

  Indexed 4/15/2026

• Sqitch — VCS-powered SQL Change Management

  https://justatheory.com/2012/04/sqitch-draft

  Back in January, I wrote three posts outlining some ideas I had about a straight-forward, sane way of managing SQL change management. The idea revolved around specifying scripts to deploy and revert in a plan file, and generating that plan file from VCS history. I still feel pretty good about the ideas there, and work has agreed to let me write it and open-source it. Here is the first step making it happen. I call it “Sqitch.”

  Indexed 3/12/2026

• More on Database Testing

  https://justatheory.com/2009/05/more-on-database-testing

  More thoughts on they whys and hows of database testing, and wondering why one might think that it’s pointless or useless.

  Indexed 3/22/2026

• Sqitch Update

  https://justatheory.com/2012/04/sqitch-update

  A quick update on Sqitch. I started implementation about a couple of weeks ago. It’s coming a long a bit more slowly than I’d like, given that I need to give a presentation on it soon. But I did things a little differently than I usually do with project like this: I wrote documentation first. In addition to the basic docs I posted a couple weeks back, I’ve written a tutorial. I put quite a lot of time into it, studying the Git interface as I did so, to try to develop useful workflows. The nice thing about this it that it will not only serve as the foundation for my presentation (PHEW! Half the work done already!), but it also serves as a design specification.

  Indexed 8/14/2025

• Neither NULL nor NOT NULL: An SQL WTF

  https://justatheory.com/2009/07/sql-neither-null-nor-not-null

  Indexed 2/27/2026

• SQL Hack: The Something-est From Each Entity

  https://justatheory.com/2010/01/somethingest-from-each-entity

  Indexed 4/10/2026

• Thoughts on Testing SQL Result Sets

  https://justatheory.com/2009/06/pgtap-comparing-relations

  pgTAP: The Critique I’ve been continuing hacking on pgTAP in order to add a lot more schema-testing functionality and a few other niceties. But back when I started the project, I using it to write tests for CITEXT, which was great for my sanity as I developed it, but proved a bit controversial. In a pgsql-hackers post, Tom Lane wrote:

  Indexed 3/12/2026

• Simple SQL Change Management

  https://justatheory.com/2012/01/simple-sql-change-management

  I’ve been thinking a lot about SQL change management. I think I may finally have cracked this thing wide open.

  Indexed 4/5/2026

• My First C: A GTIN Data Type for PostgreSQL

  https://justatheory.com/2006/09/postgres-gtin-0.01

  Indexed 7/22/2025

• Sqitch Update: Almost Usable

  https://justatheory.com/2012/07/sqitch-almost-usable

  Indexed 4/9/2026

• VCS-Enabled SQL Change Management

  https://justatheory.com/2012/01/vcs-sql-change-management

  Some thoughts on how to use VCS history to determine what changes need to be deployed or reverted without relying on a configuration file.

  Indexed 1/30/2026

• Sqitch Update: All Your Rebase Are...Never Mind

  https://justatheory.com/2012/12/sqitch-rebase

  Indexed 4/3/2026

• Understanding Window Functions

  https://justatheory.com/2013/08/understanding-window-functions

  Indexed 4/11/2026

• How to Generate Recurring Events in the Database

  https://justatheory.com/2008/01/postgres-recurring-events

  Going deep on using Postgres functions to create efficient recurring events.

  Indexed 4/15/2026

• Need Help Naming Result Set Testing Functions

  https://justatheory.com/2009/06/pgtap-result-testing-function-names

  Indexed 6/29/2025

• Enforce Foreign Key Integrity in SQLite with Triggers

  https://justatheory.com/2004/11/sqlite-foreign-key-triggers

  After some some Googling and experimentation, I’ve figured out how to enforce foreign key constraints in SQLite. I got most of the code from Cody Pisto’s sqlite_fk utility. I couldn’t get it to work, but the essential code for the triggers was in its fk.c file, so I just borrowed from that (public domain) code to figure it out.

  Indexed 4/8/2026

• Sqitch v0.80: Now With More You

  https://justatheory.com/2012/08/sqitch-more-you

  Indexed 10/16/2025

• pgTAP 0.16 in the Wild

  https://justatheory.com/2009/02/pgtap-0.16

  I’ve been writing a lot tests for a client in pgTAP lately. It’s given me a lot to think about in terms of features I need and best practices in writing tests. I’m pleased to say that, overall, it has been absolutely invaluable. I’m doing a lot of database refactoring, and having the safety of solid test coverage has been an absolute godsend. pgTAP has done a lot to free me from worry about the effects of my changes, as it ensures that everything about the databases continue to just work.

  Indexed 11/23/2025

• Sqitch's Log

  https://justatheory.com/2012/07/sqitch-log

  Indexed 2/18/2026

• Sqitch: Rename Step Objects and the SQL Directory?

  https://justatheory.com/2012/06/sqitch-steps-to-change

  After all of the thinking and rethinking about how to manage a Sqitch plan, I am just about done with all the changes to make it all work. One of the changes I’ve made is that tags are no longer objects that stand on their own between change steps, but are simply names the refer to specific change steps. Not only is this much more like how a VCS thinks of tags (basically another name for a single commit), but it also greatly simplifies the code for iterating over a plan and updating metadata in the database.

  Indexed 3/31/2026

• Enforcing a Set of Values

  https://justatheory.com/2010/01/enforce-set-of-postgres-values

  Indexed 4/14/2026

• Sqitch: Trust, But Verify

  https://justatheory.com/2013/01/sqitch-trust-but-verify

  Indexed 3/22/2026

• My Catalyst Tutorial: Add Authors to the View

  https://justatheory.com/2009/11/sql-view-aggregate-magic

  Indexed 2/17/2026

• Issues with INSERT Execution Ordering

  https://justatheory.com/2005/12/postgres-insert-execution-order-problem

  My latest experiments in my never-ending quest to move as much object/relational mapping into the database as possible has yielded more hacks to work around database features. This time, the problem is that I have two classes, Simple and Extend, where I want the latter to extend the former (hence its name). This is a little different from inheritance, in that, internally, an Extend just references a single Simple object, but externally, it just has a single interface, where attributes of a Simple object are just attributes of an Extend object. The benefit here is that I can have multiple Extend objects that reference the same Simple object—something you can’t do with simple inheritance

  Indexed 2/18/2026

• Sqitch Homebrew Tap

  https://justatheory.com/2013/02/sqitch-homebrew-tap

  If Sqitch is to succeed, it needs to get into the hands of as many people as possible. That means making it easy to install for people who are not Perl hackers and don’t want to deal with CPAN. The Sqitch Homebrew Tap is my first public stab at that. It provides a series of “Formulas” for Homebrew users to easily download, build, and install Sqitch and all of its dependencies.

  Indexed 7/7/2025

• pgTAP 0.21 Drops

  https://justatheory.com/2009/05/pgtap-0.21

  I just dropped a new version of pgTAP following a few marathon hack sessions since my talk at PGCon (movie here, BTW). Actually, the new performs_ok() function came about as I wrote the presentation, but all the rest came on the flight home and in the few days since. Not sure when I’ll hack on it this much again (it’s getting a bit big at 5,758 lines of PL/pgSQL and SQL).

  Indexed 1/22/2026

• What Advanced SQL Book Should I Buy?

  https://justatheory.com/2005/11/which-sql-book

  So, what advanced SQL book should I buy? I’ve learned a lot about SQL over the last year or so, but I’m sure that Josh Berkus is tired of being my own personal advanced SQL reference. So I’d like to really learn more about triggers, stored procedures, rules, views, and whatnot, what they’re best used for and when to use them. And other typical database features that I’m not familiar with, of course.

  Indexed 3/13/2026

• Suggest Method Names for DBIx::Connector

  https://justatheory.com/2009/10/dbix-connector-methods

  Thanks to feedback from Tim Bunce and Peter Rabbitson in a DBIx::Class bug report, I’ve been reworking DBIx::Connector’s block-handling methods. Tim’s objection is that the the feature of do() and txn_do() that executes the code reference a second time in the event of a connection failure can be dangerous. That is, it can lead to action-at-a-distance bugs that are hard to find and fix. Tim suggested renaming the methods do_with_retry() and txn_do_with_retry() in order to make explicit what’s going on, and to have non-retry versions of the methods.

  Indexed 3/12/2026