Difference SQL Dataiku / external

vmar

Hello,

I'm trying to understand a difference in behavior on Dataiku and my external tool Dbeaver. I'm runing the same queries on the same database (PostGreSQL), one on Dataiku SQL recipe the other on Dbeaver.

Here is the first request:

select extract(day from date1-date2) as delai

On Dbeaver this syntax is not accepted it says : ERROR: function pg_catalog.date_part(unknown, integer) does not exist.

On Dataiku, it works.

I tried this second request:

select date1-date2 as delai

and further in the script try to compare delai with an integer

On Dbeaver, it works and makes the comparison.

On Dataiku, the logger returns: ERROR: operator does not exist: interval <= integer Hint: No operator matches the given name and argument types.

Why such different behavior since I'm working on the same database ?

dima_naboka

Hello,

I was unable to reproduce this behavior. For me, DBevaer and DSS behavior was identical. I’m using PostgreSQL 12, DBeaver 7.2.0 with postgresql-42.2.5.jar:

and DSS 7.0.3 (on MacOS) with default postgresql-9.1-901.jdbc4.jar (/Applications/DataScienceStudio.app/Contents/Resources/kit/lib/ivy/backend-run/).

According to PostgreSQL 12 documentation "The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, time, or interval. (Expressions of type date are cast to timestamp and can therefore be used as well.)”

Apparently, your “date1” and “date2” columns can be timestamps but not dates because result of subtracting date from date is integer according to the doc. You can use dates if you do EXTRACT(DAY from submit_date) though.

Please double-check your settings and columns types and if you still experience the problem provide more details to reproduce the issue.

P.s. Generally speaking, both DSS and DBeaver rely solely on JDBC driver to communicate with target DB. So, as long as identical JDBC drivers are used you shouldn't experience any differences in the output.

vmar

Thanks

By "check your settings", what settings do you mean ? For column types they are indeed the same.