Is there a way to pass a Stream as a sql parameter to insert a query into a Postgres bytea/jsonb column using .NET? As I already have a Stream and those column types are binary types, I’m trying to pass it to query instead of reading it to byte[]/string and then passing it.
Like in this sample from SQL Server or in this sample using java’s JDBC driver.
I’ve tried something like this:
public static async Task Insert(NpgsqlConnection connection, Guid id, Stream payload)
{
string commandText = $"INSERT INTO some_table (id, payload, occurred_on) " +
$"VALUES (@id, @payload, @occurred_on)";
await using var cmd = new NpgsqlCommand(commandText, connection);
cmd.Parameters.AddWithValue("id", id);
cmd.Parameters.AddWithValue("payload", payload);
cmd.Parameters.AddWithValue("occurred_on", DateTime.Now);
await cmd.ExecuteNonQueryAsync();
}
But it has thrown an exception:
System.InvalidCastException: ‘Can’t write CLR type System.IO.MemoryStream with handler type ByteaHandler
I found there is https://www.npgsql.org/doc/copy.html#raw-binary-copy, but I’d like to insert a single row with multiple columns, one of which is passed as a Stream, and if I’m correct, it is not possible with it.
2
Answers
As Charlieface pointed out, from npgsql version 7.0 (currently in preview; I tested it on version
7.0.0-preview.7
), it will be possible to pass aStream
as a parameter to thebytea
column.You just have to pass it like any other type. The only exception is when the
Stream
is not seekable, then it is required to provide its size inDbParameter.Size
property.Looks like Postgres does not support streaming for such query.
As result npgsql library unable to handle the case. See discussion Write parameter from streams/readers.