how to use Blob datatype in Postgres

Question

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  For Postgres which data type I have to use instead of blob in MySql

User · Answer

use bytea  or Large Objects if you absolutely have to

User · Answer

I think this is the most comprehensive answer on the PostgreSQL wiki itself  https   wiki postgresql org wiki BinaryFilesInDB  Read the part with the title  What is the best way to store the files in the Database

User · Answer

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     There is no default-implementation of BFILE FileStream in Postgres  but you can add it  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    To answer the acual question  Apart from bytea  for really large files  you can use 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  gt  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  lt  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    gt  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

[database] how to use Blob datatype in Postgres

Examples related to database

Examples related to postgresql