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

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!

谢谢!

3 个解决方案

#1

use bytea (or Large Objects if you absolutely have to)

使用副茶(如果必须的话，也可以用大一点的东西)

#2

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?'

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

#3

Necromancing. Just for your information: storing files in your database will lead to a huge database size. You may not like that, for development, testing, backups, etc.

Necromancing。仅为您的信息:在数据库中存储文件将导致巨大的数据库大小。您可能不喜欢这样，因为开发、测试、备份等等。

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

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

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

在Postgre中没有缺省的BFILE/FileStream实现，但是您可以添加它:https://github.com/darold/external_file

And further information (if you speak french, otherwise good luck using google-translate) 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:

要回答一个非语言的问题:除了茶之外，对于非常大的文件，您可以使用lob:

// http://*.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

#1