ERROR: operator does not exist: numeric ~* unknown

Question

I need to created domain in PostgreSQL for a price. The price must be NUMERIC(9,2) where 9 is precision and 2 - scale. When trying to create domain getting:

ERROR: operator does not exist: numeric ~* unknown Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

QUERY:

CREATE DOMAIN d_price AS NUMERIC(9, 2) NOT NULL 
CONSTRAINT Product_price_can_contain_only_double_precision_value
CHECK(VALUE ~*'^(([[:digit:]])+\.([[:digit:]]){2})$');

Cast your numeric to TEXT. Eg. (VALUE::TEXT ~ '^(([[:digit:]])+\.([[:digit:]]){2})$'). You do not need the case-insensitive symbole (*) either. More details at postgresql.org/docs/current/static/functions-matching.html — bma
– bma, Commented Dec 30, 2013 at 23:13
What's the use of the check constraint? That does not make any sense. A numeric column won't let you store non-numeric values anyway — user330315
– user330315, Commented Dec 30, 2013 at 23:30
@a_horse_with_no_name: I need numeric value in specific form, something like: [0-9]+\.[0-9]{2} . Ex: 25.10 ; 199999.00 — J.Olufsen
– J.Olufsen, Commented Dec 30, 2013 at 23:39
Agree with @a_horse_with_no_name -- numbers do not have a format except when converted to text in a particular way. The constraint does nothing. — David Aldridge
– David Aldridge, Commented Dec 30, 2013 at 23:52
I think this might be a case of trying to define a number in terms of text, when range may be a better option. What is the min and max value of this numeric? A range check constraint makes much more sense for a number. Would that work? — Scott Marlowe
– Scott Marlowe, Commented Dec 31, 2013 at 1:39

smathy · Accepted Answer · 2013-12-30 23:18:24Z

5

You need your numeric value as a string before you can use the string operator, change your VALUE to: CAST(VALUE AS TEXT)

answered Dec 30, 2013 at 23:18

smathy

28k5 gold badges51 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

fabioresner Over a year ago

For those who use it with JPA, you can use CAST(VALUE AS string).

Craig Ringer · Accepted Answer · 2013-12-31 02:25:32Z

Your CHECK constraint is nonsensical, because it applies after the value has already been converted to NUMERIC by the database engine's number parser.

VALUE ~*'^(([[:digit:]])+\.([[:digit:]]){2})$')

appears to say "one or more leading digits, a period, and exactly two trailing digits". You can't do that check in any useful way once the number has already been parsed. Observe:

regress=> SELECT NUMERIC(18,2) '1', NUMERIC(18,2) '1.12345';
 numeric | numeric 
---------+---------
    1.00 |    1.12
(1 row)

No matter what the input is, if it fits inside the NUMERIC you've specified, it'll be extended to fit. If it doesn't fit the NUMERIC size you've given it'll produce an error before your CHECK constraint ever runs.

Collectives™ on Stack Overflow

ERROR: operator does not exist: numeric ~* unknown

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related