pg_query(): Query failed: ERROR: operator does not exist: integer ~~ unknown\n

Question

I'm new to database administration, but i need to migrate one web server on Debian 4.0 32bit to Debian Wheezy (7.6) 64bit and have some trouble that i can't fix with postgresql and php.

Old postgres is 8.1 new is 9.3.

Here is error from apache log:

PHP Warning:  pg_query(): Query failed: ERROR:  operator does not exist: integer ~~
 unknown\nLINE 1: SELECT * from sel_v_pc() WHERE pc_nomer LIKE '%' AND pccat_n...\n
 ^\nHINT:  No operator matches the given name and argument type(s). 
You might need to add explicit type casts. in /var/www/itweb/register/pc/pc.php on line 66

And here is the source:

        $default_sort = 'pc_nomer';
$allowed_order = array ('pc_nomer', 'structure_name', 'corpus_name', 'office_name', 'cputype_name', 'monitor_nomer', 'employee_name1', 'employee_name3'); 

if (!isset ($_GET['order']) || !in_array ($_GET['order'], $allowed_order)) {
    $order = $default_sort;
} else {
    $order = $_GET['order'];
}
if (!isset ($_GET['find'])) {
$query = "SELECT * from sel_v_pc() ORDER BY $order, pc_nomer";}
// ÒÚÐÑÅÍÅ
else {
    for($j = 0; $j < sizeof($_GET['finom']); $j++)
        {$finom = $_GET['finom'][$j];
        }
    for($j = 0; $j < sizeof($_GET['fpccat']); $j++)
        {$fpccat = $_GET['fpccat'][$j];
        }
    for($j = 0; $j < sizeof($_GET['fstruct']); $j++)
        {$fstruct = $_GET['fstruct'][$j];
        }
    for($j = 0; $j < sizeof($_GET['fustruct']); $j++)
        {$fustruct = $_GET['fustruct'][$j];
        }
    for($j = 0; $j < sizeof($_GET['fcorpus']); $j++)
        {$fcorpus = $_GET['fcorpus'][$j];
        }
    for($j = 0; $j < sizeof($_GET['foffice']); $j++)
        {$foffice = $_GET['foffice'][$j];
        }
    for($j = 0; $j < sizeof($_GET['fotype']); $j++)
        {$fotype = $_GET['fotype'][$j];
        }
    for($j = 0; $j < sizeof($_GET['fcpu']); $j++)
        {$fcpu = $_GET['fcpu'][$j];
        }
    for($j = 0; $j < sizeof($_GET['fcpufrecq']); $j++)
        {$fcpufrecq = $_GET['fcpufrecq'][$j];
        }
    for($j = 0; $j < sizeof($_GET['fram']); $j++)
        {$fram = $_GET['fram'][$j];
        }
    for($j = 0; $j < sizeof($_GET['fhdd']); $j++)
        {$fhdd = $_GET['fhdd'][$j];
        }
    for($j = 0; $j < sizeof($_GET['fcd']); $j++)
        {$fcd = $_GET['fcd'][$j];
        }       
    for($j = 0; $j < sizeof($_GET['fempl']); $j++)
        {$fempl = $_GET['fempl'][$j];
        }
    for($j = 0; $j < sizeof($_GET['orderby']); $j++)
        {$forder = $_GET['orderby'][$j];
        }       
    $query = "SELECT * from sel_v_pc() WHERE pc_nomer LIKE '$finom' AND pccat_name LIKE '$fpccat' AND structure_id LIKE '$fstruct' AND understructure_id LIKE '$fustruct' AND corpus_name LIKE '$fcorpus' AND office_name LIKE '$foffice' AND officetype_sign LIKE '$fotype' AND employee_id LIKE '$fempl' AND cputype_id LIKE '$fcpu' AND cpufrecq_ghz LIKE '$fcpufrecq' AND ram_mb LIKE '$fram' AND hdd_gb LIKE '$fhdd' AND cd_type LIKE '$fcd' ORDER BY $forder, pc_nomer";}

    $result = pg_query ( $query );

     $total = pg_num_rows ( $result );
?>

Craig Ringer · Accepted Answer · 2014-08-27 14:09:59Z

14

You can't use LIKE on an integer.

regress=# select 42 like '4%';
ERROR:  operator does not exist: integer ~~ unknown
LINE 1: select 42 like '4%';

You must cast the left operand to text if you truly want to pattern-match an integer:

regress=# select 42::text like '4%';
 ?column? 
----------
 t
(1 row)

but it's almost always better to use mathematical comparisons instead.

Edit after reviewing question: why on earth would you ever write LIKE '%' anyway? It's nonsensical. Fix your code generation so you don't do that.

answered Aug 27, 2014 at 14:09

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gep Sher · Accepted Answer · 2024-03-15 20:58:05Z

0

SELECT * from sel_v_pc() WHERE pc_nomer LIKE '%'

(PostgreSQL 15) -> Rewrite:

... WHERE CAST(pc_nomer AS text) LIKE 'your_RegEx';

or

... WHERE pc_nomer::text LIKE 'your_RegEx';

or

... WHERE CAST(pc_nomer AS varchar) ~ 'your_RegEx';

and so on

answered Mar 15, 2024 at 20:58

Gep Sher

274 bronze badges

Collectives™ on Stack Overflow

pg_query(): Query failed: ERROR: operator does not exist: integer ~~ unknown\n

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related