Storing small image files

why not bytea?
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

On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <
achill@matrix.gatewaynet.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.

Take a look here first :
http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level programming lang.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:

On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <achill@matrix.gatewaynet.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

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

On 5/9/2013 10:51 AM, Achilleas Mantzios wrote:

Take a look here first :

http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here :
http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level
programming lang.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:

On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios
<achill@matrix.gatewaynet.com <mailto:achill@matrix.gatewaynet.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

-

Achilleas Mantzios

IT DEV

IT DEPT

Dynacom Tankers Mgmt

To encode:

write_conn = Postgresql communication channel in your software that is
open to write to the table

char *out;
size_t out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data); /* You
have to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length,
&out_length); /* Convert */

That function allocates the required memory for the conversion. You now
have an encoded string you can "insert" or "update" with. Once you use
it in an "insert" or "update" function you then must "PQfreemem(out)" to
release the memory that was allocated.

To recover the data you do:

PQresult *result;

result = PQexec(write_conn, "select badge_photo blah-blah-blah");
....
out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned
piece of the tuple and convert it */

"out" now contains the BINARY (decoded) photo data. When done with it you:

PQfreemem(out) to release the memory that was allocated.

That's the rough outline -- see here:

http://www.postgresql.org/docs/current/static/libpq-exec.html

--
Karl Denninger
karl@denninger.net
/Cuda Systems LLC/

On 5/9/2013 11:12 AM, Karl Denninger wrote:

On 5/9/2013 10:51 AM, Achilleas Mantzios wrote:

Take a look here first :

http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here :
http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level
programming lang.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:

On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios
<achill@matrix.gatewaynet.com <mailto:achill@matrix.gatewaynet.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

-

Achilleas Mantzios

IT DEV

IT DEPT

Dynacom Tankers Mgmt

To encode:

write_conn = Postgresql communication channel in your software that is
open to write to the table

char *out;
size_t out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data); /* You
have to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length,
&out_length); /* Convert */

That function allocates the required memory for the conversion. You
now have an encoded string you can "insert" or "update" with. Once
you use it in an "insert" or "update" function you then must
"PQfreemem(out)" to release the memory that was allocated.

To recover the data you do:

PQresult *result;

result = PQexec(write_conn, "select badge_photo blah-blah-blah");
....
out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned
piece of the tuple and convert it */

"out" now contains the BINARY (decoded) photo data. When done with it
you:

PQfreemem(out) to release the memory that was allocated.

That's the rough outline -- see here:

http://www.postgresql.org/docs/current/static/libpq-exec.html

--
Karl Denninger
karl@denninger.net
/Cuda Systems LLC/

Oops -- forgot the second parameter on the "PQunescapebytea" call :-)

Yeah, that would be bad:

size_t out_length;

out = PQunescapeBytea(PQgetvalue(result, 0, 0), &out_length); /* Get the
returned piece of the tuple and convert it */

Otherwise, being binary data, how would you know how long it is? :-)

BTW I use these functions extensively in my forum code and have stored
anything from avatars (small image files) to multi-megabyte images.
Works fine. You have to figure out what the type of image is, of course
(or know that in advance) and tag it somehow if you intend to do
something like display it on a web page as the correct mime type content
header has to be sent down when the image is requested. What I do in my
application is determine the image type at storage time (along with
width and height and a few other things) and save it into the table
along with the data.

--
Karl Denninger
karl@denninger.net
/Cuda Systems LLC/

Karl Denninger escribió:

To encode:

write_conn = Postgresql communication channel in your software that is
open to write to the table

char *out;
size_t out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data); /* You
have to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length,
&out_length); /* Convert */

That function allocates the required memory for the conversion.

I think you're better off with PQexecParams() so that you don't have to
encode the image at all; just load it in memory and use it as a
parameter.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 5/9/2013 11:34 AM, Alvaro Herrera wrote:

Karl Denninger escribió:

To encode:

write_conn = Postgresql communication channel in your software that is
open to write to the table

char *out;
size_t out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data); /* You
have to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length,
&out_length); /* Convert */

That function allocates the required memory for the conversion.

I think you're better off with PQexecParams() so that you don't have to
encode the image at all; just load it in memory and use it as a
parameter.

Yeah, you can go that route too.

