No Matching Signature for operator >= for argument types: TYPE, OTHER TYPE

[jamesnestler]

SQL Error Series #1: Type Errors

Post Context: This content is adapted from our internal SME (Subject Matter Expert) curricula. Similar to knowledge drops, this content may be highly Looker specific and may become out of date in the future. The goal is to convey a high level understanding of a common SQL query issue and convey next steps for troubleshooting and resolving. 

What does it mean?

This is a SQL error that is complaining about comparing two different datatypes. You may see a specific error that complains about DATE, TIMESTAMP; VARCHAR, INT; or really any other combination of datatypes. See your dialect's documentation for more information.

An ELI5 example is if you tried to compare a number to a string. Is 1 >= 'orange' ? These ambiguities freak out computers and throw an error. For most dialects, all comparison's must be between compatible datatypes (eg a string vs a string, a number to a number, etc).

The high level steps to resolve this issue would be:

• Identify the specific SQL that is generating the comparison between the two incompatible values
• Identify the datatype of each of the two values/columns
• Add a cast or conversion statement SQL statement, or a LookML parameter that generates casting logic

---------------------------------------------------------------------------------

 

Example Case 1: Dimension_Group errors with "No Matching... DATE, TIMESTAMP"

 

How do I fix it?

As can be gathered from the high level explanation, somewhere a date value is being compared to a timestamp value. A common reason for this is if you've defined a dimension_group with a date instead of a timestamp. A dimension_group expects a timestamp by default, so you will need to either cast the date to a timestamp in the sql parameter or utilize the datatype parameter to call this a date.

A few example solutions:

1. Casting the date to a timestamp in sql: (the SQL will vary by dialect):

dimension_group: created {
type: time
sql: TIMESTAMP(${TABLE}.created_at) ;;
}

2. Using the datatype parameter:

dimension_group: created {
type: time
datatype: date
sql: ${TABLE}.created_at ;;
}

Example Case 2: Measure errors with “ERROR: function sum(text) does not exist” 

Full error message might look something like this: The PostgreSQL 9.5+ database encountered an error while running this query. ERROR: function sum(text) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

How do I fix it? 

As the error indicates, we’re trying to use a sum() function on a string value. The SQL function `sum()` expects numeric data. To resolve, we would need to cast the data to some numeric type, referencing our dialect’s documentation for specifics. 

A few examples using different methods in differing dialects: