I’m using Metabase v0.28.5 and I have a PostgreSQL database with a table X with a column called
sum_profit. I’m trying to create a “Custom” question in which I use the option
Custom Expression to calculate the value I should return in my query. This calculation is:
SUM(sum_profit * request_index)
However I receive the following error message Expected field name, number, or expression but found ‘sum’ and I’m not able to save this expression. When I replace
sum_profit with another Number column (such as
cost), the calculation seems to be correctly performed.
I noticed that this error appear whenever my column name starts with some SQL keyword, such as “count”, “sum”, “avg” and so on. Not sure if it’s an expected behavior of Metabase, but to me it’s really weird.
I also checked this behavior in the newest versions, v0.28.6 and v0.29.0-RC1
Is this a metabase error or Postgresql? In MS SQL, I’d try [sum_profit] or “sum_profit”, no idea if that’s valud in Postgresql.
Sat on a train, so can’t check my test server.
Hi @AndrewMBaines , it seems to be a Metabase error. I run a query in
psql terminal using this expression
SUM(t.sum_profit * t.request_index) and it worked correctly. I also tried to use Native Query option @ Metabase and it worked as well.
That’s why my hypothesis is that Metabase is doing some kind of “validation” in which it prevents calculation depending on column name
What are the display names for those columns in Metabase? Do they show as
Sum Profit and
Request Index instead of
Perhaps you can try
SUM("Sum Profit" * "Request Index") (or whatever the column display names are) as your custom expression.
Hi, @william , I disabled the “humanized name” Metabase try to guess to column names. My solution for this was to change the column names in order to change de position of the SQL keyword. So, instead of name the column
sum_profit, I called it
profit_sum and it worked properly.
Anyways, maybe it’s a good point to review in Metabase custom expression creation. =)
I wonder if this is something to do with Metabase being written in Clojure. It has all sorts of functions for converting between ‘_’, ‘-’ and CamelCase. Perhaps somewhere the underscore is being interprested as minus.
No evidence, just conjecture.
Edit: Thinking further, that can’t be right or reversing the order wouldn’t work either.