--
Karl Denninger
karl@denninger.net
/Cuda Systems LLC/

On Thu, May 9, 2013 at 10:51 AM, Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:

**

Take a look here first :

http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here :
http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level
programming lang.

Thanks Achilleas. I usually do the physical design in vi using sql scripts,
and I like to include a couple of inserts and selects to make sure
everything is going according to plan. It looks like I may just have to
work with a front-end developer for this particular instance. Of all the
stupid things, in all of my years doing this I've never once had to work
with storing binary files, other than years ago when I was studying for
some of the MySQL certs.

If I do happen to come up with a solution that works I will be sure to post
it here.

Thanks Karl, but I'm trying to do this from a psql shell. I can't use the C
functions there, can I?

On Thu, May 9, 2013 at 11:21 AM, Karl Denninger <karl@denninger.net> wrote:

2013/5/9 Nelson Green <nelsongreen84@gmail.com>

Thanks Karl, but I'm trying to do this from a psql shell. I can't use the
C functions there, can I?

On Thu, May 9, 2013 at 11:21 AM, Karl Denninger <karl@denninger.net>wrote:

On 5/9/2013 11:12 AM, Karl Denninger wrote:

On 5/9/2013 10:51 AM, Achilleas Mantzios wrote:

Take a look here first :

http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here :
http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level
programming lang.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:

On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <
achill@matrix.gatewaynet.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

-

Achilleas Mantzios

IT DEV

IT DEPT

Dynacom Tankers Mgmt

To encode:

write_conn = Postgresql communication channel in your software that is
open to write to the table

char *out;
size_t out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data); /* You have
to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length,
&out_length); /* Convert */

That function allocates the required memory for the conversion. You now
have an encoded string you can "insert" or "update" with. Once you use it
in an "insert" or "update" function you then must "PQfreemem(out)" to
release the memory that was allocated.

To recover the data you do:

PQresult *result;

result = PQexec(write_conn, "select badge_photo blah-blah-blah");
....
out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned
piece of the tuple and convert it */

"out" now contains the BINARY (decoded) photo data. When done with it
you:

PQfreemem(out) to release the memory that was allocated.

That's the rough outline -- see here:

http://www.postgresql.org/docs/current/static/libpq-exec.html

--
Karl Denninger
karl@denninger.net
*Cuda Systems LLC*

Oops -- forgot the second parameter on the "PQunescapebytea" call :-)

Yeah, that would be bad:

size_t out_length;

out = PQunescapeBytea(PQgetvalue(result, 0, 0), &out_length); /* Get the
returned piece of the tuple and convert it */

Otherwise, being binary data, how would you know how long it is? :-)

BTW I use these functions extensively in my forum code and have stored
anything from avatars (small image files) to multi-megabyte images. Works
fine. You have to figure out what the type of image is, of course (or know
that in advance) and tag it somehow if you intend to do something like
display it on a web page as the correct mime type content header has to be
sent down when the image is requested. What I do in my application is
determine the image type at storage time (along with width and height and a
few other things) and save it into the table along with the data.

--
Karl Denninger
karl@denninger.net
*Cuda Systems LLC*

You can try:

create or replace function bytea_import(p_path text)
returns bytea
language plpgsql as $$
declare
l_oid oid;
r record;
b_result bytea;
begin
p_result := '';
select lo_import(p_path) into l_oid;
for r in ( select data
from pg_largeobject
where loid = l_oid
order by pageno ) loop
b_result = b_result || r.data;
end loop;
perform lo_unlink(l_oid);
return b_result;
end;$$;

then when you want to insert a row in a table:

INSERT INTO security_badge VALUES('badge_no1', 1,
bytea_import('pathtothefile'))

On Thu, May 9, 2013 at 12:05 PM, Nelson Green <nelsongreen84@gmail.com>wrote:

On Thu, May 9, 2013 at 10:51 AM, Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:

**

Take a look here first :

http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here :
http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level
programming lang.

Thanks Achilleas. I usually do the physical design in vi using sql
scripts, and I like to include a couple of inserts and selects to make sure
everything is going according to plan. It looks like I may just have to
work with a front-end developer for this particular instance. Of all the
stupid things, in all of my years doing this I've never once had to work
with storing binary files, other than years ago when I was studying for
some of the MySQL certs.

