PostgreSQL tutorial (6): Functions and operators (2), postgresql Operators

PostgreSQL tutorial (6): Functions and operators (2), postgresql Operators

6. Pattern Matching:

PostgreSQL provides three methods for implementing pattern matching: SQL LIKE operator, closer SIMILAR TO operator, and POSIX-style regular expression.
1. LIKE:
 Copy 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, the LIKE expression returns true. As we imagine, if LIKE returns true, the not like expression returns false, and vice versa. The underscore (_) in pattern indicates matching any single character, while a percent sign (%) matches any zero or more characters, such:
 Copy codeThe Code is as follows:
'Abc' LIKE 'abc' true
'Abc' LIKE 'a % 'true
'Abc' LIKE '_ B _' true
'Abc' LIKE 'C' false
 
To match the underline or percent sign of a text, rather than other characters, the corresponding character in pattern must begin with an escape character. The default ESCAPE character is a backslash, but you can use the ESCAPE clause to specify a backslash. To match the Escape Character itself, write two escape characters. We can also effectively disable the ESCAPE mechanism by writing ESCAPE ''. In this case, we cannot disable the special meanings of underlines and percent signs.
The keyword ILIKE can be used to replace LIKE, so that the match is case-insensitive for the current region settings. This feature is not an SQL standard, but an extension of PostgreSQL. Operator ~~ It is equivalent to LIKE, while ~~ * Corresponding to ILIKE. Also !~~ And !~~ * The operators are not like and not ilike. All these operators are unique to PostgreSQL.

2. similar to regular expression:
Similar to returns true or false based on whether the pattern matches the given string.
 Copy 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. It supports LIKE wildcards ('_' and '%') and retains its original intent. In addition, similar to also supports some unique metacharacters, such:
1). | identify selection (one of the two candidates ).
2). * Indicates repeating the previous item zero or more times.
3). + indicates repeat the previous item once or more times.
4). You can use a circular arc () to combine items into a logical item.
5). A square arc expression [...] declares a two‑dimensional table, just like a POSIX regular expression.
See the following example:
 Copy 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 from a string that matches the regular expression pattern of SQL. Like similar to, the declared mode must match the entire data string; otherwise, the function fails and returns NULL. To identify the mode part that should be returned when the request is successful, the mode must contain two escape characters followed by double quotation marks. Strings matching the pattern between the two tags will be returned, for example:
MyTest = # SELECT substring ('foobar' from '% # "o_ B #" %' FOR '#'); -- here # is an escape character. The mode in double quotation marks is the return part.
 Copy codeThe Code is as follows:
Substring
-----------
Oob
(1 row)
MyTest = # SELECT substring ('foobar' from '# "o_ B #" %' FOR '#'); -- foobar cannot completely match the following pattern, so NULL is returned.
Substring
-----------

(1 row)
 

VII. Data Type formatting functions:

The PostgreSQL Formatting Function provides a set of effective tools for various data types (date/time, integer, floating point, and numeric) convert to a formatted string and, in turn, convert from a formatted string to a specified data type. These functions are listed below, all following a common call 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	Description	Example
To_char (timestamp, text)	Text	Converts a timestamp to a string.	To_char (current_timestamp, 'hh12: MI: ss ')
To_char (interval, text)	Text	Convert the time interval into a string	To_char (interval '15h 2 m 12s', 'hh24: MI: ss ')
To_char (int, text)	Text	Converts an integer to a string.	To_char (125, '123 ')
To_char (double precision, text)	Text	Convert real numbers/double-precision numbers into strings	To_char (125.8: real, '999d9 ')
To_char (numeric, text)	Text	Converts numeric to a string.	To_char (-125.8, '999d99s ')
To_date (text, text)	Date	Converts a string to a date.	To_date ('05 Dec 2000 ', 'dd Mon yyyy ')
To_timestamp (text, text)	Timestamp	Converts a string to a timestamp.	To_timestamp ('05 Dec 2000 ', 'dd Mon yyyy ')
To_timestamp (double)	Timestamp	Converts a UNIX epoch to a timestamp.	To_timestamp (200120400)
To_number (text, text)	Numeric	Convert string to numeric	To_number ('1970-', '99g999d9s ')

1. format used for date/time formatting:

Mode	Description
HH	Hours per day (01-12)
HH12	Hours per day (01-12)
HH24	Hours per day (00-23)
MI	Minute (00-59)
SS	Seconds (00-59)
MS	Millisecond (000-999)
US	Microseconds (000000-999999)
AM	Midday logo (uppercase)
Y, YYY	Year with commas)
YYYY	Year (4 and more)
YYY	The last three digits of the year
YY	The last two
Y	The last digit of the year
MONTH	Full-length uppercase month name (blank filled with 9 characters)
Month	Full-length mixed-case month name (blank filled with 9 characters)
Month	Full-length lowercase month name (blank filled with 9 characters)
MON	Abbreviated month name (3 characters)
Mon	Abbreviation: Mixed-case month name (3 characters)
Mon	Lowercase abbreviation month name (3 characters)
MM	Month (01-12)
DAY	Full-length uppercase Date name (blank filled with 9 characters)
Day	Full-length mixed-case Date name (blank filled with 9 characters)
Day	Full-length lowercase Date name (blank filled with 9 characters)
DY	Abbreviation: Capital Date name (3 characters)
Dy	Abbreviation: Mixed-case Date name (3 characters)
Dy	Lowercase Date name (3 characters)
DDD	Days in a year (001-366)
DD	Days in a month (01-31)
D	Days in a week (1-7; Sunday is 1)
W	The number of weeks in a month (1-5) (the first week begins on the first day of the month)
WW	The number of weeks in a year (1-53) (the first week begins on the first day of the year)

