Export image file from bytea column

I am trying to export an image file from a PostgreSQL database.The users_data_circulation table has a photo (bytea) column.

My command:

copy (select encode(photo,'hex') from users_data_circulation limit 1)
TO '/tmp/imagetest.hext';


In operating system:

$> xxd -p -r /tmp/imagetest.hex > /tmp/imagetest.jpg
$> file /tmp/imagetest.jpg
/tmp/imagetest.jpg: ASCII TEXT


I can't open the jpg file. How can I convert this file to jpg?

devinim@devinimpostgresql:~$ hexdump -C /tmp/image.hex | more
00000000 5c 5c 33 37 37 5c 5c 33 33 30 5c 5c 33 37 37 5c |\\377\\330\\377\|
00000010 5c 33 34 30 5c 5c 30 30 30 10 4a 46 49 46 5c 5c |\340\\000.JFIF\\|
00000020 30 30 30 01 02 5c 5c 30 30 30 5c 5c 30 30 30 01 |000..\\000\\000.|
00000030 5c 5c 30 30 30 01 5c 5c 30 30 30 5c 5c 30 30 30 |\\000.\\000\\000|
00000040 5c 5c 33 37 37 5c 5c 33 34 31 5c 5c 30 30 30 5c |\\377\\341\\000\|
00000050 76 50 49 43 5c 5c 30 30 30 02 5c 6e 5c 6e 01 5c |vPIC\\000.\n\n.\|
00000060 5c 30 30 30 5c 5c 33 37 37 5c 5c 33 37 36 5c 5c |\000\\377\\376\\|
00000070 30 30 30 21 50 69 63 74 75 72 65 20 45 6c 65 6d |000!Picture Elem|
00000080 65 6e 74 73 2c 20 49 6e 63 2e 20 49 53 45 2f 53 |ents, Inc. ISE/S|
... continues like that


It's a horrible idea to store jpegs in the database.

That said, if you want to get it out of the database, you can use psql. This gets it out as hex.

psql -t -A -o "/tmp/imagetest.jpg" -c \
  "SELECT photo FROM users_data_circulation LIMIT 1";

You may also want to check out the large object.

StackExchange Database Administrators Q#151207, answer score: 4