ERROR: Operator does not exist: character varying = integer

Problem

You created an SQL insight and when you execute it (or save it and view it) you get an error message stating: ERROR: Operator does not exist: character varying = integer

Cause

The above error means that somewhere in your insight you are trying to compare a text string to a numeric value. SQL can't directly recognize the text as number, thus the comparison is not possible.

You can easily identify if that's the case if you do a SELECT statement for the fields you want to use in your insight and observe their output by clicking the Execute button in your insight editor. If the value is surrounded in quotation marks, then it's coming through as a text. If it's not, then it's numeric.

Here are two examples:

In the below example, the values of the id and week fields are coming through as text:

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

2. In this example, the values of the id and week fields are coming through as numeric:

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

Solution

If you must compare the values you're extracting against numeric values, you have to cast your fields and specify the desired format. For example:

SELECT week FROM my_table WHERE id::int=4

instructs SQL to interpret the value in the id field as integer, and enables you to compare it to an integer value.

Additional information

In this article we use the :: operator to cast to a numeric type. You can read more about it here: PostgreSQL numeric data types