2. template mode for value formatting:

Mode	Description
9	Value with the specified number of digits
0	Value with leading zero
. (Period)	Decimal point
, (Comma)	Group (thousands) Separator
PR	Negative value in angle brackets
S	Signed Value
L	Currency symbols
D	Decimal point
G	Group Separator
MI	Negative number at the specified position (if the number is <0)
PL	The positive number at the specified position (if the number is greater than 0)
SG	Positive/negative signs at the specified position

8. Time/date functions and operators:

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

 

Operator	Example	Result
+	Date '2017-09-28 '+ integer '7'	Date '2017-10-05'
+	Date '2014-09-28 '+ interval '1 hour'	Timestamp '2017-09-28'
+	Date '2014-09-28 '+ time '03: 00'	Timestamp '2017-09-28'
+	Interval '1 Day' + interval '1 hour'	Interval '1 day 0:00'
+	Timestamp '2017-09-28 '+ interval '23 Hours'	Timestamp '2017-09-29'
+	Time '01: 00' + interval '3 Hours'	Time '04: 00'
-	-Interval '23 urs'	Interval '-23:00'
-	Date '2017-10-01 '-date' 2001-09-28'	Integer '3'
-	Date '2017-10-01 '-integer '7'	Date '2014-09-24'
-	Date '2014-09-28 '-interval '1 hour'	Timestamp '2017-09-27'
-	Time '05: 00'-time '03: 00'	Interval '02: 00'
-	Time '05: 00'-interval '2 Hours'	Time '03: 00'
-	Timestamp '2017-09-28 '-interval '23 Hours'	Timestamp '2017-09-28'
-	Interval '1 day'-interval '1 hour'	Interval '23: 00'
-	Timestamp '2017-09-29 '-timestamp '2017-09-27'	Interval '1 day'
*	Interval '1 hour '* double precision '3. 5'	Interval '03: 30'
/	Interval '1 hour'/double precision '1. 5'	Interval '00: 40'

2. date/time functions:

Function	Return type	Description	Example	Result
Age (timestamp, timestamp)	Interval	Subtract the parameter to generate a "symbolic" result of year and month.	Age ('2017-04-10 ', timestamp '2017-06-13 ')	43 years 9 mons 27 days
Age (timestamp)	Interval	The value calculated from current_date.	Age (timestamp '2017-06-13 ')	43 years 8 mons 3 days
Current_date	Date	Today's date
Current_time	Time	Current Time
Current_timestamp	Timestamp	Date and Time
Date_part (text, timestamp)	Double	Obtain a subdomain (equivalent to extract)	Date_part ('hour ', timestamp' 2001-02-16 20:38:40 ')	20
Date_part (text, interval)	Double	Obtain a subdomain (equivalent to extract)	Date_part ('month', interval '2 years 3 months ')	3
Date_trunc (text, timestamp)	Timestamp	Truncation to the specified precision	Date_trunc ('hour ', timestamp' 2001-02-16 20:38:40 ')	2001-02-16 20:00:00 + 00
Extract (field from timestamp)	Double	Retrieve subdomains	Extract (hour from timestamp '2017-02-16 20:38:40 ')	20
Extract (field from interval)	Double	Retrieve subdomains	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 the EXTRACT and date_part functions:

Domain	Description	Example	Result
CENTURY	Century	EXTRACT (century from timestamp '2017-12-16 12:21:13 ');	20
DAY	Date Field (1-31) in (month)	EXTRACT (DAY from TIMESTAMP '2017-02-16 20:38:40 ');	16
DECADE	Year domain divided by 10	EXTRACT (DECADE from TIMESTAMP '2017-02-16 20:38:40 ');	200
DOW	The day of the week (0-6; Sunday is 0) (only for timestamp)	EXTRACT (dow from timestamp '2017-02-16 20:38:40 ');	5
DOY	The day of the year (1-365/366) (only used for timestamp)	EXTRACT (DOY from TIMESTAMP '2017-02-16 20:38:40 ');	47
HOUR	Small time domain (0-23)	EXTRACT (HOUR from TIMESTAMP '2017-02-16 20:38:40 ');	20
MICROSECONDS	Second field, including the decimal part, multiplied by 1,000,000.	EXTRACT (MICROSECONDS from TIME '17: 12: 28.5 ');	28500000
MILLENNIUM	Millennium	EXTRACT (MILLENNIUM from TIMESTAMP '2017-02-16 20:38:40 ');	3
MILLISECONDS	Second field, including the decimal part, multiplied by 1000.	EXTRACT (MILLISECONDS from TIME '17: 12: 28.5 ');	28500
MINUTE	Minute domain (0-59)	EXTRACT (MINUTE from TIMESTAMP '2017-02-16 20:38:40 ');	38
MONTH	For the timestamp value, it is the number of months in a year (1-12); for the interval value, it is the number of months, and then modulo 12 (0-11)	EXTRACT (MONTH from TIMESTAMP '2017-02-16 20:38:40 ');	2
QUARTER	Quarter of the year (1-4) of the day (only for timestamp)	EXTRACT (QUARTER from TIMESTAMP '2017-02-16 20:38:40 ');	1
SECOND	Second field, including decimal part (0-59 [1])	EXTRACT (SECOND from TIMESTAMP '2017-02-16 20:38:40 ');	40
WEEK	The week in which the day belongs.	EXTRACT (WEEK from TIMESTAMP '2017-02-16 20:38:40 ');	7
YEAR	Year domain	EXTRACT (YEAR from TIMESTAMP '2017-02-16 20:38:40 ');	2001

4. Current date/time:

We can use the following function to obtain the current date and/or time:
 Copy 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 sourced from the official PostgreSQL documentation. The main purpose of this blog is to facilitate future access.