If I do happen to come up with a solution that works I will be sure to
post it here.

OK, this is kind of convoluted, but I got a couple of test cases that work
for me. The steps to make the first one are below.

First I took one of the photos and shrunk it real small using GIMP. Then I
manually converted that to a base64 encoded text file:
/usr/bin/base64 < test.jpg > test.64

this created a 113 line base64 file. I then just put those 113 lines into
my insert statement:

INSERT INTO security_badge
VALUES
(
'PK00880918',
(SELECT employee_id
FROM employee
WHERE employee_lastname = 'Kendell' AND
employee_firstname = 'Paul'),
decode('<all 113 lines of the base64 string manually pasted from
test.64>','base64')
);

Then to retrieve the file:
\o /output.64
SELECT ENCODE((SELECT badge_photo
FROM security_badge
WHERE badge_number = 'PK00880918'),'BASE64');

That outputs a base64 string that matches test.64. Outputting that to a
file and then converting it back gives me my image:
/usr/bin/base64 -d < output.64 > newtest.jpg

Like I said, kind of crazy, but it satisfies me that my basic premise is
doable. I'll still get one of the front-end developers to whip out some PHP
just to be safe.

Thanks to all!

Hi Misa,

That gives me a "ERROR: must be superuser to use server-side lo_import()"
on the client. I think this is enforced to preserve file permissions on the
server? I appreciate the suggestion, and I will keep it, but I think I
found a solution that meets my immediate need.

Thanks!

On Thu, May 9, 2013 at 12:31 PM, Misa Simic <misa.simic@gmail.com> wrote:

2013/5/9 Nelson Green <nelsongreen84@gmail.com>

Hi Misa,

That gives me a "ERROR: must be superuser to use server-side lo_import()"
on the client. I think this is enforced to preserve file permissions on the
server? I appreciate the suggestion, and I will keep it, but I think I
found a solution that meets my immediate need.

Thanks!

On Thu, May 9, 2013 at 12:31 PM, Misa Simic <misa.simic@gmail.com> wrote:

2013/5/9 Nelson Green <nelsongreen84@gmail.com>

Thanks Karl, but I'm trying to do this from a psql shell. I can't use
the C functions there, can I?

On Thu, May 9, 2013 at 11:21 AM, Karl Denninger <karl@denninger.net>wrote:

On 5/9/2013 11:12 AM, Karl Denninger wrote:

On 5/9/2013 10:51 AM, Achilleas Mantzios wrote:

Take a look here first :

http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here :
http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level
programming lang.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:

On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <
achill@matrix.gatewaynet.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

-

Achilleas Mantzios

IT DEV

IT DEPT

Dynacom Tankers Mgmt

To encode:

write_conn = Postgresql communication channel in your software that is
open to write to the table

char *out;
size_t out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data); /* You
have to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length,
&out_length); /* Convert */

That function allocates the required memory for the conversion. You
now have an encoded string you can "insert" or "update" with. Once you use
it in an "insert" or "update" function you then must "PQfreemem(out)" to
release the memory that was allocated.

To recover the data you do:

PQresult *result;

result = PQexec(write_conn, "select badge_photo blah-blah-blah");
....
out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned
piece of the tuple and convert it */

"out" now contains the BINARY (decoded) photo data. When done with it
you:

PQfreemem(out) to release the memory that was allocated.

That's the rough outline -- see here:

http://www.postgresql.org/docs/current/static/libpq-exec.html

--
Karl Denninger
karl@denninger.net
*Cuda Systems LLC*

Oops -- forgot the second parameter on the "PQunescapebytea" call :-)

Yeah, that would be bad:

size_t out_length;

out = PQunescapeBytea(PQgetvalue(result, 0, 0), &out_length); /* Get
the returned piece of the tuple and convert it */

Otherwise, being binary data, how would you know how long it is? :-)

BTW I use these functions extensively in my forum code and have stored
anything from avatars (small image files) to multi-megabyte images. Works
fine. You have to figure out what the type of image is, of course (or know
that in advance) and tag it somehow if you intend to do something like
display it on a web page as the correct mime type content header has to be
sent down when the image is requested. What I do in my application is
determine the image type at storage time (along with width and height and a
few other things) and save it into the table along with the data.

--
Karl Denninger
karl@denninger.net
*Cuda Systems LLC*

