Please do tell me how to get rid of this error
Here is my code:
%%sql
DROP TABLE IF EXISTS ages;
CREATE TABLE ages AS
SELECT CODEDESC, SUM(age) AS age FROM
(SELECT CODEDESC, (CASE WHEN (JULIANDAY(MAX(inspdate))-JULIANDAY(MIN(inspdate)))< 30 THEN 30
ELSE (JULIANDAY(MAX(inspdate))-JULIANDAY(MIN(inspdate)))
END)AS age FROM filtered_webextract
INNER JOIN cuisine
ON filtered_webextract.CUISINECODE = cuisine.CUISINECODE
GROUP BY CODEDESC, camis) as foo
GROUP BY CODEDESC;
Here is the error:
* postgresql://docker:***@nycinspection.tditrain.com:5433/NYCinspection
Done.
(psycopg2.errors.UndefinedFunction) function julianday(timestamp without time zone) does not exist
LINE 3: (SELECT CODEDESC, (CASE WHEN (JULIANDAY(MAX(inspdate))-JULI...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
[SQL: CREATE TABLE ages AS
SELECT CODEDESC, SUM(age) AS age FROM
(SELECT CODEDESC, (CASE WHEN (JULIANDAY(MAX(inspdate))-JULIANDAY(MIN(inspdate)))< 30 THEN 30
ELSE (JULIANDAY(MAX(inspdate))-JULIANDAY(MIN(inspdate)))
END)AS age FROM filtered_webextract
INNER JOIN cuisine
ON filtered_webextract.CUISINECODE = cuisine.CUISINECODE
GROUP BY CODEDESC, camis) as foo
GROUP BY CODEDESC;]
(Background on this error at: http://sqlalche.me/e/13/f405)
Please let me know how to change my code.
Thanks in advance
2
Answers
This error occur because there is no user defined or inbuilt function calls
JULIANDAY
in postgresql. Therefore replaceJULIANDAY(timestamp)
withextract(julian from 'timestamp')
.Then it will work.try
MAX(JULIANDAY(inspdate)) - MIN(JULIANDAY(inspdate)) < 30
orMAX(JULIANDAY(inspdate)) - MIN(JULIANDAY(inspdate)) < INTERVAL '30 days'
, or even useDATE_PART('day', end - start)
UPD: assuming that the
JULIANDAY()
is your custom function