Re: Constraint: string length must be 32 chars

[Darren Duncan <darren@xxxxxxxxxxxxxxxx>]

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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general