Custom type that expects valid TLD

[fnando]

Right now this column type isn't that useful, because it accepts a@a as a valid e-mail address, which it is according to the spec, but is not really valid on the real world.

Would be nice to have another data type that validates the hostname as a valid TLD.

[petere]

How would you check for a valid TLD, seeing that they seem to change every couple of weeks?

[fnando]

Yeah, that sucks. I have some libs that fetch that list and I update it when a new released is published. I can live without those bleeding edge TLDs.

[fnando]

Here's the TLD list I use: https://data.iana.org/TLD/tlds-alpha-by-domain.txt

[chungy]

What happens if IANA drops a TLD that you happen to use in your database? What about people that use an alternate root and expect their system to store data with TLDs not part of IANA?

Maybe you just want to make sure someone didn't mistype "COM" as "CMO" -- but going on that path, you can't be sure that an entry with "UA" wasn't just a mistype for "US". It doesn't really seem useful to make this so restrictive, and creates too many faulty assumptions by doing so.

[petere]

Well, this is not a new category of questions. See also: Which macaddr prefixes are valid? Which time zones are valid? Which IP addresses are valid? And in the realm of email, which addresses can actually be delivered to? And as mentioned in the comment above, what happens to existing data when the definitions change? The answers to these questions should not be hardcoded into the base data type. They can be handled in application-specific ways using facilities such as domains, triggers, user-defined functions, and so on.

[pvh]

@petere What if the TLDs were listed as a table within the extension's namespace and were enforced via an optional domain constraint?

[petere]

I don't think it can work that way. How would you invalidate existing constraints if that table changes? (For that reason, check constraints cannot refer to outside data.)