Dynamic SQL is the construction of a SQL statement as a string at runtime, then executing it. It exists because some parts of a query cannot be parameterized: table names, column names, sort directions, and identifiers that the query planner needs to see as structure rather than data. The hazard is that the same mechanism…
A Foreign Data Wrapper is PostgreSQL’s implementation of the SQL/MED standard: a library that lets the database treat data residing outside PostgreSQL as if it were a local table . The wrapper handles the protocol for connecting to the external source, fetching rows, and returning them to the PostgreSQL executor. The postgres_fdw extension is the…
Change Data Capture is the pattern of detecting row-level changes—inserts, updates, and deletes—as they happen, and streaming them to downstream systems. The two dominant approaches in relational databases are log-based CDC, which reads the database’s transaction log, and trigger-based CDC, which installs database triggers that write to an audit table. Debezium is the de-facto open-source…
Vector search brings the core operation of modern AI applications—finding items that are semantically similar—into the relational database. An embedding is a high-dimensional vector of floating-point numbers that represents the meaning of text, an image, or any other data. Two embeddings that are close in vector space represent items with similar meaning. pgvector is the…
Relational databases have stored XML data for decades, but the mechanism for querying it is not SQL. XPath is a separate expression language with its own syntax, its own type system, and its own limitations. The SQL functions that expose XPath—PostgreSQL’s xpath() and xpath_exists(), Oracle’s XMLExists and XMLQuery, SQL Server’s xml methods—are bridges between two…
Duplicate rows in TSV files are a pain. You sort. You scan. You delete one by one. And if you accidentally delete the wrong tab, the whole dataset shifts. I built a free tool that solves this. What it does: Use cases: Privacy:100% client-side. Nothing uploaded. Try it: tools.kandz.me/tsv-row-deduplicator
Package lifecycle management extends beyond publishing. A maintainer eventually faces one of three decisions: remove a package entirely, mark it as no longer recommended while keeping it installable, or hand it to someone else. npm provides a distinct command for each, and the registry’s policies determine which commands are even permitted. The default recommendation from…
Distribution tags (dist-tags) are human-readable labels that point to specific versions of a published package. The latest tag is the one npm uses by default when a consumer runs npm install package-name without a version or tag specifier. Every other tag—next, beta, stable, canary—is a custom pointer that the publisher creates and manages. The npm…
Publishing a package to the npm registry is a single command, but the result depends on a decision made before the command runs: is the package scoped or unscoped, and is the account paid or free? The npm publish command reads the package name in package.json and applies a default access level based on whether…
Version management is the discipline of incrementing a package’s version number in a way that communicates the nature of the change to the consumers who depend on it. The npm version command automates this increment, writing the new version to package.json and package-lock.json, and—when run inside a Git repository—creating a commit and a tag that…