Simon Willison: TILs on postgresql

postgresql Constructing GeoJSON in PostgreSQL - 2021-04-24

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 json_build_object() and friends. …

postgresql Using json_extract_path in PostgreSQL - 2021-04-13

The json_extract_path() function in PostgreSQL can be used to extract specific items from JSON - but I couldn't find documentation for the path language it uses. …

postgresql Using unnest() to use a comma-separated string as the input to an IN query - 2021-04-10

django-sql-dashboard lets you define a SQL query plus one or more text inputs that the user can provide in order to execute the query. …

postgresql Closest locations to a point - 2021-03-22

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 that distance. …

postgresql Granting a PostgreSQL user read-only access to some tables - 2021-02-26

I wanted to grant a PostgreSQL user (or role) read-only access to a specific list of tables. …

postgresql Show the SQL schema for a PostgreSQL database - 2021-02-23

This took me longer to figure out than I care to admit. …