Casting id to string in join recipe
![jvijayakumar2](https://us.v-cdn.net/6038231/uploads/Dataiku/nAvatar11.png)
I'm trying to join tables based on ID but I'm getting " ERROR: operator does not exist: uuid = text Hint: No operator matches the given name and argument types. You might need to add explicit type casts." But both of the columns I'm trying to match are of type string except that one of them is named ID and the other is not. Can someone help, please?
Operating system used: Windows
Best Answers
-
Hi @jvijayakumar2
,The error `ERROR: operator does not exist: uuid = text Hint: No operator matches the given name and argument types. You might need to add explicit type casts` means a string/text datatype operator is attempting to be applied to a uuid datatype.
Example:
Consider the following tables in Postgres:
mike=# \d t; Table "public.t" Column | Type | Collation | Nullable | Default --------+---------------+-----------+----------+--------- id | uuid | | | [...] mike=# \d t2; Table "public.t2" Column | Type | Collation | Nullable | Default --------+---------------+-----------+----------+--------- id | text | | | [...]
Note:
- table t’s `id` column is of datatype uuid
- table t2’s `id` column is of datatype text
If I use a join visual recipe to join these datasets using `id` as the join condition AND I use the `In-database (SQL)` Recipe engine I will receive the following error:
ERROR: operator does not exist: uuid = text Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
This is because we are attempting to compare two different datatypes (text and uuid) inside of Postgres which is not allowed.
You can work around the issue by doing one of the following:
- changing the recipe engine from `In-database (SQL)` to `DSS` (bottom left of the recipe UI)
- using a sync recipe to create a copy of the dataset containing the uuid column and then performing the join using the copied dataset. In my example above I would need to use a sync recipe to create a copy of `t` called `t_copy` then I would perform the join between `t_copy` and `t2`
- use Pre-join computed columns section of the join recipe to cast the id column of datatype uuid to text (see screenshot directly below)
Note: I understand you mentioned both columns are of type string (I’m assuming this is what you see when looking at the data in the Explore tab of DSS) however since the operation is by default being performed in the underlying storage (i.e. `In-database (SQL)`) it's the datatypes in the underlying storage which come into play.
Thank you,
Mike -
Jurre Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS Core Concepts, Registered, Dataiku DSS Developer, Neuron 2022 Posts: 114 ✭✭✭✭✭✭✭
Great tip this : "use Pre-join computed columns section of the join recipe to cast the id column", that will help bringing some efficiency in my flows. Thanx @MikeG
Answers
-
jvijayakumar2 Partner, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 30 Partner
Thanks @MikeG
! Using "Pre-join computed columns section" works!