Postgres SQLSTATE[42725]: Ambiguous Function

Question

I am trying to perform the update to follow using Postgres but I am coming across the following message. Does anyone know a way to resolve?

"SQLSTATE[42725]: Ambiguous Function: 7 ERROR: operator is not unique: Unknown - Unknown"

UPDATE produto.categoria_departamento SET left_categoria = (left_categoria + ".$width.") WHERE (left_categoria >= CASE WHEN ".$destino." > ".$right." THEN  ".$destino." - ".$width." ELSE ".$destino." END) AND (parent = ".$parent." OR fk_codigo_categoria = ".$parent.")

When I echo in sql get: 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)

Answer 1

I am 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 was able to solve by concatenating the direct variables instead of using ?.

Answer 2

I managed to simulate the error and get the same SQL state 42725, by default the Standard uses emulated Prepared statements, what PDO driver does is to send all values as strings, so the error happens postgres expect integers in your query but strings are passed.

The error generated is:

ERROR: operator is not unique: Unknown - Unknown

SQL state: 42725

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

Feint of error

Example - sqlfiddle

To simulate the error just a very simple query,

To obtain 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 for true, this setting is set in the class Illuminate\Database\Connectors\Connector and PostgresConnector inherits her. In the laravel5.1, open the folderslaravel\framework\src\illuminate\database\connectors.

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 (type) in placeholders, as below.

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

//teste do pgAdmin

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

Answer 3

By what you put from your select is missing close 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) -- <<<<

That solves your problem?