Alright, let’s talk about shapes. No, not the ones you failed to cut out in kindergarten. We’re dealing with Postgres’s geometric types, a wonderfully useful and occasionally maddening set of tools for when you need to store more than just numbers and text. They live in their own little coordinate-based universe, and while they’re powerful, they have a few quirks that will make you want to gently headbutt your monitor. I’m here to guide you through that.

First, a universal truth: these types operate in a two-dimensional Cartesian plane. Think of it as a classic math graph with an X and Y axis. This is crucial because it means point (0,0) is the origin, not the upper-left corner of your screen like in some graphics systems. Get that mental model right now, or you’ll be perpetually upside-down.

The Humble, Yet Mighty point

The fundamental building block is the point. It’s just a pair of floating-point numbers, (x, y). Simple, right? The first gotcha is syntax. You can’t just type (2, 3). Postgres expects you to explicitly cast to the point type or use its specific constructor syntax.

-- This will likely fail or give you a weird composite type
SELECT (2, 3);

-- Do it like this instead
SELECT point '(2, 3)';
SELECT '(2, 3)'::point;
-- Or using the less common function-style syntax
SELECT point(2, 3);

You’ll mostly use points as elements for the more complex types, but they’re great for quick distance calculations or storing simple coordinates.

Lines and Line Segments (line and lseg)

Here’s where the designers’ choices get… interesting. There are two types: the theoretical, infinite line and the practical, finite lseg (line segment).

The line type is defined by the equation Ax + By + C = 0. So you define it by the coefficients (A, B, C). It’s elegant mathematically, but a pain to reason about intuitively.

-- A horizontal line where y = 5
-- Rewritten as 0x + 1y - 5 = 0, so (A, B, C) = (0, 1, -5)
SELECT line '(0, 1, -5)';

-- A 45-degree angle line: x - y = 0 -> (A, B, C) = (1, -1, 0)
SELECT line '(1, -1, 0)';

The lseg is far more straightforward. It’s just defined by its two endpoints.

-- A line segment from (0,0) to (5,5)
SELECT lseg '[(0,0), (5,5)]';

Honestly, unless you’re solving calculus problems in your SQL queries, you’ll use lseg about 99% more often than line.

The Weirdly Ordered box

A box is represented by two opposite corners. The crucial thing—the thing they don’t put in flashing lights but absolutely should—is that Postgres automatically normalizes boxes. It takes whatever two points you give it and rearranges them to be the lower-left and upper-right coordinates. This is actually brilliant because it makes equality checks consistent.

-- These two boxes are identical. Postgres normalizes them internally.
SELECT box '((0,0), (5,5))' AS box1, box '((5,5), (0,0))' AS box2;
-- Both will be represented as (5,5),(0,0)

This normalization is why the height and width functions always return positive values. It’s a great feature, but you must be aware of it or comparing boxes will seem like magic.

The Deceptively Simple circle

Circles are intuitive: a center point and a radius. The main pitfall here is remembering the order within the syntax: it’s <(center point), radius>.

-- A circle centered at (5,5) with a radius of 3
SELECT circle '<(5,5), 3>';

The operations on circles are what you’d expect: area, circumference, and checking if a point is inside it (@>). The big gotcha is that many of the more complex operators for other types (like && for overlap) don’t work with circles. You’re often better off using the point <-> point distance operator for circle-related logic.

The Kitchen Sink: polygon

The polygon is the most flexible type and, consequently, the most complex. It’s defined by a list of vertices (points) that form a closed path. The “closed” part is critical; the first and last point should be the same, but Postgres will usually close it for you if you forget. Don’t rely on that. Close your polygons.

-- A simple triangle. Note the explicit closure.
SELECT polygon '((0,0), (0,5), (5,0), (0,0))';

Here be dragons:

  1. Performance: Polygons are checked for validity. A complex polygon with hundreds or thousands of points will be more expensive to insert and check than a simpler shape.
  2. Simplicity: The polygon type only works with simple polygons (no self-intersections). If you need to store a shape with a hole in it (like a donut), you’re out of luck with the built-in geometric types. You’d need to look at the PostGIS extension, which is a whole other glorious can of worms.
  3. Vertex Order: While the built-in operators aren’t sensitive to clockwise vs. counter-clockwise order, if you do any custom processing, this will become a massive headache. Be consistent.

Best Practices and Final Thoughts

  • Know Your Operators: The real power comes from operators like @> (contains), <@ (contained in), && (overlaps), and <-> (distance). Look them up. Use them. They’re fast and avoid you writing horrific procedural geometry code.
  • Prefer PostGIS for Real Work: I have to be blunt. If you’re doing anything remotely serious with geospatial data—like mapping—stop right here. These built-in geometric types are for the Cartesian plane. For dealing with Earth (ellipsoids, geodesics, real longitude/latitude, etc.), you need the PostGIS extension. These types are for diagrams, UI layouts, pure math problems, and maybe a game board. Not for plotting shipping routes.
  • Mind the Precision: These are floating-point types. Equality checks (=) can be fraught with peril due to precision errors. Often, you’re better off checking if the distance between two points is less than a very small epsilon value rather than if they’re exactly equal.

They’re a quirky, occasionally frustrating, but ultimately powerful part of Postgres. Just know their limits, and they’ll serve you well.