I think it is telling you exactly what is wrong. You cannot compare an integer with a varchar. PostgreSQL is strict and does not do any magic typecasting for you. I’m guessing SQLServer does typecasting automagically (which is a bad thing).
If you want to compare these two different beasts, you will have to cast one to the other using the casting syntax ::.
Something along these lines:
create view view1
as
select table1.col1,table2.col1,table3.col3
from table1
inner join
table2
inner join
table3
on
table1.col4::varchar = table2.col5
/* Here col4 of table1 is of "integer" type and col5 of table2 is of type "varchar" */
/* ERROR: operator does not exist: integer = character varying */
....;
Notice the varchar typecasting on the table1.col4.
Also note that typecasting might possibly render your index on that column unusable and has a performance penalty, which is pretty bad. An even better solution would be to see if you can permanently change one of the two column types to match the other one. Literately change your database design.
Or you could create a index on the casted values by using a custom, immutable function which casts the values on the column. But this too may prove suboptimal (but better than live casting).
Reported by nbe-renzel-net on 26 Sep 2017 09:41
Version: 2017.08.5 Community Edition
PostgreSQL 9.5.7 has strict types, so checking an «integer» against a «varchar» key doesn’t work:
ERROR: operator does not exist: character varying = integer at character 71
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
STATEMENT: SELECT «tine20_cal_events».»uid» FROM «tine20_cal_events» WHERE («id» IN (‘2c17cde7d11f2eb119d92dc6e3b3cc594c3d9f12’, ‘b67a93e60c07564e7692cc654e585827be8f0e42’, ‘5de07cb1950411e2eb331bc2b34df2ad61d09643′, ’21da540547877eee9372f84de548ee936c85c9c2’, ‘7fe603af75b7509714fa7fb8012366661a9a7f51′, ’89af8519-5573-4807-a79e-1da16edac346’, 1490091065, 1490278238, 1490356832, ‘b040ab9d1bc7fb6c229d211bd3d501b82b47d7e0’, ‘fe4c877f2a64f8173b3de97d4216ce646a8f4253’, ‘56164bc4365b6ce6ff4c6e69d7b0e023bd48ee42’, ‘9dc32661840b83c9236b22e00fd18cad42e69998’, ‘20170327T163101Z-2d1ab9fadb1e70d1’, ‘2ed067d70a52eba2a899b072488860239e7a5cbf’, ‘694ad70acc5e82d8fc1bf0f4a781acc41095e4d1’, ‘731e94f5ed58a9718072ff9799fa61e5c0c75d4b’, ‘d6edeec3877b69e49289d4047d4df0e15828d720′, ’15b032b3acb64a348b35759bba562f2436ef8e14’, ‘0db8f5f84c48b8e09f3052477d7583e23e1268a6’, ‘4188f7593ea22a3333057b6b31c228c7f1471450′, ’07a8e05ca0dbc60e774cc246538cf8d7a371a3b7’, ‘412c2263294d19fae76a070783fae9d698ae2187’) AND «recurid» IS NULL)
In order to circumvent that, I modified Zend DB Abstract «_quote» functions to put everything in quotes:
diff -u -r orig/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php modded/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php
— orig/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php 2017-09-21 13:51:57.000000000 +0200
+++ modded/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Abstract.php 2017-09-25 10:30:31.531928000 +0200
@@ -838,11 +838,11 @@
*/
protected function _quote($value)
{
+/* if (is_int($value)) {
return $value;
} elseif (is_float($value)) {
return sprintf(‘%F’, $value);
-
} */ return "'" . addcslashes($value, "\000\n\r\\'\"\032") . "'";}
@@ -902,7 +902,6 @@
}
return $quotedValue;
}
return $this->_quote($value);
}
diff -u -r orig/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php modded/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php
— orig/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php 2017-09-21 13:51:57.000000000 +0200
+++ modded/vendor/zendframework/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php 2017-09-21 11:57:10.904654000 +0200
@@ -289,9 +289,9 @@
*/
protected function _quote($value)
{
-
if (is_int($value) || is_float($value)) {
+/* if (is_int($value) || is_float($value)) {
return $value;
-
} */ $this->_connect(); return $this->_connection->quote($value);}
So I keep getting the title error. The string I am using to create the query is,
select p from Product p where p.productType.productTypeId in (:productTypeIds)
And here is a clip of the java
List<Long>partTerminologyIds = getProducTypeds(partTerminologys);
..........................................................................
query.setParameter("partTerminologyIds", productTypeIds);
I have no idea why I am getting this error, ane yes partTerminolgyId in my database is a numeric 18.
Any ideas???
eis
52.1k13 gold badges150 silver badges199 bronze badges
asked Feb 9, 2012 at 20:22
1
So in the end it ended up being that a «foreign key» was a string in one table and an numeric in the other. I rebuilt the database with new scripts and did not reverse engineer the new database.
answered Feb 13, 2012 at 17:42
LandisterLandister
2,1947 gold badges38 silver badges56 bronze badges
This query is invalid:
select p from Product p where p.productType.productTypeId in (:productTypeIds)
Do you mean:
SELECT p FROM product p WHERE p.productTypeId IN (:productTypeIds)
Or rather:
SELECT * FROM product p WHERE p.productTypeId IN (:productTypeIds)
And if so, what is the data type of productTypeId in your query. Please clarify.
answered Feb 10, 2012 at 0:56
Erwin BrandstetterErwin Brandstetter
608k145 gold badges1083 silver badges1232 bronze badges
1
This looks like a Hibernate query. You need to do query.setParameterList to specify a collection value, otherwise Hibernate won’t know to expand out :productTypeIds to a list of placeholders instead of simply binding the list as a serializable blob (which I think is an awful default).
answered Feb 9, 2012 at 21:15
araqnidaraqnid
127k24 gold badges158 silver badges134 bronze badges
2
Я сталкиваюсь с общей проблемой. Приложение My Rails работает на моей локальной машине, но после развертывания в heroku он сбой:
<% unless @user.hotels.empty? %>
<% @user.hotels.each do |hotel| %>
<%= "#{hotel.description} #{hotel.name} in #{hotel.city}, #{hotel.country}" %><br />
<% end %>
<% end %>
Это из журналов heroku:
ActionView::Template::Error (PGError: ERROR: operator does not exist: character varying = integer
LINE 1: SELECT "hotels".* FROM "hotels" WHERE ("hotels".user_id = 1)
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "hotels".* FROM "hotels" WHERE ("hotels".user_id = 1)):
@user.hotels.empty? создает ошибку. Я знаю, что sqlite довольно прощает, но PostgreSQL — нет. Это внешний ключ в модели отеля: user_id :integer
Героку говорит:
Make sure the operator is adequate for the data type. ActiveRecord does this automatically when you use an interpolated condition form.
Array conditions:
:conditions => ['column1 = ? AND column2 = ?', value1, value2]
Hash conditions:
:conditions => { :column1 => value1, :column2 => value2 }
Миграция выглядит следующим образом:
class CreateHotels < ActiveRecord::Migration
def self.up
create_table :hotels do |t|
t.string :name
t.string :vanity_url
t.integer :user_id
....
Ответ 1
Это обычно происходит, когда внешний ключ в базе данных не является целым числом.
Например:
LINE 1: SELECT "hotels".* FROM "hotels" WHERE ("hotels".user_id = 1)
^
В этом случае PostgreSQL ожидает, что user_id будет целым числом, но похоже, что он говорит вам, что это на самом деле варчар.
Я бы попытался удалить столбец и добавить его снова.
If you have ever encountered the PostgreSQL error message “operator does not exist: integer = character varying”, then you know how frustrating it can be. This error message is usually caused by trying to compare a numeric column with a character column in the WHERE clause of a SQL query.
Fortunately, there are a few ways to fix this error. In this guide, we will walk through some code examples to demonstrate each solution.
Solution 1: Cast the character column to an integer
The easiest way to fix this error is to cast the character column to an integer. This can be done using the `CAST()` function. Here’s an example:
SELECT *
FROM my_table
WHERE my_numeric_column = CAST(my_character_column AS INTEGER);
In this example, we are casting the `my_character_column` to an integer using the `CAST()` function. This allows us to compare the character column with the numeric column in the WHERE clause.
Solution 2: Cast the integer column to a character
Another solution is to cast the integer column to a character. This can be done using the `CAST()` function as well. Here’s an example:
SELECT *
FROM my_table
WHERE CAST(my_numeric_column AS VARCHAR) = my_character_column;
In this example, we are casting the `my_numeric_column` to a character using the `CAST()` function. This allows us to compare the numeric column with the character column in the WHERE clause.
Solution 3: Use the :: operator
A third solution is to use the `::` operator to cast the character column to an integer. Here’s an example:
SELECT *
FROM my_table
WHERE my_numeric_column = my_character_column::INTEGER;
In this example, we are using the `::` operator to cast the `my_character_column` to an integer. This allows us to compare the character column with the numeric column in the WHERE clause.
Conclusion
In this guide, we have discussed three solutions to the PostgreSQL error message “operator does not exist: integer = character varying”. By casting either the character column to an integer or the integer column to a character, or by using the `::` operator, we can compare numeric and character columns in the WHERE clause of a SQL query without encountering this error.