Question: I need to implement regex validation for value that will be used in my server side to get data where certain timestamp is older (smaller) than `now() - interval 'myValue'`.

pSQL interval function is explained [here](https://www.postgresql.org/docs/9.1/functions-datetime.html), and in short it can have values like `2 days`,`3 years`,`12 hours`, but also you can nest more different values like `2 days 6 hours 30 minutes` etc.

I currently have a regex `/^\d+\s(seconds?|minutes?|hours?|days?|weeks?|months?|years?)$/i` that accepts only one value (e.g. `2 days`), but can't figure out how to allow multiple values, and set a rule that a certain string from this group can only be repeated once or not at all.

This regex `/^\d+\s(seconds?|minutes?|hours?|days?|weeks?|months?|years?)(\s\d+\s(seconds?|minutes?|hours?|days?|weeks?|months?|years?))*$/i` allows nesting but also allows repetition of values e.g. `2 days 12 hours 6 hours 2 minutes` which will result in a fatal error in pSQL query.

I tried restricting repetition of values in this group with `\1` and `{0,1}` combination of regex operators but I just can't nail it precisely enough.

NOTE: Regex is unfortunately only way I can validate this value, since I don't have access to server-side controller which receives this value nor do I have access to client-side frontend of this form. I can't just throw exceptions or skip query because it is a part of important cron-job, and must be stable at all time.

(All I have access to is json schema of this value, and therefore can only define regex pattern for it)

Any help is appreciated, thanks.

Answer: 
You can use

```sql
^(?!.*(second|minute|hour|day|week|month|year).*\1)\d+\s+(?:second|minute|hour|day|week|month|year)s?(?:\s+\d+\s+(?:second|minute|hour|day|week|month|year)s?)*$

```

See the [regex demo](https://regex101.com/r/htldlR/2)

*Details*

* `^` - start of string
* `(?!.*(second|minute|hour|day|week|month|year).*\1)` - no `second`, `minute`, `hour` day`,` week`,` month`or`year` string repetition allowed in the whole string
* `\d+\s+(?:second|minute|hour|day|week|month|year)s?` - 1 or more digits, one or more whitespaces, then either `second`, `minute`, `hour`, `day`, `week`, `month` or `year`, and then an optional `s` letter
* `(?:\s+\d+\s+(?:second|minute|hour|day|week|month|year)s?)*` - zero or more repetition of one or more whitespaces followed with the pattern described above
* `$` - end of string.
Forget it. The only complete documentation of the supported values for `interval` is [the implementation](https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/timestamp.c;h=3a93e92e4036dc0afe0fcf81ca7251749b599166;hb=HEAD#l875) (the guts are in `ParseDateTime`).

Consider these:

```
SELECT INTERVAL '12 00:12:00';

     interval     
══════════════════
 12 days 00:12:00
(1 row)

SELECT INTERVAL '12 d 12 mins';

     interval     
══════════════════
 12 days 00:12:00
(1 row)

SELECT INTERVAL '3-2';

    interval    
════════════════
 3 years 2 mons
(1 row)

```

What I would do in your place is to write a function that casts the string to `interval` and catches and reports an error:

```
CREATE FUNCTION interval_ok(text) RETURNS boolean
   LANGUAGE plpgsql AS
$$BEGIN
   PERFORM CAST ($1 AS interval);
   RETURN TRUE;
EXCEPTION
   WHEN invalid_datetime_format THEN
      RETURN FALSE;
END;$$;

```