The Problem⌗

I was recently working with some Postgres tables that have an ltree column. I had written tests for my new feature that made use of them and all the tests passed locally.

However, the tests were failing in CI with a vague error:

ERROR: ltree syntax error at character 9 (SQLSTATE 42601)

The query that was producing this error was quite simple as well:

INSERT INTO "my_table" ("path","value") VALUES ('22e8f4e1-437f-448b-a8fb-0d7fbed8de7a','foo')

In this case, the path column was of type ltree. Running that exact query locally worked fine and I was confused as to why it wasn’t working in CI.

The Cause⌗

It turns out that I was running a more modern version of Postgres locally (17.5) than what was being run in CI (15).

In older versions of Postgres including 15, there is a limitation that values inserted into ltree columns can’t contain the - character. This limitation was removed in Postgres 16, which expanded the set of characters allowed in ltree labels to include hyphens (and also raised the maximum label length from 256 to 1000 characters).

I was able to work around this issue by just stripping all non-alphanumeric characters out of my UUIDs before inserting them into the column.