Postgrex.execute throwing Exception

Questions / Help
1

Hello
New to elixir/postgrex here. Trying a simple insert using prepared statement:

defp insert_one(pg_pid, stmt, record, errors) do
    case Postgrex.execute(pg_pid, stmt, [to_string(record.order_type), record.run_date, record.symbol, record.price, record.quantity]) do
      {:ok, ^stmt, _}  -> errors
      {:error, error}  ->  [ error | errors]
    end
end

If the data in record is good, the code works (row is inserted). However when the run_date field is not in good shape, I get an exception from Postgrex.execute. Why should Postgrex.execute throw an Exception? Shouldn’t it report errors by returning {:error, _error} ?

Error thrown:

* (DBConnection.EncodeError) Postgrex expected %Date{}, got "03/28/2024". Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
  (postgrex 0.19.1) lib/postgrex/type_module.ex:1084: Postgrex.DefaultTypes.encode_params/3
  (postgrex 0.19.1) lib/postgrex/query.ex:75: DBConnection.Query.Postgrex.Query.encode/3
  (db_connection 2.7.0) lib/db_connection.ex:1449: DBConnection.encode/5
  (db_connection 2.7.0) lib/db_connection.ex:1549: DB Connection.run_prepare_execute/5
  (db_connection 2.7.0) lib/db_connection.ex:1653: DBConnection.run/6
  (db_connection 2.7.0) lib/db_connection.ex:772: DBConnection.parsed_prepare_execute/5
  (db_connection 2.7.0) lib/db_connection.ex:825: DBConnection.execute/4
2

Postgrex.execute will return an {:error, %Postgrex.Error{}} shape when the database server reports an error executing the query.

The exception you’re seeing is happening before the query is even sent to the DB, when Postgrex is forming the binary payload to send to the server.

You’ll either need to convert record.run_date to the correct type beforehand, or write a query that expects a MM/DD/YYYY string and converts in SQL.