Casting id to string in join recipe

jvijayakumar2

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

MikeG

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

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

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