PostgreSQL
PostgreSQL ARE- About
- Advanced regular expressions as used by the ~ operator and the regexp_ functions. By default a dot matches a newline and ^ and $ anchor at the ends of the text.
- Verified on
- PostgreSQL 18.7
Offsets are counted in code points. - Found in
- PostgreSQL ~, regexp_match(), SIMILAR TO (a different syntax)
its own documentation - Flags
iignore casennewlinewmultilinexverbose
Character classes 9 of 26
| Syntax | Matches | Notes | Try |
|---|---|---|---|
. |
Any characterAny single character. Whether that includes a newline depends on the dialect and its flags. | Matches a newline. |
test |
[abc]not[^abc] |
One of a set of characters | test | |
[a-z] |
One of a range of characters | test | |
[\]] |
Backslash escape inside a setInside a bracket expression a backslash takes the special meaning from the next character. Where this is missing, a backslash there is a member like any other. | test | |
[\d] |
Class escape inside a setA class escape such as the one for a digit, used as a member of a bracket expression. | test | |
[\D] |
Negated class escape inside a setA negated class escape, such as the one for anything but a digit, used as a member of a bracket expression. | test | |
\d[[:digit:]]not\D |
Digit | ASCII digits only: |
test |
\w[[:alnum:]_]not\W |
Word characterA letter, a digit or an underscore. | Unicode-aware: matches letters and digits of every script. |
test |
\s[[:space:]]not\S |
Whitespace | Also matches the no-break space. |
test |
| n/a | Horizontal whitespaceA space or a tab, but not a line break. | not in PostgreSQL Tried: |
who has it |
| n/a | Vertical whitespaceA line break character of any kind. | not in PostgreSQL Tried: |
who has it |
| n/a | Any character except newlineNever matches a newline, whatever the flags. | not in PostgreSQL Tried: |
who has it |
| n/a | Any line break sequenceOne line break, with CR LF taken as a single unit. | not in PostgreSQL Tried: |
who has it |
| n/a | Alphabetic character (shorthand) | not in PostgreSQL Tried: |
who has it |
| n/a | Lowercase letter (shorthand) | not in PostgreSQL Tried: |
who has it |
| n/a | Uppercase letter (shorthand) | not in PostgreSQL Tried: |
who has it |
| n/a | Hexadecimal digit (shorthand) | not in PostgreSQL Tried: |
who has it |
| n/a | Class intersectionCharacters that are in both classes. | not in PostgreSQL Tried: |
who has it |
| n/a | Class subtractionCharacters in the first class that are not in the second. | not in PostgreSQL Tried: |
who has it |
| n/a | Head-of-word character (shorthand)A letter or the underscore: what a word may start with. | not in PostgreSQL Tried: |
who has it |
| n/a | Octal digit (shorthand) | not in PostgreSQL Tried: |
who has it |
| n/a | Identifier characterA character allowed in an identifier. In Vim the set comes from the 'isident' option. | not in PostgreSQL Tried: |
who has it |
| n/a | Keyword characterA character allowed in a keyword. In Vim the set comes from the 'iskeyword' option. | not in PostgreSQL Tried: |
who has it |
| n/a | File name characterA character allowed in a file name. In Vim the set comes from the 'isfname' option. | not in PostgreSQL Tried: |
who has it |
| n/a | Printable characterA character that is displayed as itself. In Vim the set comes from the 'isprint' option. | not in PostgreSQL Tried: |
who has it |
| n/a | Any single byteOne code unit, even where that is part of a longer character, and even a newline. | not in PostgreSQL Tried: |
who has it |
Anchors 8 of 14
| Syntax | Matches | Notes | Try |
|---|---|---|---|
^ |
Start of the text or lineStart of the text. With a multiline flag, or always in some dialects, also the start of each line. | test | |
$ |
End of the text or line | Matches only at the very end, not before a final newline. |
test |
\A |
Start of the textStart of the text only, whatever the flags. | test | |
\Z |
End of the textThe very end of the text, whatever the flags. | test | |
| n/a | End of the text, or before a final newline | not in PostgreSQL Tried: |
who has it |
\y |
Word boundaryThe position between a word character and a non-word character or an end of the text. | Unicode-aware: a letter outside ASCII is a word character. |
test |
\Y |
Not a word boundary | test | |
\m[[:<:]] |
Start of a word | test | |
\M[[:>:]] |
End of a word | test | |
| n/a | Where the previous match endedThe position where the last match stopped, or the start of the text for the first match. | not in PostgreSQL Tried: |
who has it |
| n/a | Reset the start of the matchEverything matched so far is required but left out of the reported match. | not in PostgreSQL Tried: |
who has it |
| n/a | Start of symbolBefore a run of word and symbol characters, such as an identifier with an underscore in it. | not in PostgreSQL Tried: |
who has it |
| n/a | End of symbol | not in PostgreSQL Tried: |
who has it |
| n/a | Cursor positionWhere the editor's cursor is in the buffer being searched. | not in PostgreSQL doc Not run on an engine: taken from the documentation. |
who has it |
Quantifiers 8 of 9
| Syntax | Matches | Notes | Try |
|---|---|---|---|
X? |
Zero or one | test | |
X* |
Zero or more | test | |
X+ |
One or more | test | |
X{N} |
Exactly N times | test | |
X{N,} |
N or more times | test | |
X{0,M} |
At most M times | test | |
X{N,M} |
Between N and M times | test | |
X*?X+?X?? |
Lazy quantifierRepeat as few times as possible instead of as many as possible. | test | |
| n/a | Possessive quantifierRepeat as many times as possible and never give any of it back. | not in PostgreSQL Tried: |
who has it |
Groups and alternation 5 of 16
| Syntax | Matches | Notes | Try |
|---|---|---|---|
(X) |
Capture group | test | |
(?:X) |
Non-capturing group | test | |
| n/a | Named capture group | not in PostgreSQL Tried: |
who has it |
X|Y |
Alternation | Takes the longest match among the alternatives (leftmost-longest). |
test |
\N |
Backreference by numberThe same text that capture group N matched. | test | |
| n/a | Backreference by name | not in PostgreSQL Tried: |
who has it |
| n/a | Backreference by relative positionThe group N groups back from here. | not in PostgreSQL Tried: |
who has it |
| n/a | Atomic groupOnce the group has matched, the engine never tries a different way to match it. | not in PostgreSQL Tried: |
who has it |
| n/a | Branch reset groupEvery alternative numbers its capture groups from the same start. | not in PostgreSQL Tried: |
who has it |
| n/a | ConditionalMatch one pattern if a capture group took part, otherwise another. | not in PostgreSQL Tried: |
who has it |
| n/a | Recurse into the whole pattern | not in PostgreSQL Tried: |
who has it |
| n/a | Call a group as a subroutineMatch the pattern of capture group N again at this point, not the text it matched. | not in PostgreSQL Tried: |
who has it |
(?#TEXT) |
Comment | test | |
| n/a | Explicitly numbered groupA capture group that is given its number, whatever its position. | not in PostgreSQL Tried: |
who has it |
| n/a | Call an earlier group by relative positionMatch the pattern of the Nth group before this point again. | not in PostgreSQL Tried: |
who has it |
| n/a | Call a later group by relative positionMatch the pattern of the Nth group after this point here as well. | not in PostgreSQL Tried: |
who has it |
Lookaround 4 of 4
Escape sequences 14 of 16
| Syntax | Matches | Notes | Try |
|---|---|---|---|
\. |
Escape a special characterTake the next character literally. | test | |
| n/a | Quote a run of textTake everything between the markers literally. | not in PostgreSQL Tried: |
who has it |
\t |
Tab | test | |
\n |
Newline | test | |
\r |
Carriage return | test | |
\f |
Form feed | test | |
\v |
Vertical tab | test | |
\a |
Alert (bell) | test | |
\b[\b] |
Backspace | test | |
\e |
Escape character | test | |
\cC |
Control characterThe control character typed as Ctrl plus the letter. | test | |
\OOO |
Character by octal code | test | |
\xHH |
Character by two-digit hex code | test | |
\uHHHH\UHHHHHHHH |
Character by Unicode code point | test | |
| n/a | Character by Unicode name | not in PostgreSQL Tried: |
who has it |
\0 |
NUL characterThe character with code 0. |
doc Not run on an engine: taken from the documentation. |
Flags and modes 6 of 14
| Syntax | Matches | Notes | Try |
|---|---|---|---|
flag i(?i) |
Ignore case | test | |
flag w(?w)(?n)(?m) |
MultilineThe line anchors also match at the start and end of every line. | Flag |
test |
(?s) |
Dot matches newline | partial On by default: the dot matches a newline unless flag No flag is needed. Flag |
test |
flag x(?x) |
Free spacingWhitespace in the pattern is ignored, and a # starts a comment. | test | |
| n/a | Swap greedy and lazy | not in PostgreSQL Tried: |
who has it |
(?FLAGS) |
Turn a mode on inside the patternApplies from this point to the end of the pattern or the enclosing group. | test | |
| n/a | Turn a mode on in the middle of the patternThe mode applies from where the flag stands, not to what comes before it. Some dialects accept an inline flag only at the very start of the pattern. | not in PostgreSQL Tried: |
who has it |
| n/a | Turn a mode on for one group | not in PostgreSQL Tried: |
who has it |
| n/a | Turn a mode off inside the pattern | not in PostgreSQL Tried: |
who has it |
| n/a | ASCII-only classesMakes the shorthand classes such as the digit class match ASCII characters only. | not in PostgreSQL Tried: |
who has it |
| n/a | Unicode classesMakes the shorthand classes such as the digit class match by Unicode property. | not in PostgreSQL Tried: |
who has it |
| n/a | No automatic capturePlain parentheses group without capturing; only named groups capture. | not in PostgreSQL Tried: |
who has it |
TEXT ~ PATTERN COLLATE "de_DE" |
Locale-dependent classesClasses and case folding follow the current locale. | partial There is no mode letter: classes such as doc Not run on an engine: taken from the documentation. |
|
| n/a | Native character set rulesPerl's rules from before 5.14: the pattern and the text decide between native and Unicode rules. | not in PostgreSQL doc Not run on an engine: taken from the documentation. |
who has it |
POSIX named classes 1 of 1
| Syntax | Matches | Notes | Try |
|---|---|---|---|
[[:NAME:]]not[^[:NAME:]] |
POSIX named classA named set of characters, written inside a bracket expression. | 14 names: Not here: |
test |
POSIX collating symbols 2 of 2
| Syntax | Matches | Notes | Try |
|---|---|---|---|
[[=X=]] |
Equivalence classEvery character that collates the same as X in the current locale. | test | |
[[.NAME.]] |
Collating symbol by nameA single collating element, written by its POSIX name inside a bracket expression. | 127 names: |
test |
Unicode properties 0 of 4
| Syntax | Matches | Notes | Try |
|---|---|---|---|
| n/a | Unicode general category | not in PostgreSQL Tried: |
who has it |
| n/a | Unicode script | not in PostgreSQL Tried: |
who has it |
| n/a | Extended grapheme clusterOne character as a reader sees it: a base character with its combining marks. | not in PostgreSQL Tried: |
who has it |
| n/a | Unicode text boundaryA boundary as Unicode's segmentation rules define it, which is not the same as a boundary between word and non-word characters. | not in PostgreSQL Tried: |
who has it |
Syntax table classes 0 of 2
| Syntax | Matches | Notes | Try |
|---|---|---|---|
| n/a | Syntax classA character with the given role in the syntax table: whitespace, word, symbol, punctuation, a bracket. | not in PostgreSQL Tried: |
who has it |
| n/a | Character categoryA character in one of the editor's character categories, most of which are scripts. | not in PostgreSQL Tried: |
who has it |
Host language 8 of 15
| Syntax | Matches | Notes | Try |
|---|---|---|---|
'PATTERN'$$PATTERN$$E'PATTERN' |
Pattern in source codeHow a pattern is written where it is used: a regex literal with its delimiters, or the kind of string the documentation recommends. | No regex literal; a pattern is a string constant. In '...' a single quote is doubled and backslashes are literal while doc Not run on an engine: taken from the documentation. |
|
regexp_like(TEXT, PATTERN, FLAGS)regexp_replace(TEXT, PATTERN, REPLACEMENT, FLAGS)TEXT ~* PATTERN |
Flags outside the patternWhere flags such as case-insensitive go when they are not written inside the pattern. | FLAGS is a text string of option letters such as 'i', accepted as an optional argument by every doc Not run on an engine: taken from the documentation. |
|
| n/a | Compile once and reuseBuild a pattern object and keep it. | not in PostgreSQL Every operator and function takes the pattern as doc Not run on an engine: taken from the documentation. |
who has it |
TEXT ~ PATTERNregexp_like(TEXT, PATTERN)regexp_substr(TEXT, PATTERN)regexp_match(TEXT, PATTERN) |
Search a textTest whether the pattern matches anywhere in a text, or get the first match. | Not anchored: the pattern may match anywhere in TEXT. doc Not run on an engine: taken from the documentation. |
|
regexp_matches(TEXT, PATTERN, 'g') |
Every matchGet all the matches that do not overlap. | Returns one row per match, each a text array. Without the doc Not run on an engine: taken from the documentation. |
|
regexp_replace(TEXT, PATTERN, REPLACEMENT)regexp_replace(TEXT, PATTERN, REPLACEMENT, 'g') |
Replace matches | The plain form replaces the first match only; add the doc Not run on an engine: taken from the documentation. |
|
(regexp_match(TEXT, PATTERN))[NUM]regexp_substr(TEXT, PATTERN, 1, 1, '', NUM) |
Captured group, in codeRead what a group captured after a match. | partial By number only; the pattern syntax has no named groups. With no parenthesized subexpression in the pattern, element 1 of the doc Not run on an engine: taken from the documentation. |
|
\NUM |
Captured group, in replacement text | partial By number only, and only doc Not run on an engine: taken from the documentation. |
|
\& |
Whole match, in replacement text | A literal backslash in the replacement is written doc Not run on an engine: taken from the documentation. |
|
| n/a | Match once until resetA match operator that succeeds one time only, until it is reset. | not in PostgreSQL doc Not run on an engine: taken from the documentation. |
who has it |
| n/a | Keep the position after a failed matchIn a repeated search, a failed match does not send the next one back to the start. | not in PostgreSQL No search position is kept between calls. doc Not run on an engine: taken from the documentation. |
who has it |
| n/a | Keep the matched text for laterAsk for the matched text, and the text around it, to be saved. | not in PostgreSQL doc Not run on an engine: taken from the documentation. |
who has it |
| n/a | Interpolate and compile only onceA pattern literal with variables in it is built the first time and never again. | not in PostgreSQL doc Not run on an engine: taken from the documentation. |
who has it |
| n/a | Code inside a patternHost-language code that runs when the match reaches it. | not in PostgreSQL doc Not run on an engine: taken from the documentation. |
who has it |
| n/a | Pattern computed during the matchCode inside the pattern whose result is matched as a pattern at that point. | not in PostgreSQL doc Not run on an engine: taken from the documentation. |
who has it |
Every row was run on the engine named above, except the rows marked doc: those describe something no regex engine can run by itself, such as the syntax of the host language, and are taken from the documentation they link to. For a row marked "not in PostgreSQL", the spelling other dialects use was tried and the line starting "Tried" says what it did here; that shows the spelling does not work, not that no other spelling exists.