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).
What steps will reproduce the problem?
Set emulatePrepare=true
in db component config
Execute query:
Model::find()->where(['title' => 1])->one()
Where title
column has type string
db=# select column_name, data_type from information_schema.columns where table_name = 'table';
column_name | data_type
----------------+-------------------
...
title | character varying
...
What is the expected result?
SQL Query:
SELECT * FROM "table" WHERE "title"='1'
What do you get instead?
SQL Query:
SELECT * FROM "table" WHERE "title"=1
And exception
yiidbException with message 'SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: character varying = integer
LINE 1: SELECT * FROM "table" WHERE "title"=1
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
The SQL being executed was: SELECT * FROM "table" WHERE "title"=1'
Every time explicitly indicate the type is inconvenient
What helped
https://github.com/yiisoft/yii2/blob/master/framework/db/Schema.php#L266
...
'integer' => PDO::PARAM_STR,
...
In overridden schema class
When searching by a string value on a numeric column, there is no error
Additional info
Q | A |
---|---|
Yii version | 2.0.14 |
PHP version | 7.2.3 |
Operating system | Debian GNU/Linux 9 |
PostgreSQL | 9.6.6 |
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).
Google Chrome 59.0.3071.115
Ubuntu 17.04
PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
- Metabase version: 0.25.1
- Metabase hosting environment: Docker
- Metabase internal database: H2
SELECT COUNT("order".order_id) AS sclr_0, TO_CHAR("order".created_at, 'YYYY-MM-DD') AS sclr_1 FROM "order" where {{created_at}} AND {{restaurant}} GROUP BY sclr_1;
restaurant variable -> Widget: id
ERROR: operator does not exist: integer = character varying Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 331
Logs
Jul 28 16:27:32 WARN metabase.query-processor :: {:status :failed,
:class java.lang.Exception,
:error «ERROR: operator does not exist: integer = character varyingn Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.n Position: 275»,
:stacktrace
[«driver.generic_sql.query_processor$do_with_try_catch.invokeStatic(query_processor.clj:349)»
«driver.generic_sql.query_processor$do_with_try_catch.invoke(query_processor.clj:345)»
«driver.generic_sql.query_processor$execute_query.invokeStatic(query_processor.clj:396)»
«driver.generic_sql.query_processor$execute_query.invoke(query_processor.clj:392)»
«driver$fn__22473$G__22466__22480.invoke(driver.clj:45)»
«query_processor$execute_query.invokeStatic(query_processor.clj:49)»
«query_processor$execute_query.invoke(query_processor.clj:43)»
«query_processor.middleware.mbql_to_native$mbql__GT_native$fn__26496.invoke(mbql_to_native.clj:30)»
«query_processor.middleware.annotate_and_sort$annotate_and_sort$fn__25019.invoke(annotate_and_sort.clj:41)»
«query_processor.middleware.limit$limit$fn__26451.invoke(limit.clj:14)»
«query_processor.middleware.cumulative_aggregations$cumulative_aggregation$fn__26299.invoke(cumulative_aggregations.clj:46)»
«query_processor.middleware.cumulative_aggregations$cumulative_aggregation$fn__26299.invoke(cumulative_aggregations.clj:46)»
«query_processor.middleware.format_rows$format_rows$fn__26442.invoke(format_rows.clj:21)»
«query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__27720.invoke(results_metadata.clj:88)»
«query_processor.middleware.resolve$resolve_middleware$fn__24627.invoke(resolve.clj:329)»
«query_processor.middleware.expand$expand_middleware$fn__26193.invoke(expand.clj:536)»
«query_processor.middleware.add_row_count_and_status$add_row_count_and_status$fn__24711.invoke(add_row_count_and_status.clj:14)»
«query_processor.middleware.driver_specific$process_query_in_context$fn__26319.invoke(driver_specific.clj:12)»
«query_processor.middleware.resolve_driver$resolve_driver$fn__27730.invoke(resolve_driver.clj:14)»
«query_processor.middleware.cache$maybe_return_cached_results$fn__25099.invoke(cache.clj:146)»
«query_processor.middleware.catch_exceptions$catch_exceptions$fn__26241.invoke(catch_exceptions.clj:58)»
«query_processor$process_query.invokeStatic(query_processor.clj:124)»
«query_processor$process_query.invoke(query_processor.clj:120)»
«query_processor$run_and_save_query_BANG_.invokeStatic(query_processor.clj:232)»
«query_processor$run_and_save_query_BANG_.invoke(query_processor.clj:227)»
«query_processor$fn__27764$process_query_and_save_execution_BANG___27769$fn__27770.invoke(query_processor.clj:270)»
«query_processor$fn__27764$process_query_and_save_execution_BANG___27769.invoke(query_processor.clj:256)»
«api.card$run_query_for_card.invokeStatic(card.clj:491)»
«api.card$run_query_for_card.doInvoke(card.clj:477)»
«api.card$fn__29925$fn__29928$fn__29929.invoke(card.clj:498)»
«api.card$fn__29925$fn__29928.invoke(card.clj:497)»
«api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:227)»
«api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:222)»
«api.card$fn__29925.invokeStatic(card.clj:493)»
«api.card$fn__29925.invoke(card.clj:493)»
«middleware$enforce_authentication$fn__28955.invoke(middleware.clj:122)»
«api.routes$fn__40133.invokeStatic(routes.clj:56)»
«api.routes$fn__40133.invoke(routes.clj:56)»
«routes$fn__40784$fn__40785.doInvoke(routes.clj:75)»
«routes$fn__40784.invokeStatic(routes.clj:71)»
«routes$fn__40784.invoke(routes.clj:71)»
«middleware$log_api_call$fn__29054$fn__29056.invoke(middleware.clj:331)»
«middleware$log_api_call$fn__29054.invoke(middleware.clj:330)»
«middleware$add_security_headers$fn__29004.invoke(middleware.clj:245)»
«middleware$bind_current_user$fn__28959.invoke(middleware.clj:142)»
«middleware$maybe_set_site_url$fn__29008.invoke(middleware.clj:268)»],
:query
{:type «native»,
:native
{:query «SELECT COUNT(«order».order_id) AS sclr_0, TO_CHAR(«order».created_at, ‘YYYY-MM-DD’) ASnsclr_1 FROM «order» where {{created_at}} [[AND {{restaurant}}]]nGROUP BY sclr_1;»,
:template_tags
{:created_at
{:id «431f6a38-455e-db5e-d862-59d6903b3242», :name «created_at», :display_name «Created at», :type «dimension», :required true, :dimension [«field-id» 166], :widget_type «date/range»},
:restaurant {:id «66ad5e09-0957-d8e2-d85f-30cab586024a», :name «restaurant», :display_name «Restaurant», :type «dimension», :dimension [«field-id» 181], :widget_type «id»}}},
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:parameters [{:type «id», :target [«dimension» [«template-tag» «restaurant»]], :value «6»}],
:cache_ttl nil,
:info
{:executed-by 1,
:context :question,
:card-id 3,
:dashboard-id nil,
:query-hash [-49, -69, -67, 38, 55, -79, -44, 54, 54, -72, -75, -20, 71, -14, 52, -66, -89, -106, -38, 7, 42, 16, 27, 25, 122, 123, 24, -84, 42, 71, -116, -126],
:query-type «native»}},
:expanded-query nil}
Jul 28 16:27:32 WARN metabase.query-processor :: Query failure: ERROR: operator does not exist: integer = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 275
[«query_processor$assert_query_status_successful.invokeStatic(query_processor.clj:201)»
«query_processor$assert_query_status_successful.invoke(query_processor.clj:194)»
«query_processor$run_and_save_query_BANG_.invokeStatic(query_processor.clj:233)»
«query_processor$run_and_save_query_BANG_.invoke(query_processor.clj:227)»
«query_processor$fn__27764$process_query_and_save_execution_BANG___27769$fn__27770.invoke(query_processor.clj:270)»
«query_processor$fn__27764$process_query_and_save_execution_BANG___27769.invoke(query_processor.clj:256)»
«api.card$run_query_for_card.invokeStatic(card.clj:491)»
«api.card$run_query_for_card.doInvoke(card.clj:477)»
«api.card$fn__29925$fn__29928$fn__29929.invoke(card.clj:498)»
«api.card$fn__29925$fn__29928.invoke(card.clj:497)»
«api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:227)»
«api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:222)»
«api.card$fn__29925.invokeStatic(card.clj:493)»
«api.card$fn__29925.invoke(card.clj:493)»
«middleware$enforce_authentication$fn__28955.invoke(middleware.clj:122)»
«api.routes$fn__40133.invokeStatic(routes.clj:56)»
«api.routes$fn__40133.invoke(routes.clj:56)»
«routes$fn__40784$fn__40785.doInvoke(routes.clj:75)»
«routes$fn__40784.invokeStatic(routes.clj:71)»
«routes$fn__40784.invoke(routes.clj:71)»
«middleware$log_api_call$fn__29054$fn__29056.invoke(middleware.clj:331)»
«middleware$log_api_call$fn__29054.invoke(middleware.clj:330)»
«middleware$add_security_headers$fn__29004.invoke(middleware.clj:245)»
«middleware$bind_current_user$fn__28959.invoke(middleware.clj:142)»
«middleware$maybe_set_site_url$fn__29008.invoke(middleware.clj:268)»]
The error org.postgresql.util.PSQLException: ERROR: operator does not exist: my_enum_type = character varying can be fixed by using implicit conversions in PostgreSQL or using explicit conversions by passing the java.sql.Types.OTHER to Spring JDBC.
The exception you will receive
When using Spring JDBC or Spring Data JDBC and custom Java enum types, you might run into the following problem:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT * FROM your_table where enum_column = :enum_value;] nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: my_enum_type = character varying Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
The exception can occur if you are having enums with either
- or you are trying to persist an Spring Data JDBC entity with a repository save method
- executing a query on your own with one of Spring JDBC’s jdbcTemplate methods like query
Making Spring Data JDBC’s save method work with enumerations
With Spring Data JDBC you have probably an SQL definition like
CREATE TYPE my_enum_type AS ENUM('VALUE_1','VALUE_2') CREATE TABLE my_table (enum_column my_enum_type);
The belonging Java source code will look like
public enum MyEnumType { VALUE_1, VALUE_2 } @Table("my_table") public class MyEntity { private MyEnumType type; // getter & setter ... } @Repository public class MyTableRepository extends CrudRepository</* ... */> { } @Controller public class MyTableController { @Autowired MyTableRepository @GetMapping("/") public void save() { MyEntity entity = new MyEntity(); entity.setType(MyEnumType.VALUE_1); repository.save(entity); } }
As soon as you try to call repository.save you will receive an exception. The problem is, that Spring Data JDBC does not support enumerations at the moment. See Jens Schauder’s answer at stackoverflow.
Jens does also link to another SO answer which describes how to solve the problem. To make the code sample above working, we can use PostgreSQL’s implicit conversion feature as described in the linked answer. The following SQL definition would reside somewhere in your Liquibase or Flyway migration definition:
<!-- Liquibase migration definition --> <!-- as described above --> <sql>CREATE TYPE my_enum_type AS ENUM('VALUE_1','VALUE_2')</sql> <!-- add an additional type --> <sql>CREATE CAST (varchar AS my_enum_type) WITH INOUT AS IMPLICIT</sql>
With help of the described CREATE CAST PostgreSQL will automatically try convert each String/varchar into the specified enum. You can now do something like
SELECT * FROM my_table WHERE enum_column = 'VALUE_1'; -- OR INSERT INTO my_table(enum_column) VALUES('VALUE_1')
After that, repository.save() will work.
Using JdbcTemplate and enumerations
You might think that the specified CREATE CAST definition would also work for something like that:
public class MyRepository { @Autowired NamedParameterJdbcTemplate jdbcTemplate; public List<String> findAll() { MapSqlParameterSource parameters = new MapSqlParameterSource("type", MyEnumType.VALUE_1); return jdbcTemplate .queryForList("SELECT enum_column FROM my_table WHERE enum_column = :type", parameters); } }
But this will drive you right into the exception you find at the beginning:
org.postgresql.util.PSQLException: ERROR: operator does not exist: my_enum_type = character varying
The reason for this behavior is how PostgreSQL handles the type casts. This Stackoverflow answer describes the reasons in detail. Spring JDBC does automatically convert the enum value into a Java String type and assigns it to the prepared statement. The underlying PgJDBC drivers then assigns the java.sql.Type.VARCHAR as type for this prepared statement parameter. Due to the assignment of the java.sql.Type, PostgreSQL will no longer try to apply our CREATE CAST conversion.
Solving it by configuration
You can configure your JDBC URL to use the parameter stringtype with value undefined. Each String parameter previously set with setString() will then not have the type definition java.sql.Type.VARCHAR. PostgreSQL applies will then apply our CREATE CAST definition.
Solving it programatically
If you don’t globally want to set stringtype to undefined, you have to use the java.sql.Types.OTHER when adding a value to the MapSqlParameterSource:
// does NOT WORK: // .addValue("type", MyEnumType.VALUE_1)) // one of the following does work: .addValue("type", "VALUE_1", java.sql.Types.OTHER) // or .addValue("type", MyEnumType.VALUE_1.getName(), java.sql.Types.OTHER) // or .addValue("type", MyEnumType.VALUE_1, java.sql.Types.OTHER)
To make it more convenient, you can extend from MapSqlParameterSource to get something like this:
public static class CustomMapSqlParameterSource extends MapSqlParameterSource { public CustomMapSqlParameterSource addEnum(String paramName, Object value) { if (!value.getClass().isEnum()) { throw new IllegalArgumentException("Given parameter is not of Java type enum"); } addValue(paramName, value, java.sql.Types.OTHER); return this; } }
Wrapping it up
This blog post showed you, how you can use native PostgreSQL enumerations with native Java and make them both work with Spring Data JDBC and Spring JDBC.
I have a product
table as follows;
And the fields are as follows;
As you can see, aside from id
, order_id
is integer and the rest are varchars.
When I execute the following query, it gives me the error that I cannot compare integer and varchar in order_id field.
private final static String removeAlternativeProductQuery = "DELETE FROM product where order_id=? AND product_id='?' AND alternative_product_id='?'";
public void removeAlternativeProduct(AlternativeProduct alternativeProduct){
Query query = em.createNativeQuery(removeAlternativeProductQuery);
query.setParameter(1, (Integer) alternativeProduct.getOrderId());
query.setParameter(2, alternativeProduct.getProductId());
query.setParameter(3, alternativeProduct.getAlternativeProductId());
query.executeUpdate();
}
Parallel with the database, orderId
is Integer and the rest are String in my object. So why am I getting this cast error?
To make it more clear, in the query, where order_id=?
, the left side is integer. And the right side is taken from
query.setParameter(1, (Integer) alternativeProduct.getOrderId());
which is also an integer. So where is the character varying error here??
The complete error;
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.1.v20121003-ad44345): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 54
Error Code: 0
Call: DELETE FROM product where order_id=? AND product_id='?' AND alternative_product_id='?'
bind => [1 parameter bound]
Query: DataReadQuery(sql="DELETE FROM product where order_id=? AND product_id='?' AND alternative_product_id='?'")
I have a product
table as follows;
And the fields are as follows;
As you can see, aside from id
, order_id
is integer and the rest are varchars.
When I execute the following query, it gives me the error that I cannot compare integer and varchar in order_id field.
private final static String removeAlternativeProductQuery = "DELETE FROM product where order_id=? AND product_id='?' AND alternative_product_id='?'";
public void removeAlternativeProduct(AlternativeProduct alternativeProduct){
Query query = em.createNativeQuery(removeAlternativeProductQuery);
query.setParameter(1, (Integer) alternativeProduct.getOrderId());
query.setParameter(2, alternativeProduct.getProductId());
query.setParameter(3, alternativeProduct.getAlternativeProductId());
query.executeUpdate();
}
Parallel with the database, orderId
is Integer and the rest are String in my object. So why am I getting this cast error?
To make it more clear, in the query, where order_id=?
, the left side is integer. And the right side is taken from
query.setParameter(1, (Integer) alternativeProduct.getOrderId());
which is also an integer. So where is the character varying error here??
The complete error;
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.1.v20121003-ad44345): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 54
Error Code: 0
Call: DELETE FROM product where order_id=? AND product_id='?' AND alternative_product_id='?'
bind => [1 parameter bound]
Query: DataReadQuery(sql="DELETE FROM product where order_id=? AND product_id='?' AND alternative_product_id='?'")
здесь я пытаюсь создать представление, как показано ниже в Примере:
пример:
create view view1
as
select table1.col1,table2.col1,table3.col3
from table1
inner join
table2
inner join
table3
on
table1.col4 = 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 */
....;
Примечание: тот же запрос выполняется в sql server, но получает вышеуказанную ошибку в postgreSQL.
1 ответов
Я думаю, что это говорит вам, что именно не так. Нельзя сравнивать целое число с varchar. PostgreSQL является строгим и не делает никакой магии для вас. Я предполагаю, что SQLServer делает типизацию автоматически (что плохо).
Если вы хотите сравнить этих двух разных зверей, вам придется бросить один на другой, используя синтаксис литья ::
.
что-то в этом роде:
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 */
....;
уведомления varchar
типажей на таблица1.col4.
также обратите внимание, что типизация может сделать ваш индекс в этом столбце непригодным для использования и имеет штраф за производительность, что довольно плохо. Еще лучшим решением было бы посмотреть, можно ли навсегда изменить один из двух типов столбцов в соответствии с другим. Грамотно измените дизайн базы данных.
или вы можете создать индекс для литых значений, используя пользовательский,неизменяемые функция, которая отбрасывает значения в столбце. Но это тоже может оказаться неоптимальным (но лучше, чем живой кастинг).
The Solution to PostgreSQL: ERROR: operator does not exist: integer = character varying is
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).
~ Answered on 2014-08-18 07:19:52
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
51.5k13 gold badges149 silver badges198 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,1747 gold badges38 silver badges55 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
596k144 gold badges1058 silver badges1215 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
126k24 gold badges156 silver badges133 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
будет целым числом, но похоже, что он говорит вам, что это на самом деле варчар.
Я бы попытался удалить столбец и добавить его снова.