Storing a file hash as primary key

[Eduardo Pérez Ureta]

I was wondering what the best way is to store a file hash (MD5 or SHA1)
and make it primary key indexed.
I have seen some people storing the hexadecimal encoded MD5 in a
CHAR(32) but it may be a better idea to use a CHAR(16) without encoding
the string, but that may cause some problems.

What do you recommend?
Do you have any experiences storing file hashes in a database?
Do you know any good opensource software that stores file hashes in the
database (to take a look)?

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Greg Stark]

Eduardo Pérez Ureta <ep****@it.uc3m.es> writes:

I was wondering what the best way is to store a file hash (MD5 or SHA1)
and make it primary key indexed.
I have seen some people storing the hexadecimal encoded MD5 in a
CHAR(32) but it may be a better idea to use a CHAR(16) without encoding
the string, but that may cause some problems.

I would say either char(32) or bytea(16). Not char(16) since you don't want to
treat the raw binary data using any specific character encoding or sort it
according to any locale specific rules etc.

Personally I would have preferred bytea(16) but for some reason the php
drivers seem to jut drop NULL there when I try to store raw binary md5 hashes.
So for now I just declared it bytea with no length specification and store the
hex encoded hash.

If anyone knows how to get Pear::DB to store binary data in a bytea column, by
all means.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Joe Conway]

Greg Stark wrote:

Personally I would have preferred bytea(16) but for some reason the php
drivers seem to jut drop NULL there when I try to store raw binary md5 hashes.
So for now I just declared it bytea with no length specification and store the
hex encoded hash.

If anyone knows how to get Pear::DB to store binary data in a bytea column, by
all means.

Did you try using pg_escape_bytea()?

Joe
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)