CODEWITHSUNDEEP
postgresql
user299709
user299709

Reputation: 5412

postgresql: insert multiple images into bytea column?

I know you can store an image file to the bytea column, but what if you need to store multiple images associated with that row in that column? Are you forced to create another table that makes reference to this row? I am trying to avoid that as each row is part of a tabular data, I need an image to appear in the specific column order specified, but I can't know ahead of time how many images there will be or the order of columns.

text | text | img | text | img

or it could be

img | text | img | text | text

or

img | img | img | img | img | text | text

Upvotes: 0

Views: 2417

Answers (3)

harmic
harmic

Reputation: 30587

It is possible to create a column which would contain an array of bytea values, eg:

CREATE TABLE test ( id serial, images bytea[], .. other columns ...);

However, I would recommend creating a separate table to contain the images, for the following reasons:

  1. It will provide greater flexibility when querying out the images

  2. It will most likely perform better, when it comes to aspects such as updating the images, inserting an image, deleting images.

  3. Depending on your use case, queries are likely to perform better as well.

The reason for the performance aspects is to do with how columns are stored. If the total size of a row is less than the page size (typically 8KB) then these columns are stored in-line. If the size exceeds 8KB, then the largest columns will be compressed and/or moved into separate "TOAST" tables.

If you store all of the images in an array inside the column, then:

  1. From the database perspective, you will only ever be able to retrieve ALL of the images, not just one of them. Even though you can select a single image, the database will have to read all of them into memory to find that image for you.

  2. If the size of the rows is <8kb (ie. if the images are small) then you won't even be able to query other table columns without the database reading the images into memory as well.

  3. Adding, updating, or removing an image will result in all of the images getting rewritten (since they are all stored in an array).

Upvotes: 2

Tometzky
Tometzky

Reputation: 23890

You probably should save your data like this:

row int not null,
col int not null,
textdata text,
imgdata bytea,
primary key (row, col),

Upvotes: 0

Guillaume Mercey
Guillaume Mercey

Reputation: 409

It is better to create a relation table and to keep one image per row.

Upvotes: 2

Related Questions