database 如何在 Postgres 中使用 Blob 数据类型

how to use Blob datatype in Postgres

提问by Jayashri

I am using a Postgresql database in my rails application. To store large file or data in database I have used blob data type in MySql.

我在 Rails 应用程序中使用 Postgresql 数据库。为了在数据库中存储大文件或数据，我在 MySql 中使用了 blob 数据类型。

For Postgres which data type I have to use instead of blob in MySql?

对于 Postgres，我必须在 MySql 中使用哪种数据类型而不是 blob？

Thanks!

谢谢！

回答by Jayashri

use bytea(or Large Objectsif you absolutely have to)

使用bytea（或大对象，如果你绝对需要）

回答by frankhommers

I think this is the most comprehensive answer on the PostgreSQL wiki itself: https://wiki.postgresql.org/wiki/BinaryFilesInDB

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

Read the part with the title 'What is the best way to store the files in the Database?'

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

回答by Stefan Steiger

Storing files in your database will lead to a huge database size. You may not like that, for development, testing, backups, etc.

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

Instead, you'd use FileStream (SQL-Server) or BFILE (Oracle).

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

There is no default-implementation of BFILE/FileStream in Postgres, but you can add it: https://github.com/darold/external_file

Postgres 中没有 BFILE/FileStream 的默认实现，但您可以添加它：https: //github.com/darold/external_file

And further information (in french) can be obtained here:
http://blog.dalibo.com/2015/01/26/Extension_BFILE_pour_PostgreSQL.html

更多信息（法语）可以在这里获得：http:
//blog.dalibo.com/2015/01/26/Extension_BFILE_pour_PostgreSQL.html

---

---

To answer the acual question:
Apart from bytea, for really large files, you can use LOBS:

要回答实际问题：
除了bytea，对于非常大的文件，您可以使用LOBS：

// 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