PostgreSQL Tutorial (vi): A detailed explanation of functions and operators (2) _postgresql

Vi. Pattern Matching:

PostgreSQL provides three ways to implement pattern matching: SQL Like operator, more recent similar to operator, and posix-style regular expression.
1. Like:

Copy Code code 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 contained in a collection of strings represented by pattern, the like expression returns True. As we have imagined, if like returns True, the not-like expression returns false and vice versa. An underscore (_) in pattern matches any single character, and a percent semicolon (%) matches any zero or more characters, such as:

Copy Code code as follows:

' abc ' like ' abc ' true
' abc ' like ' a% ' true
' abc ' like ' _b_ ' true
' abc ' like ' C ' false

To match the underline or percent of the text, instead of matching other characters, the corresponding characters in pattern must be preceded by an escape character. The default escape character is the 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 "escape", at which point we cannot turn off the special meaning of underscores and percent semicolons.
The keyword ilike can be used to replace like so that the match is case insensitive to the current locale setting. This feature is not an SQL standard, it is an extension of the PostgreSQL. Operator ~ ~ is equivalent to like, while ~~* corresponds to ilike. There are also!~~ and!~~* operators representing not and not ILIKE respectively. All of these operators are PostgreSQL specific.

2. SIMILAR to Regular expression:
SIMILAR to return TRUE or false based on whether the pattern matches the given string.

Copy Code code 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 character (' _ ' and '% ') and keeping its original meaning. In addition, SIMILAR to also support some of their own unique meta characters, such as:
1). | Identification selection (one of two candidates).
2). * Means to repeat the preceding item 0 or more times.
3). + indicates that the previous item is repeated one or more times.
4. You can use parentheses () to combine items into one logical item.
5). A square bracket expression [...] Declare a character chart, just like POSIX regular expressions.
See the following example:

Copy Code code as follows:

' abc ' SIMILAR to ' abc ' true
' abc ' SIMILAR to ' a ' false
' abc ' SIMILAR to '% (b|d)% ' true
' abc ' SIMILAR to ' (b|c)% ' false

