如何将Postgres NUMERIC解码为Vapor Fluent的Optional<Float>类型？

我基于Vapor Fluent创建了包含多个可选属性的Skill模型，通过DTO可以成功执行POST请求创建对象，但执行GET请求查询表中所有行时，触发PostgresDecodingError，提示cop_number字段的Postgres NUMERIC类型与模型中的Optional<Float>类型不匹配。相关代码及错误信息如下：

模型代码 #

final class Skill: Model, Content {
    static let schema = "skills"
    @ID(key: .id)
    var id: UUID?
    @Field(key: "name")
    var name: String
    @Enum(key: "discipline")
    var discipline: Discipline
    @Enum(key: "apparatus")
    var apparatus: Apparatus
    @Field(key: "difficulty_value")
    var difficultyValue: DifficultyValue?
    @Field(key: "vault_difficulty_value")
    var vaultDifficultyValue: Float?
    @Field(key: "description")
    var description: String
    @Field(key: "cop_number")
    var copNumber: Float?
    @Field(key: "vault_cop_number")
    var vaultCopNumber: Float?
    @Field(key: "group_number")
    var groupNumber: Int
    @Field(key: "named_after")
    var namedAfter: String?
    @Timestamp(key: "created_at", on: .create)
    var createdAt: Date?
    @Timestamp(key: "updated_at", on: .update)
    var updatedAt: Date?
}

DTO代码 #

struct SkillDTO: Content {
    var name: String
    var discipline: Discipline
    var apparatus: Apparatus
    var difficultyValue: DifficultyValue?
    var vaultDifficultyValue: Float?
    var description: String
    var copNumber: Float?
    var vaultCopNumber: Float?
    var groupNumber: Int
    var namedAfter: String?
    enum CodingKeys: String, CodingKey {
        case name, discipline, apparatus, description
        case difficultyValue = "difficulty_value"
        case vaultDifficultyValue = "vault_difficulty_value"
        case copNumber = "cop_number"
        case vaultCopNumber = "vault_cop_number"
        case groupNumber = "group_number"
        case namedAfter = "named_after"
    }
    func toModel() -> Skill {
        let model = Skill()
        model.name = self.name
        model.discipline = self.discipline
        model.apparatus = self.apparatus
        if let difficultyValue = self.difficultyValue {
            model.difficultyValue = difficultyValue
        }
        if let vaultDifficultyValue = self.vaultDifficultyValue {
            model.vaultDifficultyValue = vaultDifficultyValue
        }
        model.description = self.description
        if let copNumber = self.copNumber {
            model.copNumber = copNumber
        }
        if let vaultCopNumber = self.vaultCopNumber {
            model.vaultCopNumber = vaultCopNumber
        }
        model.groupNumber = self.groupNumber
        if let namedAfter = self.namedAfter {
            model.namedAfter = namedAfter
        }
        return model
    }
}

POST请求处理函数 #

func create(req: Request) throws -> EventLoopFuture<HTTPStatus> {
        let skill = try req.content.decode(SkillDTO.self).toModel()
        return skill.save(on: req.db).transform(to: .ok)
    }

GET请求处理函数 #

func index(req: Request) async throws -> [Skill] {
        try await Skill.query(on: req.db).all()
    }

错误信息 #

invalid field: 'cop_number', type: Optional<Float>, error: PostgresDecodingError(code: typeMismatch, columnName: "skills_cop_number", columnIndex: 7, targetType: Swift.Optional<Swift.Float>, postgresType: NUMERIC, postgresFormat: binary, postgresData: ByteBuffer 

数据库迁移代码 #

return database.schema("skills")
            .id()
            .field("name", .string, .required)
            .field("discipline", .string, .required)
            .field("apparatus", .string, .required)
            .field("difficulty_value", .string)
            .field("vault_difficulty_value", .float)
            .field("description", .string, .required)
            .field("cop_number", .sql(raw: "NUMERIC(4,3)"))
            .field("vault_cop_number", .sql(raw: "NUMERIC(3,2)"))
            .field("group_number", .int, .required)
            .field("named_after", .string)
            .field("created_at", .datetime)
            .field("updated_at", .datetime)
            .create()

方案1：改用Decimal类型（推荐，无精度损失） #

Postgres的NUMERIC类型与Swift的Decimal类型原生匹配，直接修改模型和DTO中的对应字段：

• 修改模型字段：

@Field(key: "cop_number")
var copNumber: Decimal?
@Field(key: "vault_cop_number")
var vaultCopNumber: Decimal?

• 修改DTO字段：

var copNumber: Decimal?
var vaultCopNumber: Decimal?

方案2：自定义Float与NUMERIC的转换逻辑（保留Float） #

如果业务必须使用Float，可以扩展Float实现PostgresDataConvertible协议，手动处理类型转换：

extension Float: PostgresDataConvertible {
    public static func convertFromPostgresData(_ data: PostgresData) throws -> Float {
        guard let decimal = try Decimal.convertFromPostgresData(data) else {
            throw PostgresDecodingError.typeMismatch(targetType: Float.self, postgresType: data.type)
        }
        return NSDecimalNumber(decimal: decimal).floatValue
    }
    
    public func convertToPostgresData() throws -> PostgresData {
        let decimal = Decimal(self)
        return try decimal.convertToPostgresData()
    }
}

添加该扩展后，模型中的Float?字段无需修改，Fluent会自动调用转换逻辑。

方案3：修改数据库字段类型为FLOAT（适合精度要求低的场景） #

如果对数值精度要求不高，可以直接把迁移中的NUMERIC字段改为FLOAT，与模型类型对齐：

.field("cop_number", .float)
.field("vault_cop_number", .float)

注意：此方法会丢失NUMERIC的高精度特性，需根据业务场景评估。

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