Postgres RegExp validation in javascript

Question

When running a Postgres query using POSIX regular expression matching, the query may result in a invalid regular expression error if one of the RegExp patterns is invalid. If the regex query uses a database column, the error will occur if just one of the database rows contains an invalid RegExp pattern.

The problem is that validating values to be used for this type of query does not appear to be very straightforward. All of the solutions I have come across for validating RegExp patterns in javascript, including libraries such as regexpp do not appear to be reliable for testing whether Postgres would consider a given pattern to be valid.

Is there a way to test whether a pattern would be valid in a Postgres query, or is the only way to do this validation to actually run a Postgres query using the pattern?

jjanes · Accepted Answer · 2020-04-28 15:02:53Z

1

I don't think there is anything built-in that does it in a user-accessible way. You can create your own function to do this by catching the error.

create function true_on_error(text) returns bool language plpgsql as $$
BEGIN
    perform regexp_match('',$1); 
    return false;
exception when others then
    return true;
end$$;

answered Apr 28, 2020 at 15:02

jjanes

45k5 gold badges39 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

Postgres RegExp validation in javascript

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related