`NULLIF` causes loss of precision when inserting `float64` values

raptr0n commented 2 months ago

Description:

When using the NULLIF function in an insert query with the pgx library, I encountered an issue where a float64 value that should be inserted into a NUMERIC(9,3) column loses precision and is rounded off incorrectly.

Steps to Reproduce:

Create a test table in PostgreSQL with the following schema:
```
CREATE TABLE test (
   number NUMERIC(9,3) NOT NULL
);
```

Write a Go function that inserts data into the test table using the pgx library and NULLIF for the number column:

func PerformExec(
   ctx context.Context,
   pgxPool *pgxpool.Pool,
   query string,
   args ...interface{},
) error {
   _, err := pgxPool.Exec(ctx, query, args...)
   if err != nil {
       return err
   }
   return nil
}

const testQuery = `
   INSERT INTO test (number)
   SELECT NULLIF($1, 0);
`

err := PerformExec(ctx, pgxPool, testQuery, number)
if err != nil {
   return err
}

Insert a value into the table with the following data:
```
number := 1000.17 // float64 with higher precision
```
Verify the inserted value by querying the database:
```
SELECT * FROM test;
```

Expected Behavior:

The value 1000.170 should be inserted into the number column with full precision, as the column type is NUMERIC(9,3).

Actual Behavior:

When using NULLIF($1, 0) for the number column, the value is inserted as 1000.000 instead of 1000.170.

However, when NULLIF($1, 0) is removed and $1 is passed directly, the value is inserted correctly as 1000.170.

Additional Information:

Go version: 1.23.1
pgx version: v5.7.1
PostgreSQL version: 14.12

felix-roehrich commented 2 months ago

This is not (directly) a problem with pgx but the implicit interaction with how postgres works. When executing PREPARE stmt AS SELECT NULLIF($1, 0)(what pgx does internally), the statement becomes effectively PREPARE stmt AS SELECT NULLIF($1, 0)::int due to how type inference with NULLIF works. Writing either NULLIF($1::float, 0) or NULLIF($1, 0::float) will solve the problem (or in your case numeric).

Though pgx could do things differently with how statements are prepared, this might cause more problems than it solves.

raptr0n commented 2 months ago

Yes, that absolutely worked! Thanks for your assistance and insight.

jackc / pgx