Re: Insert binary file into bytea where PG server does not have access to the file ?

On 12/3/21 12:13, David Gauthier wrote:
> 11.5 on linux
> 
> Big corp with an IT dept providing us with PG DBs running in instances 
> on their servers. (We/I amd not DBA).    We on the client side, the 
> "users" of these DBs, want to load binary files into bytea type 
> columns.  But the files we want to load are on disks that the server 
> does not have access to.
> 
> I googled around for a solution.  This failed...
> 
> insert into test_bytea (id,data) values 
> (1,pg_read_binary_file('/top/next.whatever/xyz.bin')::bytea);
> 
> ERROR:  could not stat file "/top/next.whatever/xyz.bin": No such file 
> or directory
> 
> Understandable because the server hasn't even mounted /top.
> 
> Is there a way to do what I want from the client side ?

Are you using some client side language(Java, Python, etc) library?

They generally have a way to deal with this:

Python via psycopg2:

https://www.psycopg.org/docs/usage.html#adapt-binary

Java via JDBC:

https://jdbc.postgresql.org/documentation/head/binary-data.html

> If the file is transformed into some titanic string on the client side, 
> then passed along over the net to the server in the insert statement, is 
> there a max string length that I need to worry about ?
> 
> Thanks


-- 
Adrian Klaver
adrian.klaver@aklaver.com