You can try:

create or replace function bytea_import(p_path text)
returns bytea
language plpgsql as $$
declare
l_oid oid;
r record;
b_result bytea;
begin
p_result := '';
select lo_import(p_path) into l_oid;
for r in ( select data
from pg_largeobject
where loid = l_oid
order by pageno ) loop
b_result = b_result || r.data;
end loop;
perform lo_unlink(l_oid);
return b_result;
end;$$;

then when you want to insert a row in a table:

INSERT INTO security_badge VALUES('badge_no1', 1,
bytea_import('pathtothefile'))

If your file is not on the server - then you must encode your file to
base64...

Using aproach you have done (manually) - or with any language on client
machine...

cheers,

Misa

Thanks Misa, for confirming my suspicions about server permissions. Like I
said, what I came up will work for my simple needs. I have a script that
creates the table, inserts a test row, and successfully retrieves it, which
is all I need at this point.

I appreciate all the help from everyone.

On Thu, May 9, 2013 at 1:49 PM, Misa Simic <misa.simic@gmail.com> wrote:

On 5/9/2013 12:08 PM, Nelson Green wrote:

Thanks Karl, but I'm trying to do this from a psql shell. I can't use
the C functions there, can I?

On Thu, May 9, 2013 at 11:21 AM, Karl Denninger <karl@denninger.net
<mailto:karl@denninger.net>> wrote:

On 5/9/2013 11:12 AM, Karl Denninger wrote:

On 5/9/2013 10:51 AM, Achilleas Mantzios wrote:

Take a look here first :

http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here :
http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level
programming lang.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:

On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios
<achill@matrix.gatewaynet.com
<mailto:achill@matrix.gatewaynet.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

-

Achilleas Mantzios

IT DEV

IT DEPT

Dynacom Tankers Mgmt

To encode:

write_conn = Postgresql communication channel in your software
that is open to write to the table

char *out;
size_t out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data); /*
You have to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data,
badge_length, &out_length); /* Convert */

That function allocates the required memory for the conversion.
You now have an encoded string you can "insert" or "update"
with. Once you use it in an "insert" or "update" function you
then must "PQfreemem(out)" to release the memory that was allocated.

To recover the data you do:

PQresult *result;

result = PQexec(write_conn, "select badge_photo blah-blah-blah");
....
out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the
returned piece of the tuple and convert it */

"out" now contains the BINARY (decoded) photo data. When done
with it you:

PQfreemem(out) to release the memory that was allocated.

That's the rough outline -- see here:

http://www.postgresql.org/docs/current/static/libpq-exec.html

--
Karl Denninger
karl@denninger.net <mailto:karl@denninger.net>
/Cuda Systems LLC/

Oops -- forgot the second parameter on the "PQunescapebytea" call :-)

Yeah, that would be bad:

size_t out_length;

out = PQunescapeBytea(PQgetvalue(result, 0, 0), &out_length); /*
Get the returned piece of the tuple and convert it */

Otherwise, being binary data, how would you know how long it is? :-)

BTW I use these functions extensively in my forum code and have
stored anything from avatars (small image files) to multi-megabyte
images. Works fine. You have to figure out what the type of image
is, of course (or know that in advance) and tag it somehow if you
intend to do something like display it on a web page as the
correct mime type content header has to be sent down when the
image is requested. What I do in my application is determine the
image type at storage time (along with width and height and a few
other things) and save it into the table along with the data.

--
Karl Denninger
karl@denninger.net <mailto:karl@denninger.net>
/Cuda Systems LLC/

Someone else already got that, but -- no :-)

--
Karl Denninger
karl@denninger.net
/Cuda Systems LLC/

Hi Nelson. I worked with images and Postgresql, and want to add some comments:

On Thu, 9 May 2013 13:40:15 -0500
Nelson Green <nelsongreen84@gmail.com> wrote:

OK, this is kind of convoluted, but I got a couple of test cases that work
for me. The steps to make the first one are below.

First I took one of the photos and shrunk it real small using GIMP.

If you want to manipulate images automatically, don't use GIMP, use ImageMagick(for shell scripts) or OpenCV(for C sourcecode)

Then I
manually converted that to a base64 encoded text file:
/usr/bin/base64 < test.jpg > test.64

