I'm trying to update a column with new geometry but am getting

ERROR: column "current_geom" is of type geometry but expression is of type boolean.

UPDATE this_table t1
    SET "current_geom" = ST_Intersects(t2.point_geom,t2.geom)
FROM other_table t2
WHERE t1.current_geom is null

I'm obviously doing something wrong but not sure why it doesn't accept it?

Using: PostgreSQL 9.6 and pgAdmin 4.2

  • 2
    ST_Intersects checks for spatial relation (returns BOOLEAN); ST_Intersection returns a geometry representing that spatial relation. but then, the intersection of a point with any other geometry is that point... – ThingumaBob Mar 27 at 8:36
  • @ThingumaBob please make that an answer – Ian Turton Mar 27 at 8:49
  • @IanTurton done. bit weird, though...with OPs own answer, however unfitting it might seem. I added a quick disclaimer. – ThingumaBob Mar 27 at 18:47

Yeah, after a long day one might not see the obvious anymore...:

  • ST_Intersects checks for the specific spatial relation, and returns BOOLEAN accordingly

  • ST_Intersection returns a GEOMETRY/GEOGRAPHY, representing that spatial relation, and is much rather what you are looking for


The intersection of a point with any other geometry is exactly that same point...

I'm writing this to adress the actual issue in your question, and don't take into account your (actually not quite fitting) answer...


Ok, it's been a long day. I meant to use st_pointonsurface. Which works just fine. I guess I had to ask the question to realize my mistake.

  • 2
    ST_PointOnSurface doesn't make any sense in the context of the provided code. – Vince Mar 27 at 2:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.