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

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