Using postgresql-simple to connect to Clickhouse

[jonathangenlambda]

We are trying to connect to Clickhouse via its "postgres emulation port" 9005 (see).

Unfortunately sending a simple SELECT 1 query fails with
SqlError {sqlState = "", sqlExecStatus = FatalError, sqlErrorMsg = "", sqlErrorDetail = "", sqlErrorHint = ""}

When looking at the error log of Clickhouse we see:

PostgreSQLHandler: DB::Exception: Syntax error: failed at position 15 ('TO'): TO ISO;SET client_encoding TO UTF8;SET standard_conforming_strings TO on. Expected one of: token, Dot, Equals

We checked the code of postgresql-simple and found the corresponding location in Database.PostgreSQL.Simple.Internal which executes "SET datestyle TO ISO;SET client_encoding TO UTF8;SET standard_conforming_strings TO on" eventually.

I assume currently there is no way to prevent this from getting executed? We will fork the repo and remove this execution and see what happens - maybe this could be made optional?

[jonathangenlambda]

We have removed the corresponding executiong of "SET datestyle TO ISO;SET client_encoding TO UTF8;SET standard_conforming_strings TO on" and are now able to successfully query Clickhouse via postgresql-simple.

Please consider introducing a configuration/argument that allows for disabling these "SET..." commands.