Sea-ORM操作PostgreSQL时生成枚举值CAST语法导致执行失败

我尝试使用Sea-ORM向PostgreSQL插入一条记录，启用sqlx-logging排查插入失败原因时，发现生成的SQL语句中包含CAST('guest' AS CustomerStatus)子句，触发错误提示“type "customerstatus" does not exist”（错误码42704），但通过查询确认public模式下确实存在CustomerStatus枚举类型。移除CAST语句后插入操作可正常执行，请问该问题原因是什么？

生成的SQL日志 #

[2024-08-03T07:33:35Z DEBUG sea_orm::driver::sqlx_postgres] INSERT INTO "customers" ("email", "status", "linked_acc_name", "acc_manager_name", "acc_manager_email", "acc_manager_contact_no", "created_by", "created_on", "updated_by", "updated_on") VALUES ('abcd@hmail.com', CAST('guest' AS CustomerStatus), NULL, NULL, NULL, NULL, 'a48c7582-3cac-4249-924d-5fe93b8f345a', '2024-08-03 07:33:35', 'a48c7582-3cac-4249-924d-5fe93b8f345a', '2024-08-03 07:33:35') RETURNING "id", "email", CAST("status" AS text), "linked_acc_name", "acc_manager_name", "acc_manager_email", "acc_manager_contact_no", "created_by", "created_on", "updated_by", "updated_on"
[2024-08-03T07:33:35Z INFO  sqlx::query] summary="INSERT INTO \"customers\" (\"email\", …" db.statement="\n\nINSERT INTO\n  \"customers\" (\n    \"email\",\n    \"status\",\n    \"linked_acc_name\",\n    \"acc_manager_name\",\n    \"acc_manager_email\",\n    \"acc_manager_contact_no\",\n    \"created_by\",\n    \"created_on\",\n    \"updated_by\",\n    \"updated_on\"\n  )\nVALUES\n  (\n    $1,\n    CAST($2 AS CustomerStatus),\n    $3,\n    $4,\n    $5,\n    $6,\n    $7,\n    $8,\n    $9,\n    $10\n  ) RETURNING \"id\",\n  \"email\",\n  CAST(\"status\" AS text),\n  \"linked_acc_name\",\n  \"acc_manager_name\",\n  \"acc_manager_email\",\n  \"acc_manager_contact_no\",\n  \"created_by\",\n  \"created_on\",\n  \"updated_by\",\n  \"updated_on\"\n" rows_affected=0 rows_returned=0 elapsed=594.438µs elapsed_secs=0.000594438
thread 'tokio-runtime-worker' panicked at src/handlers/users/signup.rs:56:47:
called `Result::unwrap()` on an `Err` value: Query(SqlxError(Database(PgDatabaseError { severity: Error, code: "42704", message: "type \"customerstatus\" does not exist", detail: None, hint: None, position: Some(Original(210)), where: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("parse_type.c"), line: Some(270), routine: Some("typenameType") })))
stack backtrace:

查询PostgreSQL枚举类型的结果 #

SELECT n.nspname AS schema, t.typname AS enum_name
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
GROUP BY schema, enum_name;
 schema |      enum_name
--------+---------------------
 public | WorkflowStatus
 public | DeploymentJobStatus
 public | CustomerStatus
 public | DatasetPurpose
 public | TrainingStatus
 public | DeploymentStatus
 public | TrainingParamType
(7 rows)

问题原因 #

核心是PostgreSQL的标识符大小写规则与Sea-ORM生成SQL的处理逻辑不匹配：

• PostgreSQL中，如果创建枚举时用双引号包裹了驼峰名称（如"CustomerStatus"），该枚举类型的名称会变成大小写敏感的，必须使用带双引号的名称才能被正确识别。
• Sea-ORM生成的CAST语句中直接使用了CustomerStatus（未加双引号），PostgreSQL会自动将其转换为小写customerstatus去查找枚举类型，但你的实际枚举是大小写敏感的"CustomerStatus"，因此数据库找不到对应类型，触发42704错误。

解决办法 #

有两种可行的修复方案：

1. 修改Sea-ORM枚举映射配置：在实体的枚举定义中，明确指定数据库类型为带双引号的枚举名称。示例代码：

   #[derive(EnumIter, DeriveActiveEnum)]
   #[sea_orm(rs_type = "\"CustomerStatus\"", db_type = "Enum", enum_name = "CustomerStatus")]
   pub enum CustomerStatus {
       #[sea_orm(string_value = "guest")]
       Guest,
       // 其他枚举值...
   }
   

   这样Sea-ORM生成的CAST语句会自动带上双引号，匹配数据库中的枚举类型。
2. 重新创建枚举类型（不使用大小写敏感名称）：删除现有枚举，重新创建时不使用双引号，让PostgreSQL自动将名称转为小写customerstatus。示例SQL：

   CREATE TYPE customerstatus AS ENUM ('guest', 'other_status');
   

   之后Sea-ORM生成的CAST('guest' AS CustomerStatus)会被PostgreSQL解析为小写customerstatus，与数据库中的枚举类型匹配。

内容的提问来源于stack exchange，提问作者Mopparthy Ravindranath