Can I Store Image Files in PG?

CarlosinFL · #1 (**permalink**) 05-12-11, 14:07

I'm building a database for users in the office and someone asked if there was a way to have the badge photos stored in the database with their user info. I created the following table but I have no idea what I should set the data type as so I can store user.jpg files in PostgreSQL:

Code:

florida=# \d users
           Table "public.users"
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 id       | integer               | not null
 fname    | character varying(20) | not null
 lname    | character varying(20) | not null
 email    | character varying(50) | not null
 office   | integer               | not null
 position | character varying(40) | not null
 dob      | date                  |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE, btree (email)

I would generally just add a new column but I have no idea what I should use as the data type. How can I do this or does SQL not support this?

Code:

ALTER TABLE users ADD COLUMN pic ?

shammat · #2 (**permalink**) 05-12-11, 14:51

Use the bytea datatype

CarlosinFL · #3 (**permalink**) 05-12-11, 15:04

Is it common in database world to store binary image files in the database itself or do I store them on a file server and link the path to the file in SQL? If the latter, then would I still use 'bytea' data type or was that only if I store the binary files into the database itself?

shammat · #4 (**permalink**) 05-12-11, 15:44

Quote:

Originally Posted by CarlosinFL

Is it common in database world to store binary image files in the database itself or do I store them on a file server and link the path to the file in SQL?

There are several reasons for both approaches. Personally I like to keep them in the database.

Arguments for keeping binary data in the database

everything in one place
storing them is part of the transaction
no need to do file system houskeeping
no need to worry about distributing files across directories because filesystems tend to have problems if you store millions of files in a single directory

It does have some disadvantages though:

database backup gets harder because the size quickly grows
no direct access from e.g. a web server. So if the images are used in a web application the application server would need to server them. That can be a performance problem (though there are ways to still make that fast)
you can't easily access them from other applications. e.g. a word processor

The main reason why most peoply strongly believe images should not be stored in the database is mainly the size of the database (backups) and speed of access.

If you do choose to store them in the filesystem, simply create a varchar (text) column to hold the path. bytea should only be used for "real" binary data.

Internet Services

Web Development

Digital Marketing

Consumer Tech