Обсуждение: Domain check constraint not honored?

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 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

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

Thank you! (Slapping head)
Your regexp seems to do the trick.

On 10/29/2015 01:49 PM, Rob Sargent wrote:

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

Rob Sargent wrote:

> Also thought I should mention that there is an ip address type if that's
> what you're trying to accomplish.

Looking at the domain name, I wonder whether contrib/ltree would be
helpful.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 10/29/2015 03:44 PM, Alvaro Herrera wrote:
> Rob Sargent wrote:
>
>> Also thought I should mention that there is an ip address type if that's
>> what you're trying to accomplish.
> Looking at the domain name, I wonder whether contrib/ltree would be
> helpful.
Very observant! This is indeed part of a hierarchical data solution.
Thanks for the suggestion, but this solution has been in place and
working for a few years already. I'm not positive, but I think I may
have looked at ltree when I first implemented it, but decided against it
in favor of a transitive closure table, augmented with this path for
sorting.
(I do sometimes wonder whether the transitive closure table is worth it
vs just a materialized path.)

I'm just now converting that path to use a custom domain (along with
custom operators) instead of just being a string. (The custom operators
allow the paths to be sorted properly without each segment needing to be
filled with zeros to a fixed length.) (Also FWIW, the latest version of
this regexp is now '^([0-9]+.)*[0-9]+$')



Cheers,

Eric

On 10/29/2015 04:29 PM, Eric Schwarzenbach wrote:
> On 10/29/2015 03:44 PM, Alvaro Herrera wrote:
> segment needing to be filled with zeros to a fixed length.) (Also
> FWIW, the latest version of this regexp is now '^([0-9]+.)*[0-9]+$')
>
>
>
> Cheers,
>
> Eric
>
>
So it can start with a dot, but not end with one?

Eric Schwarzenbach <subscriber@blackbrook.org> writes:
> ... (Also FWIW, the latest version of
> this regexp is now '^([0-9]+.)*[0-9]+$')

Um, that's not gonna do what you want at all.  Outside brackets, a dot
is a wildcard.  (Regex syntax is a mess :-(.)

            regards, tom lane

Eric Schwarzenbach <subscriber@blackbrook.org> writes:

... (Also FWIW, the latest version of 
this regexp is now '^([0-9]+.)*[0-9]+$')

Um, that's not gonna do what you want at all.  Outside brackets, a dot
is a wildcard.  (Regex syntax is a mess :-(.)
		regards, tom lane

On 10/29/15 5:29 PM, Eric Schwarzenbach wrote:
> I'm just now converting that path to use a custom domain (along with
> custom operators) instead of just being a string. (The custom operators
> allow the paths to be sorted properly without each segment needing to be
> filled with zeros to a fixed length.) (Also FWIW, the latest version of
> this regexp is now '^([0-9]+.)*[0-9]+$')

Have you looked at using int[]? It wouldn't be hard to go between that
and the string representation using string_to_array() and
array_to_string(). There's also a chance that eventually you'd be able
to do FKs on it.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

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

On 10/30/2015 09:53 AM, Jim Nasby wrote:
> On 10/29/15 5:29 PM, Eric Schwarzenbach wrote:
>> I'm just now converting that path to use a custom domain (along with
>> custom operators) instead of just being a string. (The custom operators
>> allow the paths to be sorted properly without each segment needing to be
>> filled with zeros to a fixed length.) (Also FWIW, the latest version of
>> this regexp is now '^([0-9]+.)*[0-9]+$')
>
> Have you looked at using int[]? It wouldn't be hard to go between that
> and the string representation using string_to_array() and
> array_to_string(). There's also a chance that eventually you'd be able
> to do FKs on it.
Do you mean making the column int[] and converting to string if needed,
or converting the string column to int[] for the purposes of the
ordering algorithm?

I did consider making the column int[] instead of a string, and it would
probably be slightly more efficient in a few ways. My main hesitations
were having to revisit the code that puts together this path, and
compatibility (at the moment we're only using PostgreSQL but we've had
to run on other databases for certain clients in the past, and in theory
are open to that in the future). I realize the compatibility concern is
a little humorous in light of having gone down the
custom-operator-for-sorting route, but I can always fall back to 0 padding.

On 10/30/15 12:50 PM, Eric Schwarzenbach wrote:
> On 10/30/2015 09:53 AM, Jim Nasby wrote:
>> On 10/29/15 5:29 PM, Eric Schwarzenbach wrote:
>>> I'm just now converting that path to use a custom domain (along with
>>> custom operators) instead of just being a string. (The custom operators
>>> allow the paths to be sorted properly without each segment needing to be
>>> filled with zeros to a fixed length.) (Also FWIW, the latest version of
>>> this regexp is now '^([0-9]+.)*[0-9]+$')
>>
>> Have you looked at using int[]? It wouldn't be hard to go between that
>> and the string representation using string_to_array() and
>> array_to_string(). There's also a chance that eventually you'd be able
>> to do FKs on it.
> Do you mean making the column int[] and converting to string if needed,
> or converting the string column to int[] for the purposes of the
> ordering algorithm?

Changing the storage would probably be best because int[] should be
significantly smaller than a string representation.

> I did consider making the column int[] instead of a string, and it would
> probably be slightly more efficient in a few ways. My main hesitations
> were having to revisit the code that puts together this path, and

You could create a writable view (with the same name as the table) that
would convert the string representation to an array using
string_to_array(blah, '.'). That should be pretty fast.

> compatibility (at the moment we're only using PostgreSQL but we've had
> to run on other databases for certain clients in the past, and in theory
> are open to that in the future). I realize the compatibility concern is
> a little humorous in light of having gone down the
> custom-operator-for-sorting route, but I can always fall back to 0 padding.

BTW, IIRC ORDER BY int[] will do what you want here.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com