There’s an age old question asking is it better to store images offline in a file system or online inside a database table?
Regardless where you stand on the issue, I thought it’d be fun to see how I could store an image inside of a YugabyteDB YSQL table.
Turns out that it’s very easy to do so!
In YugabyteDB, we can use the BYTEA data type to represent binary string of bytes (octets). Binary strings allow zeros (0) and non-printable bytes.
yugabyte=# CREATE TABLE images (id INT, jpg BYTEA);
CREATE TABLE
Next we have to load an image file into the BYTEA column.
For the example, I will use the following image that is stored as a JPG file:
Postgres/YSQL provides many build-in System Administration Functions, one of them being pg_read_binary_file, which we will use to load the image into our table.
yugabyte=# \! ls -lrth /root/yb.jpg
-rw-r--r-- 1 root root 12K Jan 31 18:26 /root/yb.jpg
yugabyte=# INSERT INTO images SELECT 1, pg_read_binary_file('/root/yb.jpg');
INSERT 0 1
We aren’t able to display image in ysqlsh, but we can use tools like DBeaver for that!
Have Fun!
