Re: Constraint: string length must be 32 chars - Mailing list pgsql-general

On Sat, Oct 16, 2010 at 12:15 PM, Alexander Farber
<alexander.farber@gmail.com> wrote:
> Hello,
>
> I'm trying to create a table, where md5 strings will serve as primary keys.
> So I'd like to add a constraing that the key length should be 32 chars long
> (and contain [a-fA-F0-9] only):

why don't you use the bytea type, and cut the key size down 50%?  You
can always format it going out the door if you want it displayed hex.
Besides being faster, you get to skip the 'is hex' regex.

create table foo(id bytea check(length(id) = 16));
insert into foo values (decode(md5('a'), 'hex')); -- if not using pgcrypto
insert into foo values (digest('b', 'md5')); -- if using pgcrypto (preferred)

select encode(id, 'hex') from foo;

merlin