Casting id to string in join recipe

jvijayakumar2
jvijayakumar2 Partner, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 30 Partner

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

  • MikeG
    MikeG Dataiker, Registered Posts: 15 Dataiker
    edited July 17 Answer ✓

    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
    Jurre Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS Core Concepts, Registered, Dataiku DSS Developer, Neuron 2022 Posts: 115 ✭✭✭✭✭✭✭
    Answer ✓

    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

Setup Info
    Tags
      Help me…