On 10/29/2015 11:41 AM, Eric Schwarzenbach wrote:
> I have created a custom type as a domain based on text, which adds a
> check constraint using a regexp to limit it to containing digits and
> '.'. However I am finding I can add values with other characters to a
> column of this type. Is this to be expected for some reason?
>
> Or alternately, did I define the constraint wrong somehow? It is
> defined thus:
>
> CREATE DOMAIN hierpath AS text
> CHECK(
> VALUE ~ '[0-9.]+'
> );
>
> Checking the docs I'm pretty sure the '.' doesn't need escaping but I
> also tried a test leaving it out ( '[0-9]+') and the result is the
> same. It lets me store letters in a column defined to be of this type.
>
> The version() function tells me
> "PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
> 4.8.2-19ubuntu1) 4.8.2, 64-bit"
>
> Thanks,
> Eric
>
>
I think you regexp is too weak. So long as the value has a digit or
period, it's good.
'^[0-9.]+$' might work