Add missing PostgreSQL operators and fix >^ geometric operator by rathboma · Pull Request #938 · sql-formatter-org/sql-formatter

Summary

  • Adds support for 5 PostgreSQL operators from core and popular extensions that were incorrectly tokenized (split into shorter operators): <@> (earthdistance), &&& (PostGIS 3D overlap), |=| (PostGIS trajectory distance), ~> (cube), #= (hstore)
  • Fixes the geometric "is above" operator which was defined as ^> instead of >^ per PostgreSQL docs
  • Adds unit tests for each operator in realistic SQL contexts, with links to relevant PostgreSQL/PostGIS documentation

Detail

The formatter's tokenizer was greedily matching shorter known operators instead of the full operator. For example <@> was being split into <@ + >, producing bar <@ > point(1, 2) instead of bar <@> point(1, 2).

The fix is simply adding these operators to the PostgreSQL dialect's operator list. The existing regex builder in regexFactory.ts already sorts operators by length descending, so longer operators automatically take priority.

Operators added

Operator Source Docs
<@> earthdistance extension earthdistance
&&& PostGIS geometry_overlaps_nd
|=| PostGIS geometry_distance_cpa
~> cube extension cube
#= hstore extension hstore
>^ (fix) geometric functions-geometry

Test plan

  • All new operators tested via supportsOperators() for basic spacing and dense mode
  • All new operators tested in realistic SQL contexts (WHERE, ORDER BY, SELECT)
  • Full PostgreSQL test suite passes (460 tests)