postgresql: insert multiple images into bytea column?

Question

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

Why would you ever do this? Create a one-to-many association table if you need to accommodate this. Trying to cram multiple images a single binary column will require writing your own framing and encoding method and will probably render them an unusable, unmaintainable mess. Add some kind of positional index column if you need one for ordering. — tadman
– tadman, Commented Dec 10, 2014 at 21:25

harmic · Accepted Answer · 2014-12-10 22:51:55Z

2

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:

It will provide greater flexibility when querying out the images
It will most likely perform better, when it comes to aspects such as updating the images, inserting an image, deleting images.
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:

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.
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.
Adding, updating, or removing an image will result in all of the images getting rewritten (since they are all stored in an array).

answered Dec 10, 2014 at 22:51

harmic

31k6 gold badges75 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user299709 Over a year ago

I won't be removing or editing individual images, I just need a place to dump all the images. When I query, I will need all images or non at all. The images are anywhere from 100kb~4mb.

Guillaume Mercey · Accepted Answer · 2014-12-10 21:26:25Z

2

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

answered Dec 10, 2014 at 21:26

Guillaume Mercey

4194 silver badges14 bronze badges

Comments

Tometzky · Accepted Answer · 2014-12-10 21:29:56Z

0

You probably should save your data like this:

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

answered Dec 10, 2014 at 21:29

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

Collectives™ on Stack Overflow

postgresql: insert multiple images into bytea column?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related