Re: Constraint: string length must be 32 chars

Alexander Farber wrote:
> 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):
>
> create table gps (
> id varchar(32) primary key CONSTRAINT char_length(id)==32,
> stamp timestamp DEFAULT current_timestamp,
> pos point);

If you want to use a text type for this and you are restricting the character
repertoire anyway, which presumably you'd need a regex for, then use the same
regex to restrict the length too.

Adjusting your example:

create table gps (
id text primary key CONSTRAINT id ~ '^[a-fA-F0-9]{32}$',
stamp timestamp DEFAULT current_timestamp,
pos point
);

But I would further restrict this to just upper or just lowercase, so that the
values compare correctly as text; you then have to upper/lower your inputs:

create table gps (
id text primary key CONSTRAINT id ~ '^[A-F0-9]{32}$',
stamp timestamp DEFAULT current_timestamp,
pos point
);

I would further recommend turning the above into a separate data type,
especially if you'd otherwise be using that constraint in several places, like this:

CREATE DOMAIN md5text
AS text
CHECK (
VALUE IS NOT NULL
AND
VALUE ~ '^[A-F0-9]{32}$'
)
DEFAULT '00000000000000000000000000000000';

create table gps (
id md5text primary key,
stamp timestamp DEFAULT current_timestamp,
pos point
);

This all being said, I would go with the other advice you mentioned and use a
bitstring or numeric type to represent the md5 rather than using text.

-- Darren Duncan