Postgres SQLSTATE [42725]: Ambiguous function

I'm using Laravel 5 and my query was:

DB::update("UPDATE bemed_produto.categoria_departamento SET left_categoria = (left_categoria + ?) WHERE left_categoria >= (CASE WHEN ? > ? THEN  ? - ?  ELSE ? END) AND (parent = ? OR fk_codigo_categoria = ?)", [$width, $destino, $right, $destino, $width, $width, $parent, $parent ]);

I could solve concatenating the variables directly instead of using ? .

I was able to simulate the error and get the same SQL state 42725 , by default laravel uses non-emulated prepared statements, what PDO driver does is to send all values as strings, so error occurs the postgres expect integers in your query however strings are passed.

The error generated is:

  

ERROR: operator is not unique: unknown - unknown

     

SQL state: 42725

     

Hint: You could not choose an operator that fits best. You need to add explicit type conversions.

Error Simulation

Example - sqlfiddle

To simulate the error is enough a simple query,

To get the error without a prepared query, the test is done directly in pgAdmin or phpPgAdmin with this query or similiar (other operators)

SELECT '1'- '1'

PHP

<?php
    $options = array(PDO::ATTR_EMULATE_PREPARES => false);
    $db = new PDO('pgsql:host=localhost;dbname=postgres user=usuario password=senha', $options);

    $sql = "SELECT ? + ?";
    $stmt = $db->prepare($sql);
    $stmt->execute(array(10,5));
    $res = $stmt->fetch(PDO::FETCH_ASSOC);

    echo '<pre>';
    print_r($res);

Solution

Change the option PDO::ATTR_EMULATE_PREPARES to true , this setting is set in class Illuminate\Database\Connectors\Connector and PostgresConnector inherits it. In laravel5.1 , open the laravel\framework\src\illuminate\database\connectors folders %.

Original configuration:

protected $options = [
    PDO::ATTR_CASE => PDO::CASE_NATURAL,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
    PDO::ATTR_STRINGIFY_FETCHES => false,
    PDO::ATTR_EMULATE_PREPARES => false,
];

or cast them in the placeholders, as below.

$sql = 'select ?::int - ?::int as resultado';

//teste do pgAdmin

SELECT '10'::int - '5'::int as resultado';

What you have put of your select is missing closing the last parentheses.

Current:

UPDATE produto.categoria_departamento SET left_categoria = (left_categoria + 
10) WHERE (left_categoria >= CASE WHEN 2 > 27 THEN  2 - 10 ELSE 2 END) AND 
(parent = 46 OR fk_codigo_categoria = 46

Closing relatives:

UPDATE produto.categoria_departamento SET left_categoria = (left_categoria + 
10) WHERE (left_categoria >= CASE WHEN 2 > 27 THEN  2 - 10 ELSE 2 END) AND 
(parent = 46 OR fk_codigo_categoria = 46) -- <<<<

Does this solve your problem?