Use Stream as PostgresSQL parameter in .NET

Question

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.

user_11787880 · Answer

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 a Stream as a parameter to the bytea 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 in DbParameter.Size property.

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();
}

user_2011679 · Answer

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.

Use Stream as PostgresSQL parameter in .NET

2 Answers