Shenanigans•2w ago

[SOLVED] Casting to Enums and custom types - PostgreSQL

Hello, I am encountering the following error when I try to upsert on a PostgreSQL record where one of my arguments is an enum type. I assumed I would be able to use a string directly from my script arguments, but that does not seem to be the case. Error:

{
    "error": {
        "name": "ExecutionErr",
        "message": "execution error:\nError: error serializing parameter 4: cannot convert between the Rust type `alloc::string::String` and the Postgres type `currency`: cannot convert between the Rust type `alloc::string::String` and the Postgres type `currency` @pg_executor.rs:129:24"
    }
}

{
    "error": {
        "name": "ExecutionErr",
        "message": "execution error:\nError: error serializing parameter 4: cannot convert between the Rust type `alloc::string::String` and the Postgres type `currency`: cannot convert between the Rust type `alloc::string::String` and the Postgres type `currency` @pg_executor.rs:129:24"
    }
}

I have also tried casting via

INSERT INTO myTable (currency) VALUES (
  ...
  ($5::currency)::TEXT
  ...

INSERT INTO myTable (currency) VALUES (
  ...
  ($5::currency)::TEXT
  ...

But I encounter the following error:

{
    "error": {
        "name": "ExecutionErr",
        "message": "execution error:\nError: db error: ERROR: column \"currency\" is of type currency but expression is of type text\nHINT: You will need to rewrite or cast the expression.: ERROR: column \"currency\" is of type currency but expression is of type text\nHINT: You will need to rewrite or cast the expression. @pg_executor.rs:129:24"
    }
}

{
    "error": {
        "name": "ExecutionErr",
        "message": "execution error:\nError: db error: ERROR: column \"currency\" is of type currency but expression is of type text\nHINT: You will need to rewrite or cast the expression.: ERROR: column \"currency\" is of type currency but expression is of type text\nHINT: You will need to rewrite or cast the expression. @pg_executor.rs:129:24"
    }
}

Windmill Version: Windmill EE v1.491.5-27-gdfd8c4cd2

10 Replies

rubenf•2w ago

do the opposite, cast to text then currency

ShenanigansOP•4d ago

Thanks for the feedback. Unfortunately, upon applying your suggestion, I encounter the following error:

{
    "error": {
        "name": "ExecutionErr",
        "message": "execution error:\nError: Error: conversion issue for value at column_name `currency` with type Other(Other { name: \"currency\", oid: 147457, kind: Enum([\"USD\", \"CAD\"]), schema: \"public\" }): error deserializing column 4: cannot convert between the Rust type `core::option::Option<alloc::string::String>` and the Postgres type `currency`: cannot convert between the Rust type `core::option::Option<alloc::string::String>` and the Postgres type `currency` @pg_executor.rs:862:19 @pg_executor.rs:168:60"
    }
}

{
    "error": {
        "name": "ExecutionErr",
        "message": "execution error:\nError: Error: conversion issue for value at column_name `currency` with type Other(Other { name: \"currency\", oid: 147457, kind: Enum([\"USD\", \"CAD\"]), schema: \"public\" }): error deserializing column 4: cannot convert between the Rust type `core::option::Option<alloc::string::String>` and the Postgres type `currency`: cannot convert between the Rust type `core::option::Option<alloc::string::String>` and the Postgres type `currency` @pg_executor.rs:862:19 @pg_executor.rs:168:60"
    }
}

I also tried using the CAST() postgresql function. Not sure of other tricks I can use to get around this problem Hey @rubenf, sorry to ping you, but the issue above is a blocker for my team and me. Do you have any other suggestions I can follow? Thanks

rubenf•4d ago

it's the opposite $5::text::currency

ShenanigansOP•4d ago

Thanks for the reply. Unfortunately that doesn't work. I have also tried wrapping in brackets and case statement as bellow to no avail

CASE 
    WHEN $5 = 'USD' THEN 'USD'::currency
    ELSE 'CAD'::currency
  END,

CASE 
    WHEN $5 = 'USD' THEN 'USD'::currency
    ELSE 'CAD'::currency
  END,

rubenf•4d ago

What is the error you have?

ShenanigansOP•4d ago

The same as before.

{
    "error": {
        "name": "ExecutionErr",
        "message": "execution error:\nError: Error: conversion issue for value at column_name `currency` with type Other(Other { name: \"currency\", oid: 147457, kind: Enum([\"USD\", \"CAD\"]), schema: \"public\" }): error deserializing column 4: cannot convert between the Rust type `core::option::Option<alloc::string::String>` and the Postgres type `currency`: cannot convert between the Rust type `core::option::Option<alloc::string::String>` and the Postgres type `currency` @pg_executor.rs:862:19 @pg_executor.rs:168:60"
    }
}

{
    "error": {
        "name": "ExecutionErr",
        "message": "execution error:\nError: Error: conversion issue for value at column_name `currency` with type Other(Other { name: \"currency\", oid: 147457, kind: Enum([\"USD\", \"CAD\"]), schema: \"public\" }): error deserializing column 4: cannot convert between the Rust type `core::option::Option<alloc::string::String>` and the Postgres type `currency`: cannot convert between the Rust type `core::option::Option<alloc::string::String>` and the Postgres type `currency` @pg_executor.rs:862:19 @pg_executor.rs:168:60"
    }
}

Hugo•3d ago

do you have "returning" at the end of your insert statement?

ShenanigansOP•3d ago

Yes. My sql script resembles the following:

INSERT INTO my_table (
  col_1,
  col_2,
  col_3,
  col_4,
  currency
) VALUES (
  $1::INT,
  $2::INT,
  COALESCE($3::TEXT, NULL),
  $4::TEXT,
  $5::TEXT::currency
)
ON CONFLICT (col_1) DO UPDATE SET
  currency = EXCLUDED.currency
RETURNING *

INSERT INTO my_table (
  col_1,
  col_2,
  col_3,
  col_4,
  currency
) VALUES (
  $1::INT,
  $2::INT,
  COALESCE($3::TEXT, NULL),
  $4::TEXT,
  $5::TEXT::currency
)
ON CONFLICT (col_1) DO UPDATE SET
  currency = EXCLUDED.currency
RETURNING *

Hugo•3d ago

that's why you get this error, you should cast your custom type to text in the returning statement instead of *

ShenanigansOP•3d ago

It works now. Thank you very much. Didn't realize I had to cast the returning data as well. I'll mark the title as solved

[SOLVED] Casting to Enums and custom types - PostgreSQL

Did you find this page helpful?