CODEWITHSUNDEEP
sqlpostgresql
J.Olufsen
J.Olufsen

Reputation: 13915

ERROR: operator does not exist: numeric ~* unknown

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})$');

Upvotes: 2

Views: 12090

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324275

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.

Upvotes: 4

smathy
smathy

Reputation: 27961

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

Upvotes: 5

Related Questions