Postgres parameterized query with INSERT ON CONFLICT and geometry not working in PHP

Question

I do not know why this isn't working. Is it something about using parameters in the query twice? Using PHP 7.4.33, Postgres PostgreSQL 9.6.22.

Error is:

Warning: pg_query_params(): Query failed: ERROR:  inconsistent types deduced for parameter $2
LINE 11:                 , $2
                           ^
DETAIL:  double precision versus numeric in /home/douglass/redata/process.php on line 36

What could be the problem?

Here's what I did (I know lat/lon are in the row twice, once in a point, once in individual columns, but for the sake of the example, I would like to understand the error):

Created table:

(
  unique_id numeric,
  latitude numeric,
  longitude numeric,
  coordinates geometry(Point,4326)
);

Ran PHP:

    pg_query_params($connection, "
INSERT INTO test_table5
                (
                unique_id 
                , latitude
                , longitude
                , coordinates )
        VALUES (
        $1
                , $2
                , $3
                ,  ST_SetSRID(ST_MakePoint( $3, $2), 4326) )
        ON CONFLICT
                (unique_id)
        DO UPDATE SET
                latitude = $2
                , longitude = $3
                , coordinates =  ST_SetSRID(ST_MakePoint( $3, $2), 4326)",
                     [
                        50113947,
                        35.76673,
                        -78.36914
                     ]);

Answer 1

Postgres has a richer and stricter type system than PHP, so when you pass in any value, a decision needs to be made about what type it should be. Most of the time, you don't need to think about this, because Postgres deduces the correct type from the context where the value is used.

That goes some way to explaining this message:

inconsistent types deduced for parameter $2

Because you've used $2 in two different places, there are two different contexts which Postgres can use to deduce the right type; the problem is, it's come up with two different answers.

DETAIL: double precision versus numeric

We can see the context where numeric is the best choice: you have declared latitude numeric in your table, and then inserted $2 into that column.

That leaves ST_MakePoint( $3, $2). Looking up a manual page for the function confirms that its arguments are of type double precision.

To resolve the ambiguity, you can add casts to one or both contexts: CAST($2 AS numeric) for the table column and/or ST_MakePoint(CAST($3 AS float), CAST($2 AS float)) (float is an alias for double precision).

Answer 2

Sounds like Postgres complaining about double precision versus numeric. Given that your column is numeric the PHP bound column must be double precision. What happens when you use an explicit cast in SQL?

$2::numeric

Looking at the PHP documentation there is no representation of Postgres' numeric type in PDO.