search

isoos / postgresql-dart

Dart PostgreSQL driver: supports extended query format, binary protocol and statement reuse.
https://pub.dev/packages/postgres
BSD 3-Clause "New" or "Revised" License
131 stars 37 forks source link

Error when using int8 (bigInteger) as query parameter type #22

Open ryanaston opened 2 years ago

ryanaston commented 2 years ago

Dart ints are 64-bit, so I assumed this would work:

await connection.query("SELECT TO_TIMESTAMP(@ts / 1000)", substitutionValues: {'ts': 1640556171599});

However, I get the following error when running the query above:

PostgreSQLSeverity.error 22003: value "1640556171599" is out of range for type integer package:postgres/src/connection.dart 453:18 _PostgreSQLExecutionContextMixin._query package:postgres/src/connection.dart 427:7 _PostgreSQLExecutionContextMixin.query

I attempted to remedy by specifying the data type in the query, like so:

await connection.query("SELECT TO_TIMESTAMP(@ts:int8 / 1000)", substitutionValues: {'ts': 1640556171599});

This causes a different error:

PostgreSQLSeverity.error : Specified parameter types do not match column parameter types in query SELECT TO_TIMESTAMP(@ts:int8 / 1000) package:postgres/src/connection.dart 453:18 _PostgreSQLExecutionContextMixin._query package:postgres/src/connection.dart 427:7 _PostgreSQLExecutionContextMixin.query

Is this a bug or am I mistaken on how to safely use 64-bit ints as query parameters?

isoos commented 2 years ago

In theory we should have full int8 support both in Dart and in package:postgres. We should have a test or two that uses it as input and output for a query, but I don't recall the details right away. If you have some time, please debug it a bit more, I'm happy to accept PRs...

isoos commented 11 months ago

Note: #264 suggests we have already fixed the :int8 part, not sure if/how the untyped part should work.