2

I came across a question about a data type for MD5 values. The recommendation in that question states using uuid as type for that field.

The argumentation is solid. But I find that this might be confusing to someone unaware of the points revealed in the above question. The decision to use uuid as MD5 type is something that I would like to see in some form of documentation in any project that does this.

As a "helper" to whoever looks into the DB Schema, one could argue to create a md5 domain inheriting from uuid. That way, the column types would be much more explicit and the intention would be much clearer.

But it would use a domain for no other reason than to "rename" an existing type.

It's still something that would make sense to document properly. But that documentation could be centralised to a section explaining the domains in the DB. So you wouldn't gain anything in terms of documentation. The advantage I see is, as mentioned, that the intention becomes clear when looking at the table DDL.

Are there any downsides to this?

  • 1
    Using a domain for that makes sense. One downside is however, that you can no longer change the definition of a domain as long as it is used by at least one column. E.g. a domain defined as varchar(10) can not be changed to varchar(20) if it's used. – a_horse_with_no_name Oct 20 '16 at 12:20
1

While it's not a bad idea to store the meta-data as the type name, I would not. That's a slippery slope that will lead to bizzare place where ints become more specific types that provide nothing (insofar as constraints, what DOMAINs are for). This meta-data about what is in the column belongs in the column name, not the type.

Further you can explain it with a PostgreSQL COMMENT

COMMENT ON COLUMN mycatalog.myschema.mytable IS
  $$ Not really a UUID, just suing it to store an MD5 $$;

Inability to change underlying types

From a_horse_with_no_name,

  • You can no longer change the definition of a domain as long as it is used by at least one column. E.g. a domain defined as varchar(10) can not be changed to varchar(20) if it's used.

Other problems

  • There are some oddities regarding ranges over domains

  • For any functions that use the domain, you will want to ensure that the types from literals are in the domain type.

    CREATE DOMAIN foo AS text;
    CREATE FUNCTION f(foo)
    RETURNS bool
    AS $$
      SELECT false
    $$ LANGUAGE SQL;
    
    SELECT f('foo'); -- returns false
    
    CREATE FUNCTION f(text)
    RETURNS bool
    AS $$
      SELECT true;
    $$ LANGUAGE SQL;
    
    SELECT f('foo'); -- returns true
    

I use domains liberally, but only to add constraints so that they're centrally managed and not in a hodgepodge of CHECK statements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.