CARTO Analytics Toolbox for Postgres

June 12, 2026 · View on GitHub

The Analytics Toolbox for Postgres contains SQL functions. The functions are deployed in a schema called carto inside a specific database. In Postgres, the functions can be used with tables of the same database, but different schemas.

Tools

Make sure you have installed the following tools:

Python version issue with Numpy

make build-modules will fail in the case of Python3.10.x due to the lack of numpy package compatible with this Python version.

Unfortunately, Ubuntu 20.4 LTS installs Python 3.10.6 by default. To solve the issue Install a lower version e.g. under Ubuntu:

apt install software-properties-common
# add repo with python packages
add-apt-repository ppa:deadsnakes/ppa
apt install python3.9-full python3.9-distutils

Then, modify the Python version used to set up the virtualenv in ./common/Makefile.

Environment variables

The .env file contains the variables required to deploy and run the toolbox. Replace each <template> with your values.

# Postgres
PG_PREFIX=
PG_HOST=<host>
PG_DATABASE=<database>
PG_USER=<user>
PG_PASSWORD=<password>

Structure

  • common
  • modules
    • doc: contains the functions' documentation
    • sql: contains the functions' SQL code
    • test: contains the functions' tests

Make commands

  • make help: shows the commands available in the Makefile
  • make lint: runs linters (flake8, markdownlint, sqlfluff, brunette --check, eslint) in read-only mode and fails if any issue is found
  • make lint-fix: applies the auto-fixable changes (brunette, eslint --fix, markdownlint --fix, sqlfluff --fix)
  • make build: builds the final SQL script
  • make deploy: builds and deploys the SQL scripts in the Postgres database
  • make test: runs the modules tests with the Postgres database (pytest)
  • make benchmark: runs per-function timing benchmarks. Flags: keep=1 (preserve output tables for inspection), verbose=1 (full error messages), tags=<tag,…> (run only cases with matching tag). Configure inputs in modules/benchmark/config.json (copy from config.template.json).
  • make remove: removes all the SQL scripts from the Postgres cluster and database
  • make clean: cleans the installed dependencies and generated files locally
  • make create-package: creates the installation package in the dist folder (zip)

Make commands can be run also inside modules folders, or be called like make ***-modules.

Filtering

Commands build-modules, deploy-modules, test-modules, create-package, and benchmark-modules can be filtered by the following. All the filters are additive:

  • diff: list of changed files
  • modules: list of modules to filter
  • functions: list of functions to filter

Example:

make build-modules diff=modules/sql/quadbin/QUADBIN_RESOLUTION.sql
make deploy-modules modules=quadbin
make test-modules functions=QUADBIN_RESOLUTION
make create-package modules=quadbin