PostgreSQL 9.x - pg_read_binary_file & inserting files into bytea

Question

I have been looking everywhere (google, stackoverflow, etc.) for some documentation on how to use the PostgreSQL pg_read_binary_file() function.

The only meaningful thing I can find is this page in the official documentation.

Every time I try to use this function I get an error.

For example:

    SELECT pg_read_binary_file('/some/path/and/file.gif');
    ERROR:  absolute path not allowed

or

    SELECT pg_read_binary_file('file.gif');
    ERROR:  could not stat file "file.gif": No such file or directory

Do I need to have my file in a specific directory for Postgres to have access to it? If so what directory?

If it matters, the reason I am looking at this function is because I am trying to insert a file into the database without doing crazy things.

Are you aware that the file is supposed to be located on the server, not on the client where you run the SQL statement? Additionally: "only files within the database cluster directory and the log_directory can be accessed" — user330315
– user330315, Commented Apr 16, 2013 at 23:04
From docs "Only files within the database cluster directory and the log_directory can be accessed. Use a relative path for files in the cluster directory, and a path matching the log_directory configuration setting for log files.". The file must be on PGDATA directory. — Guedes
– Guedes, Commented Apr 16, 2013 at 23:08
@losthorse could you post this an an answer? I would be happy to upvote. — Chris Travers
– Chris Travers, Commented May 16, 2013 at 9:26
@ChrisTravers - I have added the answer... I'm glad you found this helpful. — losthorse
– losthorse, Commented May 18, 2013 at 1:00

Community · Accepted Answer · 2017-04-13 12:42:36Z

7

As stated by @a_horse_with_no_name and @guedes the solution is to ensure that the file being uploaded is on the server in the PGDATA directory.

The postgres documentation does state the file location as a requirement.

Additionally, I made a symlink from another directory to the PGDATA directory so that I would not disturb any of the postgres data structure. This seems to be working well and I don't have to do any of the above crazy things.

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered May 18, 2013 at 0:58

losthorse

1,5701 gold badge13 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL 9.x - pg_read_binary_file & inserting files into bytea

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related