show client_encoding;

set client_encoding='UTF8';

show server_encoding;

SELECT E'\\xDEADBEEF';

 

CREATE TABLE tbl_image_t

(

  id bigserial NOT NULL,

  name character varying(32),

  thumb bytea

)

insert into "tbl_image_t" values(4,'中文', E'\\x15191a1b')

SELECT id, name,  encode(thumb, 'hex')  FROM "tbl_image_t";

.net写入读取

public void Save(string imageName, string imageFilePath)        {            using (NpgsqlConnection pgConnection = new NpgsqlConnection(ConnString))            {                try                {                    using (FileStream pgFileStream = new FileStream(imageFilePath, FileMode.Open, FileAccess.Read))                    {                        using (BinaryReader pgReader = new BinaryReader(new BufferedStream(pgFileStream)))                        {                            byte[] pgByteA = pgReader.ReadBytes(Convert.ToInt32(pgFileStream.Length));                            using (NpgsqlCommand pgCommand = new NpgsqlCommand("INSERT INTO \"tbl_image_t\" (name, thumb) SELECT @Name, @Image", pgConnection))                            {                                pgCommand.Parameters.AddWithValue("@Name", imageName);                                pgCommand.Parameters.AddWithValue("@Image", pgByteA);                                try                                {                                    pgConnection.Open();                                    pgCommand.ExecuteNonQuery();                                }                                catch                                {                                    throw;                                }                            }                        }                    }                }                catch                {                    throw;                }            }        }        public Image Get(string imageName)        {            using (NpgsqlConnection pgConnection = new NpgsqlConnection(ConnString))            {                try                {                    using (NpgsqlCommand pgCommand = new NpgsqlCommand("SELECT thumb FROM \"tbl_image_t\" WHERE name=@imageName;", pgConnection))                    {                        pgCommand.Parameters.AddWithValue("@imageName", imageName);                        try                        {                            pgConnection.Open();                            Byte[] productImageByte = (Byte[])pgCommand.ExecuteScalar();                            if (productImageByte != null)                            {                                using (Stream productImageStream = new System.IO.MemoryStream(productImageByte))                                {                                    return Image.FromStream(productImageStream);                                }                            }                        }                        catch                        {                            throw;                        }                    }                }                catch                {                    throw;                }            }            return null;        }

 

blob

private static void WriteLargeObject()        {            newcon.Open();            NpgsqlTransaction transaction = newcon.BeginTransaction();            LargeObjectManager largeObjectManager = new LargeObjectManager(newcon);            int noid = largeObjectManager.Create(LargeObjectManager.READWRITE);            LargeObject lo = largeObjectManager.Open(noid, LargeObjectManager.READWRITE);            FileStream fs = File.OpenRead(FileName);            byte[] buf = new byte[fs.Length];            fs.Read(buf, 0, (int)fs.Length);            lo.Write(buf);            lo.Close();            transaction.Commit();            transaction = newcon.BeginTransaction();            lo = largeObjectManager.Open(noid, LargeObjectManager.READWRITE);            FileStream fsout = File.OpenWrite(FileName + "database");            buf = lo.Read(lo.Size());            fsout.Write(buf, 0, (int)lo.Size());            fsout.Flush();            fsout.Close();            lo.Close();            transaction.Commit();            newcon.Close();            DeleteLargeObject(noid);            Console.WriteLine("noid: {0}", noid);        }        public static void DeleteLargeObject(Int32 noid)        {            //NpgsqlConnection conn = new NpgsqlConnection("server=localhost;user id=npgsql_tests;password=npgsql_tests");            newcon.Open();            NpgsqlTransaction t = newcon.BeginTransaction();            LargeObjectManager largeObjectManager = new LargeObjectManager(newcon);            largeObjectManager.Delete(noid);            t.Commit();            newcon.Close();        }