If you must to use the pg shell, perhaps coding Misa's function in other language (python f.ex.) allows you directly insert the bytea.

A use hint: disable toast compression for that table, images are already compressed, you don't need to waste time with it.

That outputs a base64 string that matches test.64. Outputting that to a
file and then converting it back gives me my image:
/usr/bin/base64 -d < output.64 > newtest.jpg

Like I said, kind of crazy, but it satisfies me that my basic premise is
doable. I'll still get one of the front-end developers to whip out some PHP
just to be safe.

Thanks to all!

---   ---
Eduardo Morras <emorrasg@yahoo.es>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Nelson Green wrote on 09.05.2013 19:05:

On Thu, May 9, 2013 at 10:51 AM, Achilleas Mantzios wrote:
then here :
http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

Thanks Achilleas. I usually do the physical design in vi using sql
scripts, and I like to include a couple of inserts and selects to
make sure everything is going according to plan. It looks like I may
just have to work with a front-end developer for this particular
instance. Of all the stupid things, in all of my years doing this
I've never once had to work with storing binary files, other than
years ago when I was studying for some of the MySQL certs.

The thread from DbForums links to the SQL tool I'm maintaining, SQL Workbench/J: http://www.sql-workbench.net

I assume the image files are stored on the client where you run the SQL rather than on the Postgres server, right?

If you can use a different SQL client than psql, then SQL Workbench is probably the easiest way to solve this.
I added that "extended" (proprietary) SQL syntax exactly for this purpose.

Your statement would become:

INSERT INTO security_badge
VALUES
(
'PK00880918',
(SELECT employee_id
FROM employee
WHERE employee_lastname = 'Kendell' AND
employee_firstname = 'Paul'),
{$blobfile='/path/to/test.jpg'}
);

The /path/to/test.jpg is local to the computer where SQL Workbench is running.

SQL Workbench is not only usable as a GUI application but also in console mode (similar to psql then) or in batch mode to run the scripts automatically.

For bulk loading the SQL Workbench specific "WbImport" command also supports text files that contain a filename to be stored into a bytea column.

All this support is for bytea only, it does not support "large objects" but as you are storing "small images", bytea is the better choice anyway.

Regards
Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/5/10 Eduardo Morras <emorrasg@yahoo.es>

Hi Nelson. I worked with images and Postgresql, and want to add some
comments:

On Thu, 9 May 2013 13:40:15 -0500
Nelson Green <nelsongreen84@gmail.com> wrote:

OK, this is kind of convoluted, but I got a couple of test cases that

work

for me. The steps to make the first one are below.

First I took one of the photos and shrunk it real small using GIMP.

If you want to manipulate images automatically, don't use GIMP, use
ImageMagick(for shell scripts) or OpenCV(for C sourcecode)

Then I
manually converted that to a base64 encoded text file:
/usr/bin/base64 < test.jpg > test.64

If you must to use the pg shell, perhaps coding Misa's function in other
language (python f.ex.) allows you directly insert the bytea.

well, with pl/python there is more power - no need to use
lo_largobejects... However solution for him is not in function because of
file is on different machine then Postgres...

I think Nelson has found solution what works for him...

But now, what Thomas Keller suggested sounds as very good approach if user
doesn't want to write his own client code...

A use hint: disable toast compression for that table, images are already

On Fri, May 10, 2013 at 2:59 AM, Eduardo Morras <emorrasg@yahoo.es> wrote:

Hi Nelson. I worked with images and Postgresql, and want to add some
comments:

On Thu, 9 May 2013 13:40:15 -0500
Nelson Green <nelsongreen84@gmail.com> wrote:

OK, this is kind of convoluted, but I got a couple of test cases that

work

for me. The steps to make the first one are below.

First I took one of the photos and shrunk it real small using GIMP.

If you want to manipulate images automatically, don't use GIMP, use
ImageMagick(for shell scripts) or OpenCV(for C sourcecode)

Then I
manually converted that to a base64 encoded text file:
/usr/bin/base64 < test.jpg > test.64

If you must to use the pg shell, perhaps coding Misa's function in other
language (python f.ex.) allows you directly insert the bytea.

A use hint: disable toast compression for that table, images are already
compressed, you don't need to waste time with it.

Thanks Eduardo, I should have caught that, but good advice. I appreciate it.