PostgreSQL里存儲二進制類型有兩種：bytea類型或lo（Large Object）類型
bytea類型存儲列要小于1GB
lo擴展類型可參考：
http://www.postgres.cn/docs/11/lo.html

byte類型測試

創(chuàng)建表結(jié)構(gòu)
postgres=# create table test_bytea(id int,info bytea);

生成1GB文件
postgres=# \! dd if=/dev/zero of=test_bytea bs=1k count=1048576 && sync
1048576+0 records in
1048576+0 records out
1073741824 bytes (1.1 GB) copied, 3.42339 s, 314 MB/s

文件入庫
postgres=# insert into test_bytea values(1,pg_read_binary_file('/home/postgres/test_bytea'));
ERROR:  requested length too large

生成文件稍小于1GB
postgres=# \! dd if=/dev/zero of=test_bytea bs=1k count=1048575 && sync
1048576+0 records in
1048576+0 records out
1073741824 bytes (1.1 GB) copied, 3.42339 s, 314 MB/s

入庫測試
postgres=# insert into test_bytea values(1,pg_read_binary_file('/home/postgres/test_bytea'));
INSERT 0 1

JDBC-Java文件入庫

public static void main(String[] args) throws Exception{ 
		File file = new File("c:/work/movie.mkv");//文件大小不超過1G
		FileInputStream fis = new FileInputStream(file); 
		
		Class.forName("org.postgresql.Driver");
		Connection con = DriverManager.getConnection("jdbc:postgresql://192.168.99.223:6000/postgres","XXX","XXX");
		PreparedStatement ps = con.prepareStatement("insert into test_bytea values (2,?)");
		
		ps.setBinaryStream(1, fis, (long)file.length()); 
		ps.execute(); 
		con.close();
}

JDBC-Java讀數(shù)據(jù)輸出到文件

 	public static void main(String[] args) throws Exception{ 
		Class.forName("org.postgresql.Driver");
		Connection con = DriverManager.getConnection("jdbc:postgresql://192.168.99.223:6000/postgres","XXX","XXX");
		PreparedStatement ps = con.prepareStatement("select info from test_bytea where id=2;");
		
		ResultSet rs = ps.executeQuery();
         while (rs.next()) {
            OutputStream ops = null;
            InputStream ips = null;
            File file = new File("c:/work/out.mkv");
            
            ips = rs.getBinaryStream(1);
            byte[] buffer = new byte[1024*1024];
            ops = new FileOutputStream(file);
            for (int i; (i = ips.read(buffer)) > 0;)
            {
                 ops.write(buffer, 0, i);
                 ops.flush();
            }
            ops.close();
        }
        con.close();
	}

lo類型測試

創(chuàng)建擴展及表結(jié)構(gòu)
postgres=# create extension lo;
postgres=# create table test_lo(id int,info lo);

創(chuàng)建管理觸發(fā)器
postgres=# create trigger test_lo before UPDATE OR DELETE ON test_lo FOR EACH ROW EXECUTE FUNCTION lo_manage(info);
需要建立觸發(fā)器使用lo_manage進行管理，以免形成孤立大對象

生成2G文件
postgres=# \! dd if=/dev/zero of=test_lo bs=1M count=2048 && sync
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 2.21557 s, 969 MB/s

文件入庫
postgres=# insert into test_lo values(1,lo_import('/home/postgres/test_lo'));

導出lo文件
postgres=# select lo_export(test_lo.info,'/home/postgres/test_ext_lo') from test_lo where id=1;
 lo_export 
-----------
         1
(1 row)
postgres=# \! ls -lh test_ext_lo
-rw-r--r-- 1 postgres postgres 2.0G Mar  3 18:22 test_ext_lo

lo類型原生表只存儲一個oid作為引用指針，并不實際存數(shù)據(jù)
postgres=# \dt pg_largeobject*
                    List of relations
   Schema   |          Name           | Type  |  Owner   
------------+-------------------------+-------+----------
 pg_catalog | pg_largeobject          | table | postgres
 pg_catalog | pg_largeobject_metadata | table | postgres
(2 rows)

pg_largeobject_metadata存儲lo類型的元數(shù)據(jù)信息，包括類型的宿主和訪問權(quán)限
postgres=# \d pg_largeobject_metadata
      Table "pg_catalog.pg_largeobject_metadata"
  Column  |   Type    | Collation | Nullable | Default 
----------+-----------+-----------+----------+---------
 oid      | oid       |           | not null | 
 lomowner | oid       |           | not null | 
 lomacl   | aclitem[] |           |          | 
Indexes:
    "pg_largeobject_metadata_oid_index" UNIQUE, btree (oid)

pg_largeobject存儲實際數(shù)據(jù)，最終使用byeta類型
postgres=# \d pg_largeobject
         Table "pg_catalog.pg_largeobject"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 loid   | oid     |           | not null | 
 pageno | integer |           | not null | 
 data   | bytea   |           | not null | 
Indexes:
    "pg_largeobject_loid_pn_index" UNIQUE, btree (loid, pageno)

lo類型使用注意事項
需要創(chuàng)建觸發(fā)器，對update和delete操作使用lo_manage函數(shù)管理，不然會產(chǎn)生孤立大對象。

Jdbc-Java Large Object使用例子參考

保持聯(lián)系

從2019年12月開始寫第一篇文章，分享的初心一直在堅持，本人現(xiàn)在組建了一個PG樂知樂享交流群，歡迎關(guān)注我文章的小伙伴進群吹牛嘮嗑，交流技術(shù)，互贊文章。

如果群二維碼失效可以加我微信。