Thread: 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
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?I think you regexp is too weak. So long as the value has a digit or period, it's good.
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
'^[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?I think you regexp is too weak. So long as the value has a digit or period, it's good.
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
'^[0-9.]+$' might work
On 10/29/2015 12:29 PM, Eric Schwarzenbach wrote:
Cool.
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?I think you regexp is too weak. So long as the value has a digit or period, it's good.
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
'^[0-9.]+$' might work
Note that this is a bottom-post forum and unfortunately so imho. Please don't top post.
Also thought I should mention that there is an ip address type if that's what you're trying to accomplish.
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
On 10/29/2015 05:01 PM, Tom Lane wrote:
arg. back to level two of regexpness for me :( I read it as ^[0-9.]+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 Thu, Oct 29, 2015 at 7:16 PM, Rob Sargent <robjsargent@gmail.com> wrote:
If there is no support for atomic groups you can try this: ^(?:\d++)(?:\.\d++)*$
On 10/29/2015 05:01 PM, Tom Lane wrote:arg. back to level two of regexpness for me :( I read it as ^[0-9.]+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
If I understand your regex needs correctly you want to allow digits separated by dots (like IPv4 octets) but never start w/ or end w/ a dot nor any non digit character other than a dot. If that's the case this may work. I say may because I'm using PCRE syntax and I don't know how much of it PostgreSQL supports.
^(?>\d+)(?>\.\d+)*$
And if there is no support for greedy quantifiers nor non capturing groups: ^(\d+)(\.\d++)*$
I hoped that helped.
Good luck,
Dane
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
Thank you! (Slapping head)
Your regexp seems to do the trick.
On 10/29/2015 01:49 PM, Rob Sargent wrote:
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?I think you regexp is too weak. So long as the value has a digit or period, it's good.
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
'^[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