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(dot)klaver(at)aklaver(dot)com