Oracle中NOT REGEXP_LIKE语句迁移至PostgreSQL的技术咨询

Got it, let's convert that Oracle UPDATE query to work seamlessly in PostgreSQL. Here's the breakdown and the final code:

Key Differences to Note #

• Oracle's REGEXP_LIKE function is replaced with PostgreSQL's ~ operator (this does case-sensitive regex matching; use ~* if you ever need case-insensitive, though your pattern doesn't require it here).
• The regex pattern itself stays identical—PostgreSQL uses POSIX regex which aligns perfectly with the pattern you're using for MM/DD/YYYY validation.
• CONCAT behaves the same in both databases, so no changes needed there (it handles NULL values gracefully by treating them as empty strings).

Final PostgreSQL Query #

UPDATE TBL_RESOURCEAVAILUPLOAD
SET IS_VALID_AVAILABILITY = 0,
    ERROR_CODE = 'DATA-ERR',
    ERROR_MESSAGE = CONCAT(ERROR_MESSAGE, ' Start date or end date is not in proper format. It should be in MM/DD/YYYY format.,')
WHERE (NOT (STARTDATE ~ '(0[0-9]|1[0-2])/(0[0-9]|1[0-9]|2[0-9]|3[0-1])/([0-9]{4})')
       OR NOT (ENDDATE ~ '(0[0-9]|1[0-2])/(0[0-9]|1[0-9]|2[0-9]|3[0-1])/([0-9]{4})'));

Quick Note #

If STARTDATE or ENDDATE are stored as actual DATE types (not strings) in your PostgreSQL table, you'll want to convert them to strings first using TO_CHAR to apply the regex check—like TO_CHAR(STARTDATE, 'MM/DD/YYYY') ~ '...'. But based on the original Oracle query, it looks like these are string columns, so the above code should work as-is.

内容的提问来源于stack exchange，提问作者lakshmi sowmya