Drawbacks of Using DOMAINS to Store Metadata About Column in PostgreSQL

domainpostgresql

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?

Best Answer

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.

Related Question