PNG images in postgresql database

user_10546779 0
Viewed 144

I would like to know how can I insert an image "bytea" into a table of my postgresql database, with the condition that the image must be a png file.

Here is the table:

"id_category" SERIAL,
"category_name" TEXT,
"category_image" bytea,
constraint id_cat_pkey primary key ("id_category"))without oids;

How can I ensure that any files inserted into the table must only be PNG files?

postgresql sql
1 Answers

The best you can do is to write a check constraint that extracts the first few bytes from the bytea and checks for the appropriate magic number:

CHECK (substr(image, 1, 8) = BYTEA '\x89504E470D0A1A0A')

Of course there is no guarantee that no other file starts with these bytes, but that is the best you can do short of checking the whole file for consistency.

user_64643080
Related