Re: Storing small image files

On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <
achill(at)matrix(dot)gatewaynet(dot)com> wrote:

> **
>
> why not bytea?
>

Hi Achilleas,

Actually I was asking if bytea is the correct datatype, and if so, would
someone mind providing a simple example of how to insert and retrieve the
image through the psql client.

Let's say I have an employee named Paul Kendell, who's employee ID is
880918. Their badge number will be PK00880918, and their badge photo is
named /tmp/PK00880918.jpg. What would the INSERT statement look like to put
that information into the security_badge table, and what would the SELECT
statement look like to retrieve that record?

Thanks for your time.

> much more control, much more information, IMHO.
>
> In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0,
>
> we have been storing everything binary in bytea's.
>
>
>
> There are downsides in both solutions, you just have to have good reasons
>
> to not use bytea.
>
>
>
> On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote:
>
> Good morning list,
>
> I am designing a system that will have a table for security badges, and we
> want to store the ID badge photo. These are small files, averaging about
> 500K in size. We have made the decision to store the image as a BLOB in the
> table itself for a variety of reasons. However, I am having trouble
> understanding just how to do that.
>
> The table structures:
>
> CREATE TABLE employee
> (
> employee_id INTEGER NOT NULL,
> employee_lastname VARCHAR(35) NOT NULL,
> employee_firstname VARCHAR(35) NOT NULL,
> employee_mi CHAR(1),
> PRIMARY KEY (employee_id)
> );
>
> CREATE TABLE security_badge
> (
> badge_number CHAR(10) NOT NULL,
> employee_id INTEGER NOT NULL
> REFERENCES employee(employee_id),
> badge_photo ????,
> PRIMARY KEY (badge_number)
> );
>
> What datatype should I use for the badge_photo (bytea?), and what are the
> commands to insert the picture accessing the server remotely through psql,
> and to retrieve the photos as well, please?
>
> Thanks,
> Nelson
>
>
>
> -
>
> Achilleas Mantzios
>
> IT DEV
>
> IT DEPT
>
> Dynacom Tankers Mgmt
>