6. Pattern matching:
PostgreSQL provides three methods to implement pattern matching: SQL LIKE operator, more recent SIMILAR TO operator, and POSIX-style regular expressions.
1. LIKE：
Copy the codeThe code is as follows:
string LIKE pattern [ ESCAPE escape-character ]
string NOT LIKE pattern [ ESCAPE escape-character ]
Each pattern defines a collection of strings. If the string is included in the string set represented by pattern, then the LIKE expression returns true. As we imagined, if LIKE returns true, then the NOT LIKE expression returns false and vice versa. Underscore (_) in the pattern means matching any single character, while a percent sign (%) matches any zero or more characters, such as:
Copy the codeThe code is as follows:
'abc' LIKE 'abc' true
'abc' LIKE 'a%' true
'abc' LIKE '_b_' true
'abc' LIKE 'c' false
To match the text underscore or percent sign, rather than matching other characters, the corresponding characters in the pattern must lead to escape characters. The default escape character is a backslash, but you can specify one with the ESCAPE clause. To match the escape character itself, write two escape characters. We can also effectively turn off the escape mechanism by writing it as ESCAPE ''. At this time, we cannot turn off the special meaning of underscores and percent signs.
The keyword ILIKE can be used to replace LIKE, so that the match is case-independent for the current locale. This feature is not a SQL standard, but an extension of PostgreSQL. The operator ~~ is equivalent to LIKE, and ~~* corresponds to ILIKE. There are also !~~ and !~~* operators represent NOT LIKE and NOT ILIKE respectively. All of these operators are specific to PostgreSQL.
2. SIMILAR TO regular expression:
SIMILAR TO returns true or false based on whether the pattern matches the given string.
Copy the codeThe code is as follows:
string SIMILAR TO pattern [ESCAPE escape-character]
string NOT SIMILAR TO pattern [ESCAPE escape-character]
It is very similar to LIKE, supporting the wildcard characters ('_' and '%') of LIKE and maintaining its original meaning. In addition, SIMILAR TO also supports some unique metacharacters, such as:
1). | Identification selection (one of the two candidates).
2). * means repeating the previous item zero or more times.
3). + means repeating the previous item once or more times.
4). You can use parentheses () to combine items into a logical term.
5). A square bracket expression [...] declares a character table, just like a POSIX regular expression.
See the following example:
Copy the codeThe code is as follows:
'abc' SIMILAR TO 'abc' true
'abc' SIMILAR TO 'a' false
'abc' SIMILAR TO '%(b|d)%' true
'abc' SIMILAR TO '(b|c)%' false
Substring with three parameters, substring(string from pattern for escape-character), provides a function to extract a substring that matches the SQL regular expression pattern from a string. Like SIMILAR TO, the declared pattern must match the entire data string, otherwise the function will fail and return NULL. To identify the part of the pattern that should be returned on success, the pattern must have two escape characters followed by double quotes ("). A string matching the pattern between these two marks will be returned, such as:
MyTest=# SELECT substring('foobar' from '%#"o_b#"%' FOR '#'); --Here # is the escape character, and the pattern in the double quotes is the return part.
Copy the codeThe code is as follows:
substring
-----------
oob
(1 row)
MyTest=# SELECT substring('foobar' from '#"o_b#"%' FOR '#'); --foobar cannot exactly match the following pattern, so it returns NULL.
substring
-----------
(1 row)
7. Data type formatting function:
The PostgreSQL formatting function provides an effective set of tools for converting various data types (date/time, integer, floating point and numeric) into formatted strings and in turn converting from formatted strings to specified data types. These functions are listed below, and they all follow a common calling habit: the first parameter is the value to be formatted, and the second is a template that defines the output or output format.
| function | Return type | describe | example |
| to_char(timestamp, text) | text | Convert timestamps to strings | to_char(current_timestamp, 'HH12:MI:SS') |
| to_char(interval, text) | text | Convert time intervals into strings | to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
| to_char(int, text) | text | Convert integers to strings | to_char(125, '999') |
| to_char(double precision, text) | text | Convert real/double precision numbers into strings | to_char(125.8::real, '999D9') |
| to_char(numeric, text) | text | Convert numeric to string | to_char(-125.8, '999D99S') |
| to_date(text, text) | date | Convert string to date | to_date('05 Dec 2000', 'DD Mon YYYY') |
| to_timestamp(text, text) | timestamp | Convert strings to timestamps | to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
| to_timestamp(double) | timestamp | Convert UNIX epoch to timestamp | to_timestamp(200120400) |
| to_number(text, text) | numeric | Convert string to numeric | to_number('12,454.8-', '99G999D9S') |
1. Mode for date/time formatting:
| model | describe |
| HH | Hours of a day (01-12) |
| HH12 | Hours of a day (01-12) |
| HH24 | Hours of a day (00-23) |
| MI | Minutes (00-59) |
| SS | Seconds (00-59) |
| MS | Milliseconds (000-999) |
| US | Microseconds (000000-999999) |
| AM | Noon sign (caps) |
| Y,YYY | Year with commas (4 and more) |
| YYYY | Year (4 and more) |
| YYY | The last three of the year |
| YY | The last two of the year |
| Y | The last one of the year |
| MONTH | Full-length capital month name (blank fills with 9 characters) |
| Month | Full length mixed case month name (blank fills 9 characters) |
| month | Full-length lowercase month name (blank fills to 9 characters) |
| MON | Capital abbreviation month name (3 characters) |
| Mon | Abbreviation mixed case month name (3 characters) |
| mon | Lowercase abbreviation month name (3 characters) |
| MM | Month No. (01-12) |
| DAY | Full-length capital date name (blank fills with 9 characters) |
| Day | Full length mixed case date name (blank fills 9 characters) |
| day | Full-length lowercase date name (blank fills with 9 characters) |
| DY | Abbreviation capital date name (3 characters) |
| Dy | Abbreviation mixed case date name (3 characters) |
| dy | Abbreviation lowercase date name (3 characters) |
| DDD | Days of the year (001-366) |
| DD | Days of a month (01-31) |
| D | Days of the week (1-7; Sunday is 1) |
| W | Weeks in a month (1-5) (the first week starts from the first day of the month) |
| WW | Weeks in a year (1-53) (the first week starts from the first day of the year) |
2. Template mode for numerical formatting:
| model | describe |
| 9 | Value with specified number of digits |
| 0 | Value with leading zeros |
| .(period) | Decimal point |
| ,(comma) | Grouping (thousand) separator |
| PR | Negative values in angle brackets |
| S | Signed values |
| L | Currency symbol |
| D | Decimal point |
| G | Grouping delimiter |
| MI | Negative sign at the specified position (if the number < 0) |
| PL | The positive sign at the specified position (if the number > 0) |
| SG | Positive/negative signs at specified positions |
8. Time/date functions and operators:
1. Below is a list of supported time/date operators in PostgreSQL:
| Operator | example | result |
| + | date '2001-09-28' + integer '7' | date '2001-10-05' |
| + | date '2001-09-28' + interval '1 hour' | timestamp '2001-09-28 01:00' |
| + | date '2001-09-28' + time '03:00' | timestamp '2001-09-28 03:00' |
| + | interval '1 day' + interval '1 hour' | interval '1 day 01:00' |
| + | timestamp '2001-09-28 01:00' + interval '23 hours' | timestamp '2001-09-29 00:00' |
| + | time '01:00' + interval '3 hours' | time '04:00' |
| - | - interval '23 hours' | interval '-23:00' |
| - | date '2001-10-01' - date '2001-09-28' | integer '3' |
| - | date '2001-10-01' - integer '7' | date '2001-09-24' |
| - | date '2001-09-28' - interval '1 hour' | timestamp '2001-09-27 23:00' |
| - | time '05:00' - time '03:00' | interval '02:00' |
| - | time '05:00' - interval '2 hours' | time '03:00' |
| - | timestamp '2001-09-28 23:00' - interval '23 hours' | timestamp '2001-09-28 00:00' |
| - | interval '1 day' - interval '1 hour' | interval '23:00' |
| - | timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' | interval '1 day 15:00' |
| * | interval '1 hour' * double precision '3.5' | interval '03:30' |
| / | interval '1 hour' / double precision '1.5' | interval '00:40' |
2. Date/time function:
| function | Return type | describe | example | result |
| age(timestamp, timestamp) | interval | Subtract the parameters to generate a "symbolized" result of the year and month | age('2001-04-10', timestamp '1957-06-13') | 43 years 9 mons 27 days |
| age(timestamp) | interval | Subtract the value from current_date | age(timestamp '1957-06-13') | 43 years 8 mons 3 days |
| current_date | date | Today's date | ||
| current_time | time | Time now | ||
| current_timestamp | timestamp | Date and time | ||
| date_part(text, timestamp) | double | Get subdomain (equivalent to extract) | date_part('hour', timestamp '2001-02-16 20:38:40') | 20 |
| date_part(text, interval) | double | Get subdomain (equivalent to extract) | date_part('month', interval '2 years 3 months') | 3 |
| date_trunc(text, timestamp) | timestamp | Truncate to specified accuracy | date_trunc('hour', timestamp '2001-02-16 20:38:40') | 2001-02-16 20:00:00+00 |
| extract(field from timestamp) | double | Get subdomain | extract(hour from timestamp '2001-02-16 20:38:40') | 20 |
| extract(field from interval) | double | Get subdomain | extract(month from interval '2 years 3 months') | 3 |
| localtime | time | Today's time | ||
| localtimestamp | timestamp | Date and time | ||
| now() | timestamp | Current date and time (equivalent to current_timestamp) | ||
| timeofday() | text | Current date and time |
3. Fields supported by EXTRACT, date_part function:
| domain | describe | example | result |
| CENTURY | century | EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13'); | 20 |
| DAY | Date fields in (monthly minutes) (1-31) | EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40'); | 16 |
| DECADE | Year field divided by 10 | EXTRACT(DECADE from TIMESTAMP '2001-02-16 20:38:40'); | 200 |
| DOW | Weekly weekly numbers (0-6; Sunday is 0) (for timestamp only) | EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); | 5 |
| DOY | What day of the year (1 -365/366) (for timestamp only) | EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40'); | 47 |
| HOUR | Hour Domain (0-23) | EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40'); | 20 |
| MICROSECONDS | The second field, including the fractional part, multiplied by 1,000,000. | EXTRACT(MICROSECONDS from TIME '17:12:28.5'); | 28500000 |
| MILLENNIUM | millennium | EXTRACT(MILLENNIUM from TIMESTAMP '2001-02-16 20:38:40'); | 3 |
| MILLISECONDS | The second field, including the fractional part, multiplied by 1000. | EXTRACT(MILLISECONDS from TIME '17:12:28.5'); | 28500 |
| MINUTE | Minute field (0-59) | EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40'); | 38 |
| MONTH | For timestamp value, it is the number of months in a year (1-12); for interval value, it is the number of months, and then modulo 12 (0-11) | EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40'); | 2 |
| QUARTER | The quarter of the year (1-4) where the day is (for timestamp only) | EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40'); | 1 |
| SECOND | Second field, including the fractional part (0-59[1]) | EXTRACT(SECOND from TIMESTAMP '2001-02-16 20:38:40'); | 40 |
| WEEK | What week is the day in the year you are in? | EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40'); | 7 |
| YEAR | Year domain | EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40'); | 2001 |
4. Current date/time:
We can use the following function to get the current date and/or time:
Copy the codeThe code is as follows:
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME (precision)
CURRENT_TIMESTAMP (precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME (precision)
LOCALTIMESTAMP (precision)
All information provided in this blog is from the official PostgreSQL documentation. The main purpose of writing this blog is to facilitate future review. This statement is hereby declared.