0

In a PostgreSQL database, I have a column named metaid of type text whose value contains digits as specified in the third edition of XML 1.0 standard:

Digit ::= [#x0030-#x0039] | [#x0660-#x0669] | [#x06F0-#x06F9] | [#x0966-#x096F] | [#x09E6-#x09EF] | [#x0A66-#x0A6F] | [#x0AE6-#x0AEF] | [#x0B66-#x0B6F] | [#x0BE7-#x0BEF] | [#x0C66-#x0C6F] | [#x0CE6-#x0CEF] | [#x0D66-#x0D6F] | [#x0E50-#x0E59] | [#x0ED0-#x0ED9] | [#x0F20-#x0F29]

In the documentation of PostgreSQL for SIMILAR TO expressions, matching against such Unicode characters by their range is not mentioned.

I need to put a CHECK constraint on the column for ensuring the pattern of the entered value conforms to the required format.

How can I accomplish this?

asked Sep 3, 2020 at 11:09

1 Answer 1

2

Here is a check constraint:

CHECK (col ~ '^[0-9\u0660-\u0669\u06F0-\u06F9\u0966-\u096F]+$')

The pattern starts with "beginning of string" (^) and ends with "end of string" ($)and allows one or more "digits" in between.

^ and $ are necessary, otherwise the pattern would match abc123efg.

answered Sep 3, 2020 at 11:28
0

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.