function st_intersects(bytea, bytea) is not unique

Question

When trying to combine two tables and export the result to a csv file, I am getting this error i.e. function st_intersects(bytea, bytea) is not unique.

Answer 1

The problem is that you use the wrong data type (bytea) for your geometrical data.

PostgreSQL can convert between bytea and geometry / geography implicitly:

\dC bytea
                         List of casts
   Source type   | Target type |      Function      | Implicit? 
-----------------+-------------+--------------------+-----------
 bytea           | geography   | geography          | yes
 bytea           | geometry    | geometry           | yes
 geography       | bytea       | bytea              | yes
 geometry        | bytea       | bytea              | yes
[...]
(7 rows)

Now there are three functions st_intersects:

\df st_intersects
                                  List of functions
 Schema |     Name      | Result data type |       Argument data types        | Type 
--------+---------------+------------------+----------------------------------+------
 public | st_intersects | boolean          | geog1 geography, geog2 geography | func
 public | st_intersects | boolean          | geom1 geometry, geom2 geometry   | func
 public | st_intersects | boolean          | text, text                       | func
(3 rows)

Now following its function type resolution rules, PostgreSQL has no way to determine if you want the first or the second of these functions, which causes the error message.

Solutions:

1. Use the appropriate data type (geometry or geography) in your table definitions.

2. Use an explicit cast, like the error message tells you:

   st_intersects(CAST (g1 AS geometry), CAST (g2 AS geometry))
   

I think it was a bad choice of PostGIS to make both casts implicit.