Casting id to string in join recipe

Solved!
jvijayakumar2
Level 3
Casting id to string in join recipe

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

0 Kudos
2 Solutions
MikeG
Dataiker

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)

Screen Shot 2022-04-21 at 10.43.09 PM.png

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

View solution in original post

Jurre
Level 5

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 

View solution in original post

3 Replies
MikeG
Dataiker

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)

Screen Shot 2022-04-21 at 10.43.09 PM.png

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
Level 5

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 

jvijayakumar2
Level 3
Author

Thanks @MikeG! Using "Pre-join computed columns section" works!

0 Kudos

Labels

?

Setup info

?
A banner prompting to get Dataiku