Обсуждение: Insert binary file into bytea where PG server does not have access to the file ?
Insert binary file into bytea where PG server does not have access to the file ?
От
David Gauthier
Дата:
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);
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 ?
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 ?
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
Re: Insert binary file into bytea where PG server does not have access to the file ?
От
Adrian Klaver
Дата:
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
Re: Insert binary file into bytea where PG server does not have access to the file ?
От
Adrian Klaver
Дата:
On 12/3/21 12:42, David Gauthier wrote: Please reply to list also. Ccing list > Ya, perl, how about perl ? > That would depend on what Perl database driver you are using. Assuming DBD::Pg: https://metacpan.org/pod/DBD::Pg Then search on binary. Folks with Perl experience can provide more detailed information. -- Adrian Klaver adrian.klaver@aklaver.com