pg-mem misinterprets numeric values as text when inserting into float4 columns

[hpolatyuruk]

Describe the bug

When using pg-mem with a float4 column, inserting numeric values causes the error:

QueryFailedError: column "progress" is of type float4 but expression is of type text

Even though the query is correctly generated by TypeORM with numeric values, pg-mem treats these values as text during execution, leading to a type mismatch. In particular, the error occurs when float4 is registered in pg-mem and values are passed to the float4 column.

🐜 This seems to be an execution error, which means that your request syntax seems okay, but the resulting statement cannot be executed → Probably not a pg-mem error.

*️⃣ Failed SQL statement: INSERT INTO "task_assignments" ("progress") VALUES ('39'), ('79')

The TypeORM-generated query inserts numeric values as parameters, but pg-mem interprets them as text during execution, as shown in the error message where '39' and '79' are treated as strings.

Below is a minimal reproducible example that works on a real PostgreSQL instance but fails with pg-mem.

To Reproduce

CREATE TABLE task_assignments (
  id serial PRIMARY KEY,
  progress float4
);

INSERT INTO task_assignments (progress) VALUES (39), (79);  -- This works on a real PostgreSQL instance

Code Example:

1. TypeORM Entity:

import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class TaskAssignments {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  progress: number;
}

2. Registration of float4 type to pg-mem:

    db.public.registerEquivalentType({
      name: 'float4',
      // which type is it equivalent to (will be able to cast it from it)
      equivalentTo: DataType.float,
      isValid(val: any) {
        // Validate that the value is a valid number
        return typeof val === 'number' && !isNaN(val);
      },
    });

3. Insert Data Using TypeORM:

const repository = connection.getRepository(TaskAssignments);

const task1 = new TaskAssignments();
task1.progress = 39;  // Ensure progress is a number
await repository.save(task1);

const task2 = new TaskAssignments();
task2.progress = 79;  // Another numeric value
await repository.save(task2);

4. TypeORM Generated Query (Correct):

INSERT INTO "task_assignments" ("progress") VALUES ($1), ($2) -- PARAMETERS: [39, 79]

Despite the correct query and parameters, pg-mem interprets the values as text during execution, resulting in the error.

QueryFailedError: column "progress" is of type float4 but expression is of type text

    🐜 This seems to be an execution error, which means that your request syntax seems okay,
        but the resulting statement cannot be executed → Probably not a pg-mem error.

    *️⃣ Failed SQL statement: INSERT INTO task_assignments (progress) VALUES ('39'), ('79'); 

pg-mem version

3.0.2

typeorm version

0.3.14

[oguimbal]

Thanks ! Should be fixed in [email protected]

[hpolatyuruk]

Hi @oguimbal,
I’ve upgraded to pg-mem 3.0.3 and tested the issue again, but it seems the problem still persists. This time, I’m receiving a different error message:

QueryFailedError: invalid input syntax for type float4: 0

🐜 This seems to be an execution error, which means that your request syntax seems okay,
    but the resulting statement cannot be executed → Probably not a pg-mem error.

However, the generated insert query for the float4 column is still treating the value as text, which may be contributing to the problem.