Advent of Code in PostgreSQL: Tips and Tricks from 2022

I’ve nearly finished solving the 2022 series in Advent of Code in PostgreSQL on our blog, many of these are available on our browser based Postgres playground as well. As many of you embark on your own Advent of Code adventures for 2023 this week, or maybe watch from afar, I wanted to pull together some themes, recommendations, tips, and tricks that I’ve seen work with the solutions. If there’s anything I’ve learned, it’s that you can solve almost anything with PostgreSQL!

psql holiday presets

Before you do anything, get in the holiday spirit and set your nulls to a snowman ☃️ or any other image you’d like:

\pset null ☃

Data loading via text FDW

You’ll see that every time I use the file_fdw extension to connect to the file via a foreign table. This saves me from having to load the file in. I can connect to that, build my new relational tables and move the data from the foreign data wrapper.

CREATE EXTENSION file_fdw;

CREATE SERVER aoc2022 foreign data wrapper file_fdw;

CREATE FOREIGN TABLE aoc_day1 (calorie int)
  SERVER aoc2022 options(filename '/tmp/aoc2022.day1.input', null '');

I’m also a big fan of unlogged tables. These are helpful because these challenges are ephemeral and everything will run faster if you take out the logging.

Using sequences

Most of these puzzles require you to take an input file of plan ASCII text and start organizing into a way that will help you create the solutions, puzzles, mazes, etc. One key PostgreSQL feature here is to use CREATE SEQUENCE. When used in combination with CTEs, regex, arrays and other functions, it will help you create order out of the chaos given to you with your starting Advent of Code file.

A lot of my sequences appear at the start of a CTE; see this example from Day 22

CREATE SEQUENCE aoc;
CREATE SEQUENCE aoc2;

WITH x AS (SELECT nextval('aoc') AS myrow, setval('aoc2',1,false), line
  FROM aoc_day22 WHERE line !~ '\d')
,y AS materialized (SELECT *, string_to_table(line, null) AS a FROM x)
,z AS (SELECT *, nextval('aoc2') AS mycol FROM y)
INSERT INTO monkeymap (y,x,item)
SELECT myrow, mycol, a FROM z WHERE a <> ' ';

A few key functions in sequences are:

• setval: For setting a value
• nextval: For getting the next value in a sequence.
• currval: For calling the current value in a sequence.

Day 1 uses all three of these:

-- Call it once so that `currval` works. The starting number can be anything.
SELECT setval('aoc', 1);

SELECT calorie, CASE WHEN calorie is null THEN nextval('aoc') ELSE currval('aoc') END
FROM aoc_day1;

Window Functions

You’ll also find a lot of uses for window functions in creating sequences and keeping track of what row you’re working in. See Day 12 for an example.

WITH x AS (SELECT setval('aoc',1,false), line FROM aoc_day12)
,xgrid AS (
  SELECT row_number() over() AS row, nextval('aoc') AS col,
  regexp_split_to_table(line, '') AS height
FROM x)
SELECT * FROM xgrid LIMIT 10;

PL/pgSQL functions

If you’re brand new to doing this in Postgres, many of these games are solved by creating a large function that goes through the data and does the work for you. Then the subsequent actions or bonus points section is another round of that. The DO command can be a good idea if you’re running a one-time function for playing a game, see Day 6 an example of that. Don’t be scared to create huge functions and build them pieces by piece. I recommend lots of annotations in your code here to help you debug later.

Recursive functions

Recursive functions are going to be a go-to for many of the games. These allow you to run pieces of code, get the results from that to input into a later part of the function. Day 19 is a great example of this where the first part of the function gets a score and the later parts of the function declare what to do with different scores:

/* In the final minute, all we care about is gathering geodes */
  IF minute >= maxminute THEN RETURN geodes + geode_robots; END IF;

  /* If we can afford a geode robot, make it */
  IF ore >= geode_robot_cost AND obsidian >= geode_robot_cost2 THEN

    geode_score = give_me_the_remote(
      ore + ore_robots - geode_robot_cost, ore,
      clay + clay_robots, clay,
      obsidian + obsidian_robots - geode_robot_cost2,
      geodes + geode_robots,
      ore_robots, clay_robots, obsidian_robots, geode_robots + 1,
      ore_robot_cost, clay_robot_cost, obsidian_robot_cost,
      obsidian_robot_cost2, geode_robot_cost, geode_robot_cost2,
      minute, maxminute, ''
    );

Looped functions

Looped functions are going to be common since you’ll be iterating over the data input files and trying to fill in gaps or make sense of things. You might do something like creating a Brute Force Search using a looped function like in Day 12.

