search

jchristn / DatabaseWrapper

Simple database wrapper for Microsoft SQL Server, MySQL, PostgreSQL, and Sqlite written in C# supporting dynamic query building and nesting using expressions.
MIT License
97 stars 26 forks source link

PostgreSQL Insert Boolean #25

Closed thedarklort closed 1 month ago

thedarklort commented 2 months ago

While mindlessly writing some code i noticed that i get an error when i try to use a specific Insert.

I try to insert a boolean value into a column which has the datatype boolean. The error reads as follows: "ERROR: column "istrue" is of type boolean but expression is of type integer"

I was a bit confused but after fiddling around with PostgreSQL i found out that you cannot insert a boolean value into Postgres with the values 1 or 0 it somehow has to be a string (read more here)

i tried out this behaviour on PostgreSQL version 16.4 and on 9.1.5 and on both its the same.

My proposed fix would be to change the line 1265 in PostgresqlHelper.cs from vals += ((bool)currKvp.Value ? "1" : "0"); to vals += ((bool)currKvp.Value ? "true" : "false");

A workaround is to convert the boolean beforehand in my own code dict.Add("perfect", (score.Perfect ? "true" : "false")); this works flawless

jchristn commented 1 month ago

Hi @thedarklort I had to make a lot of assumptions and introduce a lot of opinions to be able to do this in a way that would make it portable across platforms, and this is unfortunately one of those opinions and assumptions. 100% agree that it could be better for Postgresql in this case, but for me not enough of a priority to investigate what the change would entail across all the other database types. Cheers and thank you for the feedback!