Org postgresql util psqlexception ошибка оператор не существует integer character varying

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)»]

Перейти к контенту

select order_number, order_status_name
from data.order_fact s
join data.order_status_dim l
on s.order_status_key = l.order_status_key
where 
order_number in (1512011196169,1512011760019,1512011898493,1512011972111)

ERROR:  operator does not exist: character varying = bigint
LINE 6: order_number in (1512011196169,1512011760019,1512011898493,1...
                     ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

select * from numbers_as_string
where cast(my_numbers_as_string as int) in (1,2,3)

       @Query(value=" 
        ......
    .....
         inner join tablex t on t.x::integer = pv.id n"+
....
....
        ")
         List<Object> getMyQuery(@Param("x") String x)

select *
from my_table
where column_one=${myValue}

[42883] ERROR: operator does not exist: character varying = bigint Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

select *
from my_table
where column_one='123'

public class PostgresJdbcMutableAclService extends JdbcMutableAclService {
        //copy of this JdbcMutableAclService.selectObjectIdentityPrimaryKey
        private String selectObjectIdentityPrimaryKey = "select acl_object_identity.id from acl_object_identity, acl_class "
                + "where acl_object_identity.object_id_class = acl_class.id and acl_class.class=? "
                + "and acl_object_identity.object_id_identity = ?";

        public PostgresJdbcMutableAclService(DataSource dataSource, LookupStrategy lookupStrategy, AclCache aclCache) {
            super(dataSource, lookupStrategy, aclCache);
        }
        
        @Override
        protected Long retrieveObjectIdentityPrimaryKey(ObjectIdentity oid) {
            try {
                return (Long) this.jdbcTemplate.queryForObject(this.selectObjectIdentityPrimaryKey, Long.class, new Object[]{oid.getType(), "" + oid.getIdentifier()});
            } catch (DataAccessException var3) {
                return null;
            }
        }

        @Override
        public List<ObjectIdentity> findChildren(ObjectIdentity parentIdentity) {
            Object[] args = new Object[]{"" + parentIdentity.getIdentifier(), parentIdentity.getType()};
            List<ObjectIdentity> objects = this.jdbcTemplate.query(this.findChildrenSql, args, new RowMapper<ObjectIdentity>() {
                public ObjectIdentity mapRow(ResultSet rs, int rowNum) throws SQLException {
                    String javaType = rs.getString("class");
                    Serializable identifier = (Serializable) rs.getObject("obj_id");
//                    identifier = JdbcAclService.this.aclClassIdUtils.identifierFrom(identifier, rs);
                    return new ObjectIdentityImpl(javaType, identifier);
                }
            });
            return objects.size() == 0 ? null : objects;
        }
}

    @Bean
    public JdbcMutableAclService aclService() {
        JdbcMutableAclService jdbcMutableAclService = new JdbcMutableAclService(dataSource, lookupStrategy(), aclCache());

        // from documentation
        jdbcMutableAclService.setClassIdentityQuery("select currval(pg_get_serial_sequence('acl_class', 'id'))");
        jdbcMutableAclService.setSidIdentityQuery("select currval(pg_get_serial_sequence('acl_sid', 'id'))");

        // additional adjustments
        jdbcMutableAclService.setObjectIdentityPrimaryKeyQuery("select acl_object_identity.id from acl_object_identity, acl_class where acl_object_identity.object_id_class = acl_class.id and acl_class.class=? and acl_object_identity.object_id_identity = cast(? as varchar)");
        jdbcMutableAclService.setFindChildrenQuery("select obj.object_id_identity as obj_id, class.class as class from acl_object_identity obj, acl_object_identity parent, acl_class class where obj.parent_object = parent.id and obj.object_id_class = class.id and parent.object_id_identity = cast(? as varchar) and parent.object_id_class = (select id FROM acl_class where acl_class.class = ?)");

        return jdbcMutableAclService;
    }

>  [notice] Update started: paragraphs_post_update_rebuild_parent_fields
>  [error]  SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: character varying <> bigint
> LINE 5: WHERE (((p.parent_id <> f.entity_id)) OR (p.parent_type <> '...
>                              ^
> HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.: SELECT f.entity_id AS entity_id, f.field_paragraph_target_revision_id AS field_paragraph_target_revision_id, p.revision_id AS prevision_id
> FROM
> {paragraph_revision__field_paragraph} f
> INNER JOIN {paragraphs_item_revision_field_data} p ON f.field_paragraph_target_revision_id = p.revision_id
> WHERE (((p.parent_id <> f.entity_id)) OR (p.parent_type <> :db_condition_placeholder_0) OR (p.parent_field_name <> :db_condition_placeholder_1)) AND ((p.langcode = f.langcode))
> ORDER BY p.revision_id ASC NULLS FIRST
> LIMIT 100 OFFSET 0; Array
> (
>     [:db_condition_placeholder_0] => paragraph
>     [:db_condition_placeholder_1] => field_paragraph
> )
>
>  [error]  Update failed: paragraphs_post_update_rebuild_parent_fields
>  [error]  Update aborted by: paragraphs_post_update_rebuild_parent_fields

{
  "data_source_id": "5ec29e7c53e7610001d5f1be",
  "id": "4",
  "sync_date": "2020-06-25T00:00:00",
  "week": "3"
}

{
  "data_source_id": "5ec29e7c53e7610001d5f1be",
  "id": 4,
  "sync_date": "2020-06-25T00:00:00",
  "week": 3
}

SELECT week FROM my_table WHERE id::int=4

 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 */
 ....;

This question is related to
postgresql
casting
integer
varchar

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).

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

1. or you are trying to persist an Spring Data JDBC entity with a repository save method
2. 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.