FOR myrec IN SELECT * FROM heightmap WHERE id = any(currentq) LOOP
    -- Code for checking directions and updating the queue
END LOOP;

You can also limit the number of loops, like I did in Day 23 without an exit clause.

/* For this main loop, let's bail if we hit 5000 rounds */
WHILE myround < 5000 LOOP
  myround = myround + 1;

Locations, tracking, and arrays

Quite a few of these games deal with finding things in a grid or location and you’ll be getting pretty creative with SQL to make this work. You can use || and array_remove to populate and then remove some of your text based location data. There’s a good example of this in Day 12.

-- This array stores which points we are currently interested in
  currentq = currentq || startpoint;

  -- This array stores points that have already been checked
  visited = visited || startpoint;

The animated solutions puzzles take these even further by creating locations and moving objects. See Day 19 for an example of this.

rockpaths POINT[][] = ARRAY[
    [(0,0),(0,0),(1,0),(2,0),(3,0)], /* HLINE  */
    [(1,0),(0,1),(1,1),(2,1),(1,2)], /* CIRCLE */
    [(0,0),(1,0),(2,0),(2,1),(2,2)], /* ANGLE  */
    [(0,0),(0,0),(0,1),(0,2),(0,3)], /* VLINE  */
    [(0,0),(0,0),(0,1),(1,0),(1,1)]  /* BOX    */

Regex

Regular expressions are a super important part of the PostgreSQL functions in solving Advent of Code, both for sequencing the initial data set and in the game functions. Here’s some popular regex functions to keep in your back pocket.

regexp_split_to_table()

Used for splitting strings into individual characters, see Day 2.

regexp_split_to_table(password, '')

regexp_matches()

Employed for matching and extracting specific patterns in strings, see Day 4.

regexp_matches(passport_data, '(\w+):', 'g')

regexp_replace()

Utilized for replacing specific substrings in strings, see Day 7.

regexp_replace(description, ' bags?', '', 'g')

regexp_like()

Used for checking if a string matches a specified pattern, also in Day 2.

regexp_like(password, '^(\d+)-(\d+) (\w): (\w+)$')

regexp_split_to_array()

Similar to regexp_split_to_table(), used for splitting strings into arrays, see Day 3.

regexp_split_to_array(line, '')

regexp_substr()

Employed for extracting substrings based on a regular expression, see Day 7.

regexp_substr(contains, '(\d+) (\w+) bags?', 1, 1, '', 1)

ASCII animations

Moving ASCII art

One of the things that made AOC in PostgreSQL really cool was the ability to actually have things move around inside the console. It was fun to move from plain numbers to actual graphics. By combining functions for moving objects in a loop, you can see your ASCII animations as the functions run. See Day 5 for a good example of this.

Drawing with data and creating a pixel screen output

You can create functions to draw table data, to solve an ASCII puzzle, see Day 10 for an example of this.

NOTICE:   ##  #    #### #### #### #     ##  ####
NOTICE:  #  # #    #       # #    #    #  # #
NOTICE:  #  # #    ###    #  ###  #    #    ###
NOTICE:  ###  #    #     #   #    #    # ## #
NOTICE:  # #  #    #    #    #    #    #  # #
NOTICE:  #  # #### #### #### #    ####  ### ####

Adding ANSI colors

For more advanced visual effects, you can add color to your functions so they output with a specific look in your terminal emulator. See Day 9.

CASE WHEN myrec.head THEN E'\033[31mH\033[0m'
             WHEN myrec.middle ~ '9' THEN E'\033[33mT\033[0m'
             WHEN myrec.tailcount>0 THEN E'\033[32m*\033[0m' ELSE ' ' END);

Animating Objects inside a PostgreSQL Terminal

Day 17’s puzzle builds a falling block game akin to Tetris. The fundamentals here are creating sequences to track positions, a function to add new objects, a function for shifting objects, a function to draw the base chamber/game board, and a function that combines these other functions. What you get is a whole game like this.

My takeaways from doing AOC ALL in Postgres/SQL

• Postgres is so extensible, I even created a new operator in Day 2
• SQL is great for some things, atrocious for others
• Sometimes fast and ugly is better than slow and perfect
• Performance is often a bunch of small things over a large number of iterations
• Brute force seldom works - it’s the algorithm / approach that matters
• Pure SQL is possible but painful and PL/pgSQL is much easier than recursive CTEs

The series of solutions for 2022 Advent of Code in Postgres is available if you want to scroll through and use ideas for 2023. Good luck to all!

This post was co-authored with Elizabeth Christensen.