Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Unanswered: Can I Store Image Files in PG?

    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 ?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Use the bytea datatype

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    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?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •