CODEWITHSUNDEEP
imagepostgresqlbytea
liv a
liv a

Reputation: 3340

postgresql update a bytea field with image from desktop

I'm trying to write an update sql statement in postgresql (pg commander) that will update a user profile image column

I've tried this:

update mytable set avatarImg = pg_read_file('/Users/myUser/profile.png')::bytea where userid=5;

got ERROR: absolute path not allowed

Upvotes: 0

Views: 9585

Answers (3)

sampathlk
sampathlk

Reputation: 338

pg_read_file can read the files only from the data directory path, if you would like to know your data directory path use:

SHOW data_directory;

For example it will show,

/var/lib/postgresql/data

Copy you file to the directory mentioned. After the you can use only file name in your query.

UPDATE student_card SET student_image = pg_read_file('up.jpg')::bytea;

or can use pg_read_binary_file function.

UPDATE student_card SET student_image = pg_read_binary_file('up.jpg')::bytea;

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324485

(Elaborating on Richard's correct but terse answer; his should be marked as correct):

pg_read_file is really only intended as an administrative tool, and per the manual:

The functions shown in Table 9-72 provide native access to files on the machine hosting the server. Only files within the database cluster directory and the log_directory can be accessed.

Even if that restriction didn't apply, using pg_read_file would be incorrect; you'd have to use pg_read_binary_file. You can't just read text and cast to bytea like that.

The path restrictions mean that you must read the file using the client application as Richard says. Read the file from the client, set it as a bytea placement parameter in your SQL, and send the query.

Alternately, you could use lo_import to read the server-side file in as a binary large object, then read that as bytea and delete the binary large object.

Upvotes: 0

Richard Huxton
Richard Huxton

Reputation: 22893

  1. Read the file in the client.
  2. Escape the contents as bytea.
  3. Insert into database as normal.

Upvotes: 1

Related Questions