you could store the pkey as a md5 or sha1
of the image's data. or any of the other large hashing algorithms.
that way your index only has to compare 32 or 40 bytes instead of kilobytes per
row.
as for the main color, you could generate histogram-like
columns (or even a single column) with the relative strengths of each channel
and store them into a smallint or bitstring. you could then do whatever you
wanted per channel and it could be indexed.
From:
pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Jean-Christophe Roux
Sent: Thursday, October 05, 2006
7:55 PM
To: Alexander Staubo
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [GENERAL] Storing
images in PostgreSQL databases (again)
Why would I set a bytea
column (containing picures) as a primary key? Because I want to be sure that
the same image is inserted only once (that requirement comes from a real
project) and using a primary key for that purpose makes sense to me.
Am I going to retrieve an image row by its image data? I would certainly like!
For instance, I would like to get the pictures whose main color is green
(requirement from a real project), and a
select * from images where main_color(image) = 'green' would be nice.
JCR
----- Original Message
----
From: Alexander Staubo <alex@xxxxxxxxxxxxxxx>
To: Jean-Christophe Roux <jcxxr@xxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx
Sent: Thursday, October 5, 2006 7:35:04 PM
Subject: Re: [GENERAL] Storing images in PostgreSQL databases (again)
On Oct 6, 2006, at 01:29 , Jean-Christophe Roux wrote:
> By the way, is it practical to set a bytea column (containing
> pictures) as primary key? That would severely slow down many
> operations I guess.
Why would you? It's possible, but completely impractical, since
image
data typically exceeds the index page size. Moreover, are you
really
going to retrieve an image row by its image data?
Alexander.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to
majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing
list cleanly
|