A substring,substring with three arguments (string from patterns for Escape-character) provides a function to extract a substring from a string that matches the SQL regular expression pattern. As with similar to, the declared pattern must match the entire data string, otherwise the function fails and returns NULL. In order to identify the pattern portion that should be returned at a successful time, the pattern must appear with two escape characters followed by double quotes ("). A string that matches the pattern between the two tags is returned, such as:
mytest=# SELECT substring (' foobar ' from '% # ' o_b# '% ' for ' # '); -Here # is the escape character, and the pattern inside the double quotation marks is the return part.

Copy Code code as follows:

Substring
-----------
Oob
(1 row)
mytest=# SELECT substring (' foobar ' from ' # "o_b#"% ' for ' # '); --foobar cannot exactly match the following pattern, so null is returned.
Substring
-----------

(1 row)

Vii. data type Format function:

The PostgreSQL Format 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, from formatted strings to the specified data type. These functions are listed below, all of which follow a common calling habit: the first parameter is the value to be formatted, and the second is the template that defines the output or output format.

Function	return type	Describe	Example
To_char (timestamp, text)	Text	To convert a timestamp into a string	To_char (Current_timestamp, ' HH12:MI:SS ')
To_char (interval, text)	Text	Convert time intervals to strings	To_char (interval ' 15h 2m 12s ', ' HH24:MI:SS ')
To_char (int, text)	Text	Converts integers to strings	To_char (125, ' 999 ')
To_char (double precision, text)	Text	Converts a real/double number to a string	To_char (125.8::real, ' 999d9 ')
To_char (numeric, text)	Text	Convert numeric to Strings	To_char ( -125.8, ' 999d99s ')
To_date (text, text)	Date	Converts a string to a date	To_date (' Dec ', ' DD Mon YYYY ')
To_timestamp (text, text)	Timestamp	Converts a string to a timestamp	To_timestamp (' Dec ', ' DD Mon YYYY ')
To_timestamp (Double)	Timestamp	Converts the Unix era into a timestamp	To_timestamp (200120400)
To_number (text, text)	Numeric	To convert a string into a numeric	To_number (' 12,454.8-', ' 99g999d9s ')

1. Mode for date/time formatting:

Mode	Describe
HH	Number of hours in a day (01-12)
HH12	Number of hours in a day (01-12)
HH24	Number of hours in a day (00-23)
MI	Minutes (00-59)
Ss	Seconds (00-59)
Ms	Milliseconds (000-999)
US	microseconds (000000-999999)
Am	Noon Identification (upper case)
Y,yyy	Years with commas (4 and more bits)
YYYY	Years (4 and more bits)
YYY	The latter three digits of the year
Yy	The latter two of the year
Y	The last one of the year
MONTH	Full length uppercase month name (blank padding is 9 characters)
Month	Full-length mixed-Case month name (blank padding is 9 characters)
Month	Full length lowercase month name (blank padding is 9 characters)
MON	Uppercase abbreviation month name (3 characters)
Mon	Abbreviation mixed Case month name (3 characters)
Mon	Lowercase abbreviated month name (3 characters)
Mm	Month Number (01-12)
Day	Full length uppercase date name (blank padding is 9 characters)
Day	Full-length mixed case date name (blank padding is 9 characters)
Day	Full-Length lowercase date name (blank padding is 9 characters)
Dy	Abbreviation Capital Date name (3 characters)
Dy	Abbreviation mixed case date name (3 characters)
Dy	Abbreviated Lowercase date name (3 characters)
Ddd	Days of the Year (001-366)
Dd	One months of the day (01-31)
D	Days of the week (1-7; Sunday is 1)
W	Weeks in one months (1-5) (first week starting from the first day of the month)
Ww	Number of weeks in a year (1-53) (first week from the first day of the year)

2. Template mode for numeric formatting:

Mode	Describe
9	Value with the specified number of digits
0	Value with a leading zero
. Period	Decimal point
, (comma)	Grouping (Thousand) separator
PR	Negative in angle brackets
S	Signed values
L	Currency symbol
D	Decimal point
G	Grouping delimiters
MI	Minus sign at specified position (if number < 0)
Pl	A plus sign at the indicated position (if number > 0)
SG	The positive/negative sign at the specified position

Viii. time/Date functions and operators:

1. The following is a list of time/date operators supported in PostgreSQL:

Operator	Example	Results
+	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 ' hours '	Timestamp ' 2001-09-29 00:00 '
+	Time ' 01:00 ' + interval ' 3 hours '	Time ' 04:00 '
-	-Interval ' 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 ' 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	Results
Age (timestamp, timestamp)	Interval	Minus the arguments to generate a "symbolic" result that uses the year and month	Age (' 2001-04-10 ', timestamp ' 1957-06-13 ')	Years 9 Mons
Age (timestamp)	Interval	Subtract the resulting value from the current_date	Age (timestamp ' 1957-06-13 ')	Years 8 Mons 3 days
Current_date	Date	Today's date
Current_time	Time	Now the time
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	truncated to specified precision	Date_trunc (' hour ', timestamp ' 2001-02-16 20:38:40 ')	2001-02-16 20:00:00+00
Extract (field from timestamp)	Double	Get child domains	Extract (hour from timestamp ' 2001-02-16 20:38:40 ')	20
Extract (field from interval)	Double	Get child domains	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. Extract,date_part function Supported field:

Domain	Describe	Example	Results
CENTURY	Century	EXTRACT (CENTURY from TIMESTAMP ' 2000-12-16 12:21:13 ');	20
Day	Date fields in (month) (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	Week number of week (0-6; Sunday is 0) (for timestamp only)	EXTRACT (DOW from TIMESTAMP ' 2001-02-16 20:38:40 ');	5
Doy	The first 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 decimal part, multiplied by 1,000,000.	EXTRACT (microseconds from time ' 17:12:28.5 ');	28500000
Millennium	bc	EXTRACT (Millennium from TIMESTAMP ' 2001-02-16 20:38:40 ');	3
Milliseconds	The second field, including the decimal part, multiplied by 1000.	EXTRACT (milliseconds from time ' 17:12:28.5 ');	28500
MINUTE	Minutes field (0-59)	EXTRACT (MINUTE from TIMESTAMP ' 2001-02-16 20:38:40 ');	38
MONTH	For the timestamp value, it is the number of months in a year (1-12), and for the interval value, it is the number of the month and then modulo 12 (0-11)	EXTRACT (MONTH from TIMESTAMP ' 2001-02-16 20:38:40 ');	2
Quarter	The quarter of the year in which the day is located (1-4) (for timestamp only)	EXTRACT (Quarter from TIMESTAMP ' 2001-02-16 20:38:40 ');	1
SECOND	Second field, including fractional part (0-59[1])	EXTRACT (SECOND from TIMESTAMP ' 2001-02-16 20:38:40 ');	40
WEEK	The day is the first few weeks in the year in which it was located.	EXTRACT (WEEK from TIMESTAMP ' 2001-02-16 20:38:40 ');	7
Year	Year Field	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 Code code as follows:

Current_date
Current_time
Current_timestamp
Current_time (Precision)
Current_timestamp (Precision)
LocalTime
Localtimestamp
LocalTime (Precision)
Localtimestamp (Precision)

All of the information provided in this blog originates from the PostgreSQL official document, and the main purpose of writing the blog is to facilitate future access, hereby declares.