Preface
As mentioned above, when we use postgresql database to fetch data, we will inevitably encounter some data processing scenarios. This article will summarize some commonly used built-in functions.
-- CheckpostgresqlVersion(Any of the following statements can be implemented) select version(); show server_version;
1. Format conversion
1.1 Format Converter Display Conversion
The format conversion can be performed directly using double colons, and the syntax is as follows:
Field name or value::data type
Examples are as follows:
-- Put text'123'Turn toint8type
SELECT
'123' :: int8 num;
-- Put texttype字段t1Turn toint8type
SELECT
t1 :: int8
from temp;
1.2 Display conversion using data types
Use data types for conversion, the syntax is as follows:
Data type value
example:
-- Put text'123'Turn toint8type
SELECT
int8'123' num;
1.3 Format conversion function display conversion
Use the data conversion function cast to convert, the syntax is as follows:
cast(field name or value as data type)
example:
-- Put text'123'Turn toint8type
SELECT
cast('123' as int4) num;
-- Put text字段t1Turn tointtype
SELECT CAST( t1 AS INT ) t1_c
FROM
TEMP;1.4 Conversion cases
-- Text to integer
SELECT CAST
( '123' AS int4 );
-- Text to floating point numbers
SELECT CAST
( '123.34' AS DECIMAL );
SELECT CAST
( '123.34' AS NUMERIC );
-- Number to text
SELECT CAST
( 123 AS VARCHAR );--Variable string
SELECT CAST
( - 123 AS CHAR ( 2 ) );-- Fixed string，Make a cutoff，Will-123Turn to'-1'
SELECT CAST
( - 123 AS CHAR ( 6 ) );-- Fixed string，Fill spaces，Will-123Turn to'-123 '
SELECT CAST
( 124.94 AS TEXT );--Variable string,Will124.94Turn to'124.94'
SELECT
to_char( 124.94, '999D9' );--Will124.94Turn to'124.9',Follow rounding
SELECT
to_char( 124.94, 'FM999.99' );--Will124.94Turn to'124.94'
SELECT
to_char( - 124.94, 'FM9999999.99' );--Will-124.94Turn to'-124.94'
SELECT
to_char( - 124.94, 'FM9999999.990' );--Will-124.94Turn to'-124.940'
SELECT
to_char( 124, '00000' );--Use zero to make up for the left end5Bit，Will124Turn to'00124'
SELECT
to_char( 124, '99999' );--Use spaces to make up for the left end5Bit，Will124Turn to' 124'
SELECT
to_char( - 124.945, 'FM999' );--Show only integer parts，Follow rounding
-- Timestamp(timestamp)Transfer date(date)
SELECT CAST
( now( ) AS DATE );--Normal date mode
-- Timestamp(timestamp)Transfer text
SELECT CAST
( now( ) AS TEXT );--Not specifying the output format
SELECT
to_char( now( ), 'yyyy-mm-dd' );--Specify the output format;
-- 文本Transfer date(date)
SELECT
to_date( '2012-01-01', 'yyyy-mm-dd' );
-- 文本转Timestamp(TIMESTAMP)
SELECT
to_timestamp( '2012-01-01 12:02:01', 'yyyy-mm-dd HH24:MI:SS' );
2. Mathematical calculations
2.1 Mathematical operation operators
| Operator | describe | example | result |
|---|---|---|---|
| + | add | 2 + 3 | 5 |
| - | reduce | 2 - 3 | -1 |
| * | take | 2 * 3 | 6 |
| / | Dividing (integral division truncation result) | 4 / 2 | 2 |
| % | Mod (take the remainder) | 5 % 4 | 1 |
| ^ | Index (combined from left to right) | 2.0 ^ 3.0 | 8 |
| |/ | Square root | |/ 25.0 | 5 |
| ||/ | Cube root | ||/ 27.0 | 3 |
| ! | factorial | 5 ! | 120 |
| !! | Factorial (prefix operator) | !! 5 | 120 |
| @ | Absolute value | @ -5.0 | 5 |
| & | bitwise and | 91 & 15 | 11 |
| | | bitwise or | 32 | 3 |
| # | bitwise xor | 17 # 5 | 20 |
| ~ | Reverse bit by bit | ~1 | -2 |
| << | Bit-by-bit left | 1 << 4 | 16 |
| >> | Move right by bit | 8 >> 2 | 2 |
2.2 Mathematical operation functions
| function | Return type | describe | example | result |
|---|---|---|---|---|
| abs(x) | Same as input | Absolute value | abs(-12.43) | 12.43 |
| cbrt(dp) | double | Cube root | cbrt(27.0) | 3 |
| ceil(dp or numeric) | Same as input | The closest integer not smaller than the parameter | ceil(-42.8) | -42 |
| ceiling(dp or numeric) | Same as input | The closest integer not smaller than the parameter (alias for ceil) | ceiling(-95.3) | -95 |
| degrees(dp) | dp | Turn radians into angles | degrees(0.5) | 28.6478897565412 |
| div(y numeric, x numeric) | numeric | Integer quotient of y/x | div(9,4) | 2 |
| exp(dp or numeric) | Same as input | index | exp(1.0) | 2.71828182845905 |
| floor(dp or numeric) | Same as input | The nearest integer not greater than the parameter | floor(-42.8) | -43 |
| ln(dp or numeric) | Same as input | Natural logarithm | ln(2.0) | 0.693147180559945 |
| log(dp or numeric) | Same as input | Logarithm with base 10 | log(100.0) | 2 |
| log10(dp or numeric) | Same as input | Logarithm with base 10 | log10(100.0) | 2 |
| log(b numeric, x numeric) | numeric | Logarithm with base b | log(2.0, 64.0) | 6.0000000000 |
| mod(y, x) | Same as parameter type | The remainder of y/x | mod(9,4) | 1 |
| pi() | dp | "π" constant | pi() | 3.14159265358979 |
| power(a dp, b dp) | dp | Find the power of b of a | power(9.0, 3.0) | 729 |
| power(a numeric, b numeric) | numeric | Find the power of b of a | power(9.0, 3.0) | 729 |
| radians(dp) | dp | Turn angle into arc | radians(45.0) | 0.785398163397448 |
| round(dp or numeric) | Same as input | Circle is the closest integer | round(42.4) | 42 |
| round(v numeric, s int) | numeric | The circle is a decimal number in s | round(42.4382, 2) | 42.44 |
| scale(numeric) | integer | Parameter accuracy (number of digits after decimal point) | scale(8.41) | 2 |
| sign(dp or numeric) | Same as input | Symbols for parameters (-1, 0, +1) | sign(-8.4) | -1 |
| sqrt(dp or numeric) | Same as input | Square root | sqrt(2.0) | 1.4142135623731 |
| trunc(dp or numeric) | Same as input | Truncate (close to zero) | trunc(42.8) | 42 |
| trunc(v numeric, s int) | numeric | Truncate the number to the decimal position of the s position | trunc(42.4382, 2) | 42.43 |
3. Logical computing
3.1 Logical Operators
There are three logical operators in postgresql:
- AND
- OR
- NOT
3.2 Comparison operators
| Operator | describe |
|---|---|
| < | Less than |
| > | Greater than |
| <= | Less than or equal to |
| >= | Greater than or equal to |
| = | equal |
| <> or != | Not equal to |
!= operator is converted to <> during the analyzer stage
3.3 Comparative predicate
| predicate | describe |
|---|---|
| a BETWEEN x AND y | Between x and y |
| a NOT BETWEEN x AND y | Not between x and y |
| a BETWEEN SYMMETRIC x AND y | Between x and y after sorting the comparison values |
| a NOT BETWEEN SYMMETRIC x AND y | Not between x and y after sorting the comparison values |
| a IS DISTINCT FROM b | Not equal, the null value is treated as a normal value |
| a IS NOT DISTINCT FROM b | Equal to, the null value is treated as a normal value |
| expression IS NULL | It's empty value |
| expression IS NOT NULL | Not empty value |
| expression ISNULL | is a null value (non-standard syntax) |
| expression NOTNULL | Not a null value (non-standard syntax) |
| boolean_expression IS TRUE | For true |
| boolean_expression IS NOT TRUE | It is false or unknown |
| boolean_expression IS FALSE | For false |
| boolean_expression IS NOT FALSE | True or unknown |
| boolean_expression IS UNKNOWN | The value is unknown |
| boolean_expression IS NOT UNKNOWN | True or false |
3.4 Comparison Functions
| function | describe | example | Example results |
|---|---|---|---|
| num_nonnulls(VARIADIC “any”) | Returns the number of non-null parameters | num_nonnulls(0, NULL, 1 ,2 ,3) | 4 |
| num_nulls(VARIADIC “any”) | Returns the number of empty parameters | num_nulls(0, NULL, 1 ,2 ,3) | 1 |
4. String and related matching functions
| function | Return type | describe | example | result |
|---|---|---|---|---|
| string || string | text | Connect | ‘Hello’ || ‘Word’ | ‘HelloWord’ |
| string || non-string or non-string || string | text | Use a string input | 'Value: ’ || 42 | Value: 42 |
| bit_length(string) | int | Number of bits in string | bit_length(‘Hello’) | 40 |
| char_length(string) or character_length(string) | int | Number of characters in string | char_length(‘Hello’) | 4 |
| lower(string) | text | Convert string to lowercase | lower(‘Hello’) | hello |
| overlay(string placing string from int [for int]) | text | Replace substring, after for refers to the number of replaced bits | overlay(‘Hexxx,word’ placing ‘llo’ from 3 for 4) | Helloword |
| position(substring in string) | int | Position the specified substring position, and use whether the value is greater than 0 to determine whether the substring is included. | position(‘lo’ in ‘hello’) | 4 |
| substring(string [from int] [for int]) | text | Extract substrings | substring(‘hello’ from 1 for 3) | hel |
| substring(string from pattern) | text | Extract substrings matching POSIX regular expressions | substring(‘hello’ from ‘^…’) | hel |
| substr(string, from [, count]) | text | Extract substrings | substr(‘Hello’, 1, 3) | hel |
| trim([leading | trailing | both] [characters] from string) | text | Remove the longest string containing only characters in characters (default is a space) from the beginning, end, or end of a string (both is the default value) | trim(both ‘Hes’ from ‘sHehelloeHs’) | hello |
| trim([leading | trailing | both] [from] string [, characters] ) | text | Non-standard version of trim() | trim(both from ‘hhHellohh’, ‘h’) or trim(‘hhHellohh’, ‘h’) | Tom |
| upper(string) | text | Convert string to uppercase | upper(‘hello’) | HELLO |
| concat(str “any” [, str “any” [, …] ]) | text | A text representation concatenated with all parameters. The NULL parameter is ignored. | concat(‘abcde’, 2, NULL, 22) | abcde222 |
| concat_ws(sep text, str “any” [, str “any” [, …] ]) | text | Concatenate all other parameters except the first parameter together with a delimited string. The first parameter is used as the delimiter string. The NULL parameter is ignored. | concat_ws(‘,’, ‘abcde’, 2, NULL, 22) | abcde,2,22 |
| left(str text, n int) | text | Returns the first n characters in the string. When n is negative, all characters except the last |n| characters will be returned. | left(‘abcde’, 2) | ab |
| length(string) | int | Number of characters in string | length(‘hello’) | 5 |
| length(string bytea, encoding name ) | int | string the number of characters in a given encoding. string must be valid in this encoding. | length(‘hello’, ‘UTF8’) | 5 |
| lpad(string text, length int [, fill text]) | text | Fill string to length length by prefixed character fill (default is a space). If string is already longer than length, it is truncated (from the right). | lpad(‘hi’, 5, ‘ab’) | abahi |
| ltrim(string text [, characters text]) | text | Delete the longest string containing only characters (default is a space) from the beginning of the string | ltrim(‘zzzytest’, ‘xyz’) | test |
| regexp_match(string text, pattern text [, flags text]) | text[] | Returns a substring obtained by a POSIX regular expression matching the first string. | regexp_match(‘foobarbequetarz’, ‘(foo)(bar)’) | One line: {foo,bar} |
| regexp_matches(string text, pattern text [, flags text]) | setof text[] | Returns a substring obtained by a POSIX regular expression matching string | regexp_matches(‘foobarbequetarz’, ‘.ar’, ‘g’) | Two lines: {bar} {tar} |
| regexp_replace(string text, pattern text, replacement text [, flags text]) | text | Replace substrings that match a POSIX regular expression. | regexp_replace(‘Hello’, ‘l+.’, ‘r’) | Her |
| regexp_split_to_array(string text, pattern text [, flags text ]) | text[] | Use a POSIX regular expression as a separator to divide the string. | regexp_split_to_array(‘hello world’, ‘\s+’) | One line: {hello,world} |
| regexp_split_to_table(string text, pattern text [, flags text]) setof | text | Use a POSIX regular expression as a separator to divide the string. | regexp_split_to_table(‘hello world’, ‘\s+’) | Two lines: hello world |
| repeat(string text, number int) | text | Repeat the number specified by string | repeat(‘he’, 3) | hehehe |
| replace(string text, from text, to text) | text | Replace all substrings from appearing in string with substring to | replace(‘hello’, ‘ello’, ‘is’) | his |
| reverse(str) | text | Returns the reversed string | reverse(‘abcde’) | edcba |
| right(str text, n int) | text | Returns the last n characters in the string. If n is negative, return all characters except the first |n| characters. | right(‘abcde’, 2) | de |
| rpad(string text, length int [, fill text]) | text | Fill string to length by adding a character fill (default is a space). If string is already longer than length it will be truncated. | rpad(‘hi’, 5, ‘xy’) | hixyx |
| rtrim(string text [, characters text]) | text | Remove the longest string containing only characters (default is a space) from the end of the string | rtrim(‘testxxzx’, ‘xyz’) | test |
| split_part(string text, delimiter text, field int) | text | Divide string by delimiter and return the given domain (calculated from 1) | split_part(‘you!hello!world!’, ‘!’, 2) | hello |
| strpos(string, substring) | int | Specify the position of the substring (same as position(substring in string), but pay attention to the opposite order of parameters) | strpos(‘hello’, ‘o’) | 5 |
| starts_with(string, prefix) | bool | Returns true if string starts with prefix. | starts_with(‘alphabet’, ‘alph’) | t |
5. Time and date functions
5.1 Time class operator
| 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:00’ |
| + | date ‘2001-09-28’ + time ‘03:00’ | timestamp ‘2001-09-28 03:00:00’ |
| + | interval ‘1 day’ + interval ‘1 hour’ | interval ‘1 day 01:00:00’ |
| + | timestamp ‘2001-09-28 01:00’ + interval ‘23 hours’ | timestamp ‘2001-09-29 00:00:00’ |
| + | time ‘01:00’ + interval ‘3 hours’ | time ‘04:00:00’ |
| - | - interval ‘23 hours’ | interval ‘-23:00:00’ |
| - | date ‘2001-10-01’ - date ‘2001-09-28’ | integer ‘3’ (days) |
| - | date ‘2001-10-01’ - integer ‘7’ | date ‘2001-09-24’ |
| - | date ‘2001-09-28’ - interval ‘1 hour’ | timestamp ‘2001-09-27 23:00:00’ |
| - | time ‘05:00’ - time ‘03:00’ | interval ‘02:00:00’ |
| - | time ‘05:00’ - interval ‘2 hours’ | time ‘03:00:00’ |
| - | timestamp ‘2001-09-28 23:00’ - interval ‘23 hours’ | timestamp ‘2001-09-28 00:00:00’ |
| - | interval ‘1 day’ - interval ‘1 hour’ | interval ‘1 day -01:00:00’ |
| - | timestamp ‘2001-09-29 03:00’ - timestamp ‘2001-09-27 12:00’ | interval ‘1 day 15:00:00’ |
| * | 900 * interval ‘1 second’ | interval ‘00:15:00’ |
| * | 21 * interval ‘1 day’ | interval ‘21 days’ |
| * | double precision ‘3.5’ * interval ‘1 hour’ | interval ‘03:30:00’ |
| / | interval ‘1 hour’ / double precision ‘1.5’ | interval ‘00:40:00’ |
5.2 Time and date functions
| function | Return type | describe | example | result |
|---|---|---|---|---|
| age(timestamp, timestamp) | interval | Subtract the parameters to generate a "symbolized" result using year, month (rather than just day) | age(timestamp ‘2001-04-10’, timestamp ‘1957-06-13’) | 43 years 9 mons 27 days |
| age(timestamp) | interval | Subtract from current_date (at midnight) | age(timestamp ‘1957-06-13’), if today is 2022-06-14 | 65 years 1 day |
| clock_timestamp() | timestamp with time zone | Current date and time (change during statement execution) | clock_timestamp() | 2022-06-14 19:06:54.034672+08 |
| current_date | date | Current date | current_date | 2022-06-14 |
| current_time | time with time zone | Current time (time of day), with time zone | current_time | 19:11:04.336139+08 |
| current_timestamp | timestamp with time zone | Current date and time (at the beginning of the current transaction), with time zone | current_timestamp | 2022-06-14 19:11:57.83455+08 |
| date_part(text, timestamp) | double precision | Obtain subdomain (equivalent to extract) | date_part(‘hour’, timestamp ‘2022-06-14 20:38:40’) | 20 |
| date_part(text, interval) | double precision | Obtain 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 ‘2022-06-14 20:38:40’) | 2022-06-14 20:00:00 |
| date_trunc(text, timestamp with time zone, text) | timestamp with time zone | Truncate to specified precision in specified time zone | ddate_trunc(‘day’, timestamptz ‘2022-06-14 20:38:40+00’, ‘Australia/Sydney’) | 2022-06-14 22:00:00+08 |
| date_trunc(text, interval) | interval | Truncate to specified accuracy | date_trunc(‘hour’, interval ‘2 days 3 hours 40 minutes’) | 2 days 03:00:00 |
| extract(field from timestamp) | double precision | Obtain a subdomain | extract(MINUTE from timestamp ‘2022-06-14 20:38:40’) | 38 |
| extract(field from interval) | double precision | Obtain a subdomain | extract(month from interval ‘2 years 3 months’) | 3 |
| isfinite(date) | boolean | Test limited date (not +/-unlimited) | isfinite(date ‘2022-06-14’) | true (actual abbreviation is t) |
| isfinite(timestamp) | boolean | Test finite timestamps (not +/-infinite) | isfinite(timestamp ‘2022-06-14 21:28:30’) | true (actual abbreviation is t) |
| isfinite(interval) | boolean | Limited interval for testing | isfinite(interval ‘2 minutes’) | true (actual abbreviation is t) |
| justify_days(interval) | interval | Adjust the interval so that the 30-day time period can be expressed as a month | justify_days(interval ‘35 days’) | 1 mon 5 days |
| justify_hours(interval) | interval | Adjust the interval so that the 24-hour time period can be expressed as a day | justify_hours(interval ‘27 hours’) | 1 day 03:00:00 |
| justify_interval(interval) | interval | Use justify_days and justify_hours to adjust intervals, use extra symbols to adjust intervals | justify_interval(interval ‘1 mon -1 hour’) | 29 days 23:00:00 |
| localtime | time | Current time (time of day), without time zone | localtime | 19:21:14.958286 |
| localtimestamp | timestamp | Current date and time (the beginning of the current transaction), without time zone | LOCALTIMESTAMP | 2022-07-22 19:23:54.073462 |
| make_date(year int, month int, day int) | date | Date created from year, month, and day domain | make_date(2022, 7, 15) | 2022-07-15 |
| make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0) | interval | Create interval from year, month, week, day, hour, minute, and second fields | make_interval(days => 10) | 10 days |
| make_time(hour int, min int, sec double precision) | time | Create time from time, minute, and second fields | make_time(8, 15, 23.5) | 08:15:23.5 |
| make_timestamp(year int, month int, day int, hour int, min int, sec double precision) | timestamp | Create a time stamp from the year, month, day, hour, minute, and second fields | make_timestamp(2013, 7, 15, 8, 15, 23.5) | 2013-07-15 08:15:23.5 |
| make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ]) | timestamp with time zone | Create a time stamp with time zone from the year, month, day, hour, minute, and second fields. If timezone is not specified, the current time zone is used. | make_timestamptz(2022, 6, 14, 19, 30, 50.5) | 2022-06-14 19:30:50.5+08 |
| now() | timestamp with time zone | Current date and time (the beginning of the current transaction), with time zone | now() | 2022-07-22 19:28:15.804042+08 |
| statement_timestamp() | timestamp with time zone | The current date and time (the beginning of the current statement), the return value is the same as CURRENT_TIMESTAMP during the first command of a transaction | statement_timestamp() | 2022-07-22 19:31:35.75589+08 |
| timeofday() | text | Current date and time (like clock_timestamp, but as a text string) | timeofday() | Fri Jul 22 19:35:19.000959 2022 CST |
| transaction_timestamp() | timestamp with time zone | Current date and time (the beginning of the current transaction); equivalent to CURRENT_TIMESTAMP | transaction_timestamp() | 2022-07-22 19:34:02.369665+08 |
| to_timestamp(double precision) | timestamp with time zone | Convert Unix time (seconds starting from 1970-01-01 00:00:00+00) to timestamp | to_timestamp(1655211000) | 2022-06-14 20:50:00+08 |
6. Array functions
6.1 Array Operator
| Operator | describe | example | result |
|---|---|---|---|
| = | equal | ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] | t |
| <> | Not equal to | ARRAY[1,2,3] <> ARRAY[1,2,4] | t |
| < | Less than | ARRAY[1,2,3] < ARRAY[1,2,4] | t |
| > | Greater than | ARRAY[1,4,3] > ARRAY[1,2,4] | t |
| <= | Less than or equal to | ARRAY[1,2,3] <= ARRAY[1,2,3] | t |
| >= | Greater than or equal to | ARRAY[1,4,3] >= ARRAY[1,4,3] | t |
| @> | Include | ARRAY[1,4,3] @> ARRAY[3,1,3] | t |
| <@ | Contained | ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6] | t |
| && | Overlapping (with common elements) | ARRAY[1,4,3] && ARRAY[2,1] | t |
| || | Array and array concatenation | ARRAY[1,2,3] || ARRAY[4,5,6] | {1,2,3,4,5,6} |
| || | Array and array concatenation | ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] | {{1,2,3},{4,5,6},{7,8,9}} |
| || | Elements to array concatenation | 3 || ARRAY[4,5,6] | {3,4,5,6} |
| || | Array to element concatenation | ARRAY[4,5,6] || 7 | {4,5,6,7} |
6.2 Array Functions
| function | Return type | describe | example | result |
|---|---|---|---|---|
| array_append(anyarray, anyelement) | anyarray | Append an element to the end of an array | array_append(ARRAY[1,2], 3) | {1,2,3} |
| array_cat(anyarray, anyarray) | anyarray | Concatenate two arrays | array_cat(ARRAY[1,2,3], ARRAY[4,5]) | {1,2,3,4,5} |
| array_ndims(anyarray) | int | Returns the number of dimensions of the array | array_ndims(ARRAY[[1,2,3], [4,5,6]]) | 2 |
| array_dims(anyarray) | text | Returns the text representation of the dimension of the array | array_dims(ARRAY[[1,2,3], [4,5,6]]) | [1:2][1:3] |
| array_fill(anyelement, int[], [, int[]]) | anyarray | Return an array initialized with the provided values and dimensions, and you can choose to choose the lower bound to not be 1. | array_fill(6, ARRAY[3], ARRAY[5]) | [5:7]={6,6,6} |
| array_length(anyarray, int) | int | Returns the length of the requested array dimension | array_length(array[1,2,3], 1) | 3 |
| array_lower(anyarray, int) | int | Returns the lower bound of the requested array dimension | array_lower(‘[0:2]={1,2,3}’::int[], 1) | 0 |
| array_position(anyarray, anyelement [, int]) | int | Returns the subscript in the array starting from the element specified by the third parameter or starting from the first element (the array must be one-dimensional) and the first occurrence of the second parameter | array_position(ARRAY[‘sun’,‘mon’,‘tue’,‘wed’,‘thu’,‘fri’,‘sat’], ‘mon’) | 2 |
| array_positions(anyarray, anyelement) | int[] | Returns an array composed of all the subscripts of the second parameter given by the first parameter (the array must be one-dimensional). | array_positions(ARRAY[‘A’,‘A’,‘B’,‘A’], ‘A’) | {1,2,4} |
| array_prepend(anyelement, anyarray) | anyarray | Append an element to the header of an array | array_prepend(1, ARRAY[2,3]) | {1,2,3} |
| array_remove(anyarray, anyelement) | anyarray | Remove all elements equal to the given value from the array (the array must be one-dimensional) | array_remove(ARRAY[1,2,3,2], 2) | {1,3} |
| array_replace(anyarray, anyelement, anyelement) | anyarray | Replace each array element equal to the given value with a new value | array_replace(ARRAY[1,2,5,4], 5, 3) | {1,2,3,4} |
| array_to_string(anyarray, text [, text]) | text | Connect array elements using the provided delimiter and optional empty string | array_to_string(ARRAY[1, 2, 3, NULL, 5], ‘,’, ‘*’) | ‘1,2,3,*,5’ |
| array_upper(anyarray, int) | int | Returns the upper bound of the requested array dimension | array_upper(ARRAY[1,8,3,7], 1) | 4 |
| cardinality(anyarray) | int | Returns the total number of elements in the array, and if the array is empty, return 0 | cardinality(ARRAY[[1,2],[3,4]]) | 4 |
| string_to_array(text, text [, text]) | text[] | Divide the string into array elements using the provided delimiter and optional empty string | string_to_array(‘a-b-c-d-e-g-’, ‘-’, ‘’) | {a,b,c,d,e,g,NULL} |
| unnest(anyarray) | setof anyelement | Expand an array into a set of rows | unnest(ARRAY[1,2]) | 2 lines: 1 2 |
7. Range function
7.1 Range Operator
| Operator | describe | example | result |
|---|---|---|---|
| = | equal | int4range(1,5) = ‘[1,4]’::int4range | t |
| <> | Not equal to | numrange(1.1,2.2) <> numrange(1.1,2.3) | t |
| < | Less than | int4range(1,10) < int4range(2,3) | t |
| > | Greater than | int4range(1,10) > int4range(1,5) | t |
| <= | Less than or equal to | numrange(1.1,2.2) <= numrange(1.1,2.2) | t |
| >= | Greater than or equal to | numrange(1.1,2.2) >= numrange(1.1,2.0) | t |
| @> | Included range | int4range(2,4) @> int4range(2,3) | t |
| @> | Include elements | ‘[2011-01-01,2011-03-01)’::tsrange @> ‘2011-01-10’::timestamp | t |
| <@ | The scope is included | int4range(2,4) <@ int4range(1,7) | t |
| <@ | Elements are included | 42 <@ int4range(1,7) | f |
| && | Overlapping (with common points) | int8range(3,7) && int8range(4,12) | t |
| << | Strict left | int8range(1,10) << int8range(100,110) | t |
| >> | Strict right | int8range(50,60) >> int8range(20,30) | t |
| &< | No more than the right | int8range(1,20) &< int8range(18,20) t | |
| &> | No more than the left | int8range(7,20) &> int8range(5,10) | t |
| -|- | Adjacent | numrange(1.1,2.2) -|- numrange(2.2,3.3) | t |
| + | and | numrange(5,15) + numrange(10,20) | [5,20) |
| * | pay | int8range(5,15) * int8range(10,20) | [10,15) |
| - | Difference | int8range(5,15) - int8range(10,20) | [5,10) |
7.2 Range Function
| function | Return type | describe | example | result |
|---|---|---|---|---|
| lower(anyrange) | Element type of range | The lower bound of range | lower(numrange(1.1,2.2)) | 1.1 |
| upper(anyrange) | Element type of range | The upper bound of range | upper(numrange(1.1,2.2)) | 2.2 |
| isempty(anyrange) | boolean | The range is empty? | isempty(numrange(1.1,2.2)) | false |
| lower_inc(anyrange) | boolean | Is the lower realm included? | lower_inc(numrange(1.1,2.2)) | true |
| upper_inc(anyrange) | boolean | Is the upper realm included? | upper_inc(numrange(1.1,2.2)) | false |
| lower_inf(anyrange) | boolean | The lower realm is infinite? | lower_inf(‘(,)’::daterange) | true |
| upper_inf(anyrange) | boolean | Unlimited upper realm? | upper_inf(‘(,)’::daterange) | true |
| range_merge(anyrange, anyrange) | anyrange | Minimum range containing two given ranges | range_merge(‘[1,2)’::int4range, ‘[3,4)’::int4range) | [1,4) |
8. Aggregation functions
8.1 Common functions
| function | Parameter Type | Return type | Partial mode | describe |
|---|---|---|---|---|
| array_agg(expression) | Any non-array type | Array of parameter types | No | The input value (including empty) is connected to an array |
| array_agg(expression) | Any array type | Same as parameter data type | No | The input array is concatenated into an array of higher dimensions (the inputs must all have the same dimension and cannot be empty or NULL) |
| avg(expression) | Smallint, int, bigint, real, double precision, numeric or interval | For any integer type parameter is numeric, for a floating point parameter is double precision, otherwise the same as the parameter data type | Yes | Average of all non-null input values (arithmic average) |
| bit_and(expression) | Smallint, int, bigint or bit | Same as parameter data type | Yes | Bitwise orthogonal to all non-null input values, if there is no non-null value, the result is a null value |
| bit_or(expression) | smallint, int, bigint, or bit | Same as parameter data type | Yes | Bitwise or of all non-null input values, if there is no non-null value, the result is a null value |
| bool_and(expression) | bool | bool | Yes | If all input values are true, the result is true, otherwise it is false |
| bool_or(expression) | bool | bool | Yes | The result is true if at least one input value is true, otherwise it is false |
| count(*) | bigint | Yes | Number of lines entered | |
| count(expression) | any | bigint | Yes | The number of input lines with non-empty expression value |
| every(expression) | bool | bool | Yes | Equivalent to bool_and |
| json_agg(expression) | any | json | No | Aggregate values, including null values, into a JSON array |
| jsonb_agg(expression) | any | jsonb | No | Aggregate values, including null values, into a JSON array |
| json_object_agg(name, value) | (any, any) | json | No | Aggregate name/value pairs into a JSON object, the value can be empty, but not a name. |
| jsonb_object_agg(name, value) | (any, any) | jsonb | No | Aggregate name/value pairs into a JSON object. The value can be empty, but not a name. |
| max(expression) | Any array, number, string, date/time, network or enum type, or array of these types | Same as parameter data type | Yes | The maximum value of expression among all non-null input values |
| min(expression) | Any array, number, string, date/time, network or enum type, or array of these types | Same as parameter data type | Yes | The minimum value of expression among all non-null input values |
| string_agg(expression, delimiter) | (text, text) or (bytea, bytea) | Same as parameter data type | No | Non-empty input values are connected into a string, separated by delimiters |
| sum(expression) | Smallint, int, bigint, real, double precision, numeric, interval or money | For smallint or int parameters, it is bigint parameter, and for bigint parameter, it is numeric, otherwise it is the same as the parameter data type. | Yes | The sum of expressions of all non-null input values |
| xmlagg(expression) | xml | xml | No | Connect non-empty XML values |
8.2 Statistical class functions
| function | Parameter Type | Return type | Partial mode | describe |
|---|---|---|---|---|
| array_agg(expression) | Any non-array type | Array of parameter types | No | The input value (including empty) is connected to an array |
| array_agg(expression) | Any array type | Same as parameter data type | No | The input array is concatenated into an array of higher dimensions (the inputs must all have the same dimension and cannot be empty or NULL) |
| avg(expression) | Smallint, int, bigint, real, double precision, numeric or interval | For any integer type parameter is numeric, for a floating point parameter is double precision, otherwise the same as the parameter data type | Yes | Average of all non-null input values (arithmic average) |
| bit_and(expression) | Smallint, int, bigint or bit | Same as parameter data type | Yes | Bitwise orthogonal to all non-null input values, if there is no non-null value, the result is a null value |
| bit_or(expression) | smallint, int, bigint, or bit | Same as parameter data type | Yes | Bitwise or of all non-null input values, if there is no non-null value, the result is a null value |
| bool_and(expression) | bool | bool | Yes | If all input values are true, the result is true, otherwise it is false |
| bool_or(expression) | bool | bool | Yes | The result is true if at least one input value is true, otherwise it is false |
| count(*) | bigint | Yes | Number of lines entered | |
| count(expression) | any | bigint | Yes | The number of input lines with non-empty expression value |
| every(expression) | bool | bool | Yes | Equivalent to bool_and |
| json_agg(expression) | any | json | No | Aggregate values, including null values, into a JSON array |
| jsonb_agg(expression) | any | jsonb | No | Aggregate values, including null values, into a JSON array |
| json_object_agg(name, value) | (any, any) | json | No | Aggregate name/value pairs into a JSON object, the value can be empty, but not a name. |
| jsonb_object_agg(name, value) | (any, any) | jsonb | No | Aggregate name/value pairs into a JSON object. The value can be empty, but not a name. |
| max(expression) | Any array, number, string, date/time, network or enum type, or array of these types | Same as parameter data type | Yes | The maximum value of expression among all non-null input values |
| min(expression) | Any array, number, string, date/time, network or enum type, or array of these types | Same as parameter data type | Yes | The minimum value of expression among all non-null input values |
| string_agg(expression, delimiter) | (text, text) or (bytea, bytea) | Same as parameter data type | No | Non-empty input values are connected into a string, separated by delimiters |
| sum(expression) | Smallint, int, bigint, real, double precision, numeric, interval or money | For smallint or int parameters, it is bigint parameter, and for bigint parameter, it is numeric, otherwise it is the same as the parameter data type. | Yes | The sum of expressions of all non-null input values |
| xmlagg(expression) | xml | xml | No | Connect non-empty XML values |
8.3 Orderly aggregation function
| function | Parameter Type | Return type | Partial mode | describe |
|---|---|---|---|---|
| corr(Y, X) | double precision | double precision | Yes | Correlation coefficient |
| covar_pop(Y, X) | double precision | double precision | Yes | Overall covariance |
| covar_samp(Y, X) | double precision | double precision | Yes | Sample covariance |
| regr_avgx(Y, X) | double precision | double precision | Yes | Average value of independent variables (sum(X)/N) |
| regr_avgy(Y, X) | double precision | double precision | Yes | The mean value of the dependent variable (sum(Y)/N) |
| regr_count(Y, X) | double precision | bigint | Yes | The number of input lines where neither expression is empty |
| regr_intercept(Y, X) | double precision | double precision | Yes | The y-intercept of the linear equation fitted by the least squares determined by the (X, Y) pair |
| regr_r2(Y, X) | double precision | double precision | Yes | Square of correlation coefficient |
| regr_slope(Y, X) | double precision | double precision | Yes | The slope of the linear equation for least squares fitted by (X, Y) pairs |
| regr_sxx(Y, X) | double precision | double precision | Yes | sum(X^2) - sum(X)^2/N ("sum of squares" of independent variables) |
| regr_sxy(Y, X) | double precision | double precision | Yes | sum(X*Y) - sum(X) * sum(Y)/N (the "combination of product" of independent variables multiplied by the dependent variable) |
| regr_syy(Y, X) | double precision | double precision | Yes | sum(Y^2) - sum(Y)^2/N ("sum of squares" of dependent variable) |
| stddev(expression) | Smallint, int, bigint, real, double precision or numeric | Floating point parameter is double precision, otherwise it is numeric | Yes | Historical alias of stddev_samp |
| stddev_pop(expression) | Smallint, int, bigint, real, double precision or numeric | Floating point parameter is double precision, otherwise it is numeric | Yes | Overall standard deviation of input values |
| stddev_samp(expression) | Smallint, int, bigint, real, double precision or numeric | Floating point parameter is double precision, otherwise it is numeric | Yes | Sample standard deviation of input values |
| variance(expression) | Smallint, int, bigint, real, double precision or numeric | Floating point parameter is double precision, otherwise it is numeric | Yes | Historical alias of var_samp |
| var_pop(expression) | Smallint, int, bigint, real, double precision or numeric | Floating point parameter is double precision, otherwise it is numeric | Yes | The population variance of the input value (square of the population standard deviation) |
| var_samp(expression) | Smallint, int, bigint, real, double precision or numeric | Floating point parameter is double precision, otherwise it is numeric | Yes | Sample variance of input values (square of sample standard deviation) |
8.4 Ordered data sets
| function | Direct parameter type | Aggregation parameter types | Return type | Partial mode | describe |
|---|---|---|---|---|---|
| mode() WITHIN GROUP (ORDER BY sort_expression) | Any sortable type | Same as sorting expression | No | Return the most frequent input value (if there are multiple values with the same frequency, select the first one) | |
| percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression) | double precision | double precision or interval | Same as sorting expression | No | Continuous Percentage: Returns a value corresponding to the specified score in the sort, interpolated between adjacent inputs if necessary |
| percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression) | double precision[] | double precision or interval | Array of types of sort expressions | No | Multiple Continuous Percentage: Returns an array of results matching the shape of the fractions parameter, where each non-null element is replaced with a value corresponding to that percentage. |
| percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression) | double precision | A sortable type | Same as sorting expression | No | Discrete Percentage: Returns the first input value in sorting that is equal to or exceeds the specified score. |
| percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression) | double precision[] | Any sortable type | Array of types of sort expressions | No | Multiple discrete percentages: Returns an array of results matching the shape of fractions parameter, where each non-null element is replaced with the input value corresponding to that percentage. |
8.5 Imaginary aggregation function (sorting)
| function | Direct parameter type | Aggregation parameter types | Return type | Partial mode | describe |
|---|---|---|---|---|---|
| rank(args) WITHIN GROUP (ORDER BY sorted_args) | VARIADIC “any” | VARIADIC “any” | bigint | No | Ranking of imaginary rows, leaving a gap for repeated rows |
| dense_rank(args) WITHIN GROUP (ORDER BY sorted_args) | VARIADIC “any” | VARIADIC “any” | bigint | No | Ranking of imaginary rows without intervals |
| percent_rank(args) WITHIN GROUP (ORDER BY sorted_args) | VARIADIC “any” | VARIADIC “any” | double precision | No | Relative ranking of imaginary rows, ranging from 0 to 1 |
| cume_dist(args) WITHIN GROUP (ORDER BY sorted_args) VARIADIC “any” | VARIADIC “any” | double precision | No | The relative ranking of imaginary rows, ranging from 1/N to 1 |
8.6 Grouping Operation
| function | Return type | describe |
|---|---|---|
| GROUPING(args…) | integer | Integer bitmask indicates which parameters are not included in the current grouping set |
Examples of usage
WITH test_table AS ( SELECT UNNEST( ARRAY [ 'finance', 'administrative', 'Sale', 'finance', 'administrative', 'administrative' ] ) AS depart, UNNEST ( ARRAY [ 'A', 'B', 'A', 'C', 'D', 'C' ] ) AS NAME, UNNEST ( ARRAY [ 200, 100, 50, 30, 200, 100 ] ) AS donate ) SELECT depart, NAME, GROUPING ( depart, NAME ), SUM ( donate ), COUNT ( donate ) FROM test_table GROUP BY ROLLUP ( depart, NAME );
9. Conditional function
| function | grammar | Instructions for use | Use Example |
|---|---|---|---|
| case | CASE WHEN condition THEN result [WHEN …] [ELSE result] END | The CASE clause can be used anywhere an expression can appear. Each condition is an expression that returns the boolean result. If the result is true, the result of the CASE expression is a result that meets the criteria, and the remaining CASE expression will not be processed. If the result of the condition is not true, then search for any subsequent WHEN clause in the same way. If no WHEN condition is true, then the value of the CASE expression is the result in the ELSE clause. If the ELSE clause is omitted and no condition is true, the result is empty. | CASE WHEN a=1 THEN ‘one’ WHEN a=2 THEN ‘two’ ELSE 'other’END |
| coalesce | COALESCE(value [, …]) | Returns the value of its first non-null parameter. Empty is returned if and only if all parameters are empty. It is often used to replace null values with default values when retrieving data for display purposes. | COALESCE(description, short_description, ‘(none)’) |
| nullif | NULLIF(value1, value2) | When value1 and value2 are equal, NULLIF returns a null value. Otherwise it returns value1. | NULLIF(value, ‘(none)’) |
| greatest | GREATEST(value [, …]) | Select the largest numeric value from a list of arbitrary numeric expressions. The NULL value in the list will be ignored. The result will be NULL only when the result of all expressions is NULL. | greatest(2,5,1) |
| least | GREATEST(value [, …]) | Choose the smallest value from a list of any numeric expressions. The NULL value in the list will be ignored. The result will be NULL only when the result of all expressions is NULL. | least(2,6,5) |
10. Window Function
| function | Return type | describe |
|---|---|---|
| row_number() | bigint | The line number of the current row in its partition is calculated from 1 |
| rank() | bigint | Current row ranking with gaps; same as row_number for the first equivalent row of that row |
| dense_rank() | bigint | Current row ranking without gaps; this function counts the same group |
| percent_rank() | double precision | Relative ranking of the current row: (rank- 1) / (Total row count - 1) |
| cume_dist() | double precision | Cumulative distribution: (number of partition rows before the current row or at the same level) / total number of partition rows |
| ntile(num_buckets integer) | integer | From 1 to parameter value, partition as much as possible |
| lag(value anyelement [, offset integer [, default anyelement ]]) | The same type as value | Returns a value, which is calculated on the rows in the previous offset positions of the current row in the partition; if there is no such row, it returns default instead (must be the same as the value type). offset and default are both calculated based on the current row. If you ignore them, offset is 1 by default and default is null by default |
| lead(value anyelement [, offset integer [, default anyelement ]]) | The same type as value | Returns a value, which is calculated on rows at offset positions after the current row in the partition; if there is no such row, it returns default instead (must be the same as the value type). offset and default are both calculated based on the current row. If you ignore them, offset is 1 by default and default is null by default |
| first_value(value any) | same type as value | Returns the value calculated on the first row in the window frame |
| last_value(value any) | The same type as value | Returns the calculated value on the last row in the window frame |
| nth_value(value any, nth integer) | The same type as value | Returns the value calculated on the nth row (line counted from 1) in the window frame; without such rows, return a null value |
11. View all internal functions
How to view all function names in postgresql, SQL statements are as follows:
-- View all function names，Return type，and number of parameters SELECT pg_proc.proname AS "Function Name", pg_type.typname AS "Return value data type", pg_proc.pronargs AS "Number of parameters" FROM pg_proc JOIN pg_type ON (pg_proc.prorettype = pg_type.oid) -- WHERE pronamespace = (SELECT pg_namespace.oid FROM pg_namespace WHERE nspname = 'model')
Summarize
This is the end of this article about the detailed explanation of common functions and usage methods of Postgresql. For more related content on using Postgresql functions, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!