如何在 Postgres 使用 Blob 数据类型

使用 Bytea(或者 大型物体，如果你使用 绝对必须)

我认为这是 PostgreSQL wiki 本身最全面的答案: https://wiki.postgresql.org/wiki/BinaryFilesInDB

阅读标题为“在数据库中存储文件的最佳方法是什么?”的部分

在数据库中存储文件将导致巨大的数据库大小。对于开发、测试、备份等，您可能不喜欢这样。

相反，您应该使用 FileStream (SQL-Server)或 BFILE (Oracle)。

在 Postgres 没有默认的 BFILE/FileStream 实现，但是你可以添加它: Https://github.com/darold/external_file

进一步的信息(法语)可以在这里获得:
Http://blog.dalibo.com/2015/01/26/extension_bfile_pour_postgresql.html

回答真正的问题:
除了 bytea，对于真正大的文件，您可以使用 肺叶:

// http://stackoverflow.com/questions/14509747/inserting-large-object-into-postgresql-returns-53200-out-of-memory-error
// https://github.com/npgsql/Npgsql/wiki/User-Manual
public int InsertLargeObject()
{
int noid;
byte[] BinaryData = new byte[123];


// Npgsql.NpgsqlCommand cmd ;
// long lng = cmd.LastInsertedOID;


using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(GetConnectionString()))
{
using (Npgsql.NpgsqlTransaction transaction = connection.BeginTransaction())
{
try
{
NpgsqlTypes.LargeObjectManager manager = new NpgsqlTypes.LargeObjectManager(connection);
noid = manager.Create(NpgsqlTypes.LargeObjectManager.READWRITE);
NpgsqlTypes.LargeObject lo = manager.Open(noid, NpgsqlTypes.LargeObjectManager.READWRITE);


// lo.Write(BinaryData);
int i = 0;
do
{
int length = 1000;
if (i + length > BinaryData.Length)
length = BinaryData.Length - i;


byte[] chunk = new byte[length];
System.Array.Copy(BinaryData, i, chunk, 0, length);
lo.Write(chunk, 0, length);
i += length;
} while (i < BinaryData.Length);


lo.Close();
transaction.Commit();
} // End Try
catch
{
transaction.Rollback();
throw;
} // End Catch


return noid;
} // End Using transaction


} // End using connection


} // End Function InsertLargeObject






public System.Drawing.Image GetLargeDrawing(int idOfOID)
{
System.Drawing.Image img;


using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(GetConnectionString()))
{
lock (connection)
{
if (connection.State != System.Data.ConnectionState.Open)
connection.Open();


using (Npgsql.NpgsqlTransaction trans = connection.BeginTransaction())
{
NpgsqlTypes.LargeObjectManager lbm = new NpgsqlTypes.LargeObjectManager(connection);
NpgsqlTypes.LargeObject lo = lbm.Open(takeOID(idOfOID), NpgsqlTypes.LargeObjectManager.READWRITE); //take picture oid from metod takeOID
byte[] buffer = new byte[32768];


using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
int read;
while ((read = lo.Read(buffer, 0, buffer.Length)) > 0)
{
ms.Write(buffer, 0, read);
} // Whend


img = System.Drawing.Image.FromStream(ms);
} // End Using ms


lo.Close();
trans.Commit();


if (connection.State != System.Data.ConnectionState.Closed)
connection.Close();
} // End Using trans


} // End lock connection


} // End Using connection


return img;
} // End Function GetLargeDrawing






public void DeleteLargeObject(int noid)
{
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(GetConnectionString()))
{
if (connection.State != System.Data.ConnectionState.Open)
connection.Open();


using (Npgsql.NpgsqlTransaction trans = connection.BeginTransaction())
{
NpgsqlTypes.LargeObjectManager lbm = new NpgsqlTypes.LargeObjectManager(connection);
lbm.Delete(noid);


trans.Commit();


if (connection.State != System.Data.ConnectionState.Closed)
connection.Close();
} // End Using trans


} // End Using connection


} // End Sub DeleteLargeObject