sql

Granting a PostgreSQL user read-only access to some tables

I wanted to grant a PostgreSQL user (or role) read-only access to a specific list of tables. I created the role using the Heroku PostgreSQL web console. Having done that, it had the name .

Closest locations to a point

Here's a PostgreSQL SQL query that returns the closest locations to a point, based on a brute-force approach where the database calculates the distance (in miles) to every single row and then sorts by…

Combining CTEs and VALUES in SQLite

Here's how to use SQLite's syntax with a CTE to create a temporary table that you can then perform joins against in a query: Try that here.

Calculating the size of a SQLite database file using SQL

I learned this trick today while browsing the code of Blacklite, a neat Java library for writing diagnostic logs to a SQLite database.

Cumulative total over time in SQL

This is a quick trick for creating a cumulative chart of the total number of items created over time based just on their creation date.

GUnion to combine geometries in SpatiaLite

I was playing around with datanews/amtrak-geojson, which provides GeoJSON for Amtrak stations and track segments.

Combining substr and instr to extract text

Derek Willis has a Datasette instance full of political campaign emails running on Heroku. Matt Hodges pointed out that a lot of these emails include codes, which are used by ActBlue campaigns to…

Creating a dynamic line chart with SVG

I helped build the tide chart visualizations for Rocky Beaches. I wanted to generate an SVG line representing 24 hours of tide levels.

Comparing database rows before and after with SQLite JSON functions

Here's a trick I've been using to compare the rows in a table before and after I perform an operation against it. It works well for a few hundred (and maybe a few thousand) rows.

Using recursive CTEs to explore hierarchical Twitter threads

This TIL adapted from a Gist I put together in 2019, before I started tracking TILs here. My twitter-to-sqlite tool produced a SQLite table with an column that referenced another tweet ID, for…

Finding duplicate records by matching name and nearby distance

I wanted to find potentially duplicate records in my data, based on having the exact same name and being geographically located within 500 meters of each other.

Figuring out if a text value in SQLite is a valid integer or float

Given a table with a column in SQLite I want to figure out if every value in that table is actually the text representation of an integer or floating point value, so I can decide if it's a good idea…

Constructing GeoJSON in PostgreSQL

In order to efficiently generate a GeoJSON representation of a vast number of locations, I'm currently experimenting with generating the GeoJSON directly inside a PostgreSQL SQL query using and…

Consecutive groups in SQL using window functions

I have a database table with all of my Swarm checkins since 2011, created using my swarm-to-sqlite tool.