Dfyjkt

I'm trying to create a custom function that performs some scientific logarithmic math but I'm not understanding the error.

In postgreSQL or most other SQL and programing languages, it's as simple as this:

acc = 10^(val/10)

In BigQuery, when I create a TEMP function and SQL

 CREATE TEMP FUNCTION to_acc(x NUMERIC) AS (10^(x/10));
 SELECT val, to_acc(10) AS result

I get the following error:

> Bitwise operator ^ requires two integer/BYTES arguments of the same
> type, but saw DOUBLE and NUMERIC; failed to parse CREATE [TEMP]
> FUNCTION statement at [1:108]

So the Bitwise operator only works on integer/bytes....? All of our data are floats...

Is this a limitation of BigQuery or is there a way around this?

As far as I know, the operator ^ has not the same functionality in BigQuery as it does in PostgreSQL.

While in PostgreSQL it is the power operator, in BigQuery it performs an XOR (and indeed only works on integers and bytes). Based on your use case description I believe you want to calculate the (val/10)th power of 10.

As a way around this you may try the built in standard SQL mathematical functions.

Apparently you want this:

#standardSQL
SELECT POW(10, (x/10))
FROM (SELECT 30.7 x)

With POW(X, Y) being:

Returns the value of X raised to the power of Y.

Note that in most programming languages ^ means XOR, not exponentiation. Posgtres instead chose to use a very non traditional # for XOR. See some of the historical details:

• https://softwareengineering.stackexchange.com/questions/331388/why-was-the-caret-used-for-xor-instead-of-exponentiation