Save image in database and give Restore in database with Postgresql

Question

What is the best way to save image in Postgresql database?

Currently I have an OID to save, I can use normally, both to save and to recover, but when I give Restore in my database, the following error occurs

pg_restore: [arquivador (bd)] Erro ao PROCESSAR TOC: pg_restore: [arquivador (bd)] Erro no registro do TOC 583; 1247 49153 DOMAIN lo postgres pg_restore: [arquivador (bd)] could not execute query: ERROR: type "lo" already exists Comando foi: CREATE DOMAIN lo AS oid;

That’s because I’m doing the restore by his own pgadmin 9.4, and when I try to give restore in my application C# also does not work. There is another way to save the image in the database or some way to give restore to make it work?

Answer 1

As I mentioned in the comments, I use the column as bytea (byte array) which can store not only images, but also files of any format.

Follow the code as I use it, but this one I put is just an example using the library System.Data.ODBC of .NET. Other excerpts irrelevant to the situation were discarded, focusing only on the selection, insertion and update of the bytea. If you use another method for connection to the bank, it is very similar and I believe you will not have problems with converting the code to your reality.

A Class For the example object:

 public class Cadastro
 {
    public byte[] Foto { get; set; }
 }

A Class where one would have communication with the bank:

public class CadastroRepositorio
{
    public List<Cadastro> Select()
    {
        List<Cadastro> retorno = new List<Cadastro>();
        string sql = @"Select foto from tabela where id = 1;";
        using (OdbcConnection conexao = new OdbcConnection("string de conexao"))
        {
            conexao.Open();
            using (OdbcCommand cmd = new OdbcCommand(sql, conexao))
            {
                using (OdbcDataReader dr = cmd.ExecuteReader())
                {
                    Cadastro obj;
                    while (dr.Read())
                    {
                        obj = new Cadastro();
                        obj.Foto = new byte[0];
                        obj.Foto = (dr[0].ToString().Length != 0 ? (byte[])dr[0] : obj.Foto);
                        //obj.Foto = (dr["foto"].ToString().Length != 0 ? (byte[])dr["foto"] : obj.Foto); 
                        retorno.Add(obj);
                    }

                    dr.Close();
                }
            }
            conexao.Close();
        }
        return retorno;
    }

    public int Insert(Cadastro obj)
    {
        int r = 0;
        string sql = @"insert into tabela (foto) value (?);";
        using (OdbcConnection conexao = new OdbcConnection("string de conexao"))
        {
            conexao.Open();
            using (OdbcCommand cmd = new OdbcCommand(sql, conexao))
            {
                OdbcParameter param = new OdbcParameter("foto", OdbcType.Binary);
                param.Value = (obj.Foto == null ? null : ( obj.Foto.Length == 0 ? null : obj.Foto));
                cmd.Parameters.Add(param);
                r = cmd.ExecuteNonQuery();
            }
            conexao.Close();
        }
        return r;
    }

    public int Update(Cadastro obj)
    {
        int r = 0;
        string sql = @"update tabela set foto = ? where id = 1;";
        using (OdbcConnection conexao = new OdbcConnection("string de conexao"))
        {
            conexao.Open();
            using (OdbcCommand cmd = new OdbcCommand(sql, conexao))
            {
                OdbcParameter param = new OdbcParameter("foto", OdbcType.Binary);
                param.Value = (obj.Foto == null ? null : (obj.Foto.Length == 0 ? null : obj.Foto));
                cmd.Parameters.Add(param);
                r = cmd.ExecuteNonQuery();
            }
            conexao.Close();
        }
        return r;
    }
}

Finally, if necessary, the byte[] for a Image using the following function: (This is not my doing)

    /// <summary>
    /// Converte um array de bytes em um objeto System.Drawing.Image
    /// </summary>
    /// <param name="pic">byte[]</param>
    /// <returns>System.Drawing.Image</returns>
    public static Image ConvertByteToImage(byte[] pic)
    {
        if (pic != null)
        {
            try
            {
                MemoryStream ImageDataStream = new MemoryStream();
                ImageDataStream.Write(pic, 0, pic.Length);
                ImageDataStream.Position = 0;
                pic = System.Text.UnicodeEncoding.Convert(Encoding.Unicode, Encoding.Default, pic);
                System.Drawing.Image img = System.Drawing.Image.FromStream(ImageDataStream);
                return img;
            }
            catch
            {
                return null;
            }

        }
        else return null;
    }

And the reverse function, to convert an image to byte[]:

    /// <summary>
    /// Converte um objeto System.Drawing.Image em um array de bytes
    /// </summary>
    /// <param name="foto">System.Drawing.Image</param>
    /// <returns>byte[]</returns>
    public static byte[] ConvertImageToByte(System.Drawing.Image foto)
    {
        if (foto == null)
            return null;
        Bitmap bmp = new Bitmap(foto);
        MemoryStream stream = new MemoryStream();
        bmp.Save(stream, ImageFormat.Png);
        stream.Flush();
        byte[] pic = stream.ToArray();
        return pic;
    }

Now the backup and Restore part:

To back up, I use the following command:

C:\Progra~2\PostgreSQL\9.1\bin\pg_dump -h [host] -p 5432 -U postgres --inserts -c -f D:\PostgreSQL\Backups\[arquivo de backup].dump [nome database]

To do the Store, I use the following command:

C:\Progra~2\PostgreSQL\9.1\bin\psql -U postgres -d [nome database] -f D:\PostgreSQL\Backups\[arquivo de backup].dump

Works perfectly, not only for images but also for files (.doc, .zip, .exe, etc). I hope I’ve helped.

Obs.: Work in environment Windows