0

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

liv a
  • 3,232
  • 6
  • 35
  • 76

3 Answers3

1
  1. Read the file in the client.
  2. Escape the contents as bytea.
  3. Insert into database as normal.
Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
0

(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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • I'm not reading text but an image, that is why I'm using a bytea. I wish to do update my table directly from postgresql pg commander (a desktop ide which is connected to my test local DB). I'm trying to update something very specific and would rather do it with an sql statement than to write client side code to make this happen. – liv a Jun 02 '15 at 08:50
  • @liva You will then have to copy or link the images into the PostgreSQL data directory tree (not recommended!), use `lo_import`, or add a simple C extension with a modified `pg_read_binary_file` function that doesn't enforce the path restrictions. What you want to do is not directly supported in the stock DB. – Craig Ringer Jun 02 '15 at 08:52
  • @liva Also you *are* reading `text`; that's the data type returned by the `pg_read_file` function. Doing so on a PNG is incorrect and will produce truncated and/or mangled results, or encoding errors. Which was my point. You'd have to use `pg_read_binary_file` instead, *and* have the file within the data directory or disable the path checks. – Craig Ringer Jun 02 '15 at 08:53
0

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;
sampathlk
  • 338
  • 2
  • 17