Error operator does not exist integer text

Числа не всегда хранятся в базе данных как числа. Они могут находиться внутри текста. Посмотри на time_offset таблицы timezone:

SELECT *
  FROM timezone

Например, мы хотим получить все часовые зоны со смещением относительно UTC не более 5 часов.

Разобьем задачу на части:

1. Получим сдвиг относительно UTC числом;
2. Ограничим 5 часами.

Получим сдвиг относительно UTC числом

Мы знаем, что в поле time_offset данные хранятся в формате UTC+СДВИГ_В_ЧАСАХ. Возьмем подстроку, начиная с 5-го символа:

SELECT t.timezone_id,
       t.time_offset,
       right(t.time_offset, -4) AS hours
  FROM timezone t

Ограничим 5 часами

Получив сдвиг в часах, мы сделали почти всю работу. Осталось лишь добавить WHERE right(t.time_offset, -4) <= 5:

SELECT t.timezone_id,
       t.time_offset,
       right(t.time_offset, -4) AS hours
  FROM timezone t
 WHERE right(t.time_offset, -4) <= 5

error: operator does not exist: text <= integer

Упс… Что-то пошло не так…

Postgres говорит, что нельзя сравнивать текст с числом.

Для исправления ошибки нужно преобразовать подстроку, полученную right(t.time_offset, -4), к типу данных integer (целые числа). Сделать это можно одним из способов:

• использовав конструкцию ::
• вызвав функцию cast()

Конструкция ::

Чтобы преобразовать значение к другому типу данных достаточно после значения поставить :: и написать требуемый тип данных. Например:

SELECT '123'::integer

Посмотрим на типы данных в нашем запросе:

SELECT pg_typeof (t.time_offset) AS "type time_offset",
       pg_typeof (right(t.time_offset, -4)) AS "type number substring",
       pg_typeof (right(t.time_offset, -4)::integer) AS "type with ::integer",
       pg_typeof ('12'::integer) AS "type '12'::integer",
       pg_typeof (current_timestamp) AS "type current_timestamp"
  FROM timezone t
 LIMIT 1

Доделаем работу до конца

SELECT t.timezone_id,
       t.time_offset,
       right(t.time_offset, -4)::integer AS hours
  FROM timezone t
 WHERE right(t.time_offset, -4)::integer <= 5
 ORDER BY hours

Функция cast

Функция cast делает ровно то же самое. Синтаксис

cast (значение AS тип данных)

Например:

SELECT cast('123' AS integer)

SELECT t.timezone_id,
       t.time_offset,
       cast(right(t.time_offset, -4) AS integer) AS hours
  FROM timezone t
 WHERE cast(right(t.time_offset, -4) AS integer) <= 5
 ORDER BY hours

P.S. Функция cast включена в стандарт SQL и доступна в СУБД других производителей. Синтаксис через :: является историческим для PostgreSQL. На практике чаще используют ::, потому что так короче.

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

There’s a very frequent issue when upgrading to version 8.3 and bellow: the
removal of some implicit casts from text types to time or numerical ones in
8.3. The only clean solution here is to fix the application itself, period.
However, for those that cannot afford quickly such a hard work, the popular
workaround is to recreate these implicit casts, but it suffer from a side
effect. About a year ago, I found another quick-n-dirty fix for a customer.

Here is the problem:

casts=# CREATE TABLE test AS SELECT generate_series(1,10) as id;
SELECT
casts=# SELECT id, 'value = ' || id FROM test WHERE id = '5'::text;
ERROR:  operator does not exist: integer = text
LINE 1 : SELECT id, 'value = ' || id FROM test WHERE id = '5'::text;
                                                         ^
TIPS : No operator matches the given name and argument type(s). You might need to add explicit type casts.

The very well known solution is to recreate some of these implicit casts that
were removed in 8.3. Peter Eisentraut blogged about that, you’ll find his SQL
script
here.

However, as some users noticed in the comments, there is a side effect bug with
this solution: it breaks the concatenation operator.

casts=# BEGIN ;
BEGIN
casts=# i /tmp/implicit_casts.sql
CREATE FUNCTION
CREATE CAST
-- [...]
CREATE FUNCTION
CREATE CAST
casts=# SELECT id, 'value = ' || id FROM test WHERE id = '5'::text;
ERROR:  operator is not unique: unknown || integer
LINE 1 : SELECT id, 'value = ' || id FROM test WHERE id = '5'::text;
                                ^
TIPS : Could not choose a best candidate operator. You might need to add explicit type casts.
casts=# ROLLBACK ;
ROLLBACK

From here, the solution could be to cast one of the operand:

casts=# SELECT id, 'value = ' || id::text FROM test WHERE id = '5'::text;
  5 | value = 5

But then, we are back to the application fix where it might worth spending more
time fixing things in the good way.

There is another solution: creating missing operators instead of implicit
casts. You will find a sql file with a lot of those operators under the
following link: 8.3 operator workaround.sql.
Here is a sample for text to integer comparison:

CREATE FUNCTION pg_catalog.texteqint(text, integer) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int4out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.inteqtext(integer, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int4out($1)) = $2;$$;
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqint, LEFTARG=text, RIGHTARG=integer, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.inteqtext, LEFTARG=integer, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));

Using this operator instead of implicit cast, the previous test shows:

casts=# BEGIN ;
BEGIN
casts=# i '/tmp/8.3 operator workaround.sql'
CREATE FUNCTION
-- [...]
CREATE FUNCTION
CREATE OPERATOR
-- [...]
CREATE OPERATOR
casts=# SELECT id, 'value = ' || id FROM test WHERE id = '5'::text;
  5 | value = 5

casts=# -- what, you don't trust me :) ?
casts=# ROLLBACK ;
ROLLBACK
casts=# SELECT id, 'value = ' || id FROM test WHERE id = '5'::text;
ERROR:  operator does not exist: integer = text
LINE 1 : SELECT id, 'value = ' || id FROM test WHERE id = '5'::text;
                                                         ^
TIPS : No operator matches the given name and argument type(s). You might need to add explicit type casts.

Same advice from Peter here: if possible, only create the operators you need to
fix your application!

So far, I only had one positive feedback about this workaround about a year
ago, and I don’t consider this is enough to actually claim it is a safe
solution. So please, comments, tests and reports are welcome!

Again, keep in mind that the only clean way is fix your application if you hit
this problem!