Home No operator matches the given name and argument type(s): What needs to be cast?
Reply: 2

No operator matches the given name and argument type(s): What needs to be cast?

Chef1075
1#
Chef1075 Published in 2018-01-12 23:17:38Z

I'm getting an error, and I cannot figure out why. I know the error is telling me to cast a type but I'm not sure on what?

What part of CASE is the operator?

ERROR:  operator does not exist: character varying = boolean
LINE 6:              WHEN lower(foo.name) SIMILAR TO '%(foo
                    ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

My query:

SELECT foo.name,
       bar.city,
       MAX(foo.total - bar.tax_amount),
       CASE bar.name
            WHEN lower(foo.name) SIMILAR TO '%(foo|bar|baz)%' THEN true
            ELSE false
        END
        ....
GROUP BY foo.name, bar.city;
Gordon Linoff
2#
Gordon Linoff Reply to 2018-01-12 23:29:35Z

I believe you simply want:

SELECT foo.name,
       bar.city,
       MAX(foo.total - bar.tax_amount),
       (CASE WHEN lower(foo.name) SIMILAR TO '%(foo|bar|baz)%' THEN true
            ELSE false
        END)
        ....
GROUP BY foo.name, bar.city;

The CASE expression has two forms. One searches for values for a given expression. This takes a column or expression right after the CASE. The second searches through various expressions, stopping at the first one. This takes the WHEN clause right after the CASE.

Or even more simply:

SELECT foo.name, bar.city,
       MAX(foo.total - bar.tax_amount),
       (lower(foo.name) SIMILAR TO '%(foo|bar|baz)%') 
        ....
GROUP BY foo.name, bar.city;

The CASE is not needed.

Erwin Brandstetter
3#
Erwin Brandstetter Reply to 2018-01-12 23:30:42Z

Your CASE expression had a syntax error, like @a_horse commented.

But you don't even need CASE at all in this particular case for a boolean result. Just:

foo.name ~* '(foo|bar|baz)'

That's all.

~* being the case-insensitive regular expression match operator.

Never use SIMILAR TO:

  • Difference between LIKE and ~ in Postgres
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.312268 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO