Re: Storing small image files - Mailing list pgsql-general
| From | Nelson Green |
|---|---|
| Subject | Re: Storing small image files |
| Date | |
| Msg-id | CAGo-KZ=JsEr7b6EhuUC6g9QSs2jcgfGkb04jcLRzc1jk=US3Zw@mail.gmail.com Whole thread Raw |
| In response to | Re: Storing small image files (Nelson Green <nelsongreen84@gmail.com>) |
| Responses |
Re: Storing small image files
|
| List | pgsql-general |
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
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
pgsql-general by date: