1

I am working on limiting input in certain fields in my Postgres database, and I have a working solution using ENUM like so:

CREATE TYPE label AS ENUM ('foo','bar');

However, I was browsing around and looking at alternative ways of doing this using:

  • constraints (eg. CHECK (type IN ('foo','bar')) )
  • triggers (slow, from what I've read)
  • foreign keys

What are the advantages/disadvantages of using either of these methods, would any of these be more advisable? I am not necessarily asking which one is the 'best' method, only what kind of issues may arise from using these, ie. I know that using triggers might be quite slow compared to the other methods, but perhaps could deal with some more complex input conditions.

1

Maybe not all the differences, but most important in my opinion :

  1. enum vs text/varchar + check constraint.

    • enum values are internally stored as integer numbers, so different storage requirements
    • ordering : order of enum values corresponds order in which values were entering during creation type, so for enum ('small', 'big') small < big; for text column 'big'<'small'
  2. enum vs foreign key

    • performance-wise they should behave very similar, but you can bypass foreign key constraint by, for instance, disabling all triggers or changing session replication role
    • there is a bigger difference from data model perspective. If something has attributes, needs to be tracked, likely to be modified in either way, it should be stored as a separate entity in its own table. If it's just an attribute , e.g. color in many cases, then it's fine to store it as a enumerated type.

Triggers are just not the right tool to implement check constraints. They add complexity, hide logic, add maintenance overhead. In some cases they are "necessary evil" , but they definitely shouldn't be used just to do what check constraint can do.

  • Thank you for your answer! So when you are expecting change types in the future (eg. add more colours) or want to store corresponding values (eg. hex, rgb), then it would make sense to use a foreign key). I think in my case enum will probably be sufficient. – Julien Feb 10 at 15:58

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.