Domain check constraint not honored?

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.

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

On 10/29/2015 12:29 PM, Eric Schwarzenbach wrote:

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

Cool.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10/29/2015 05:01 PM, Tom Lane wrote:

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

arg. back to level two of regexpness for me :( I read it as ^[0-9.]+

On Thu, Oct 29, 2015 at 7:16 PM, Rob Sargent <robjsargent@gmail.com> wrote:

On 10/29/2015 05:01 PM, Tom Lane wrote:

Eric Schwarzenbach <subscriber@blackbrook.org> <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

arg. back to level two of regexpness for me :( I read it as ^[0-9.]+

​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+)*$

If there is no support for atomic groups you can try this:
^(?:\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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general