GREATEST and LEAST #4 and #5 of #100PostgreSQLFunctions

This post is a part of the project #100PostgreSQLFunctions, which aims to explain the meaning of 100 PostgreSQL functions and how to use them. For the full list of functions click here.

GREATEST is a function to select the biggest value in a list. This function can have two or more arguments, each of the list values. The values in the list should be of the same datatype. If the datatypes are different, all values will be converted to the first argument datatype. NULL values in the list are ignored. If the values are strings, this function sorts the strings in alphabetical order and then selects the last one.

Examples:

SELECT
    GREATEST(1, 23, 45);

 greatest
----------
       45
(1 row)


SELECT
    GREATEST('Joe', 'Alex', 'Cory');

 greatest
----------
 Joe
(1 row)


SELECT
    GREATEST('3/3/2019'::date, '3/11/2019'::date, '3/5/2019'::date);

  greatest
------------
 2019-03-11
(1 row)


LEAST is a function to select the smallest value in a list. This function can have two or more arguments, each of the list values. The values in the list should be of the same datatype. If the datatypes are different, all values will be converted to the first argument datatype. NULL values in the list are ignored. If the values are strings, this function sorts the strings in alphabetical order and then selects the first one.

Examples:

SELECT
    LEAST(1, 23, 45);

 least
-------
     1
(1 row)


SELECT
    LEAST('Joe', 'Alex', 'Cory');

 least
-------
 Alex
(1 row)


SELECT
    LEAST('3/3/2019'::date, '3/11/2019'::date, '3/5/2019'::date);

   least 
------------
 2019-03-03
(1 row)

#100PostgreSQLFunctions

This is the project #100PostgreSQLFunctions, which aims to explain the meaning of 100 PostgreSQL functions and how to use them.

Here is the full list of functions:

1. generate_series
2. CONCAT
3. CONCAT_WS
4. GREATEST
5. LEAST
6. EXTRACT
7. date_trunc
8. date_part

CONCAT and CONCAT_WS #2 and #3 of #100PostgreSQLFunctions

This post is a part of the project #100PostgreSQLFunctions, which aims to explain the meaning of 100 PostgreSQL functions and how to use them. For the full list of functions click here.

CONCAT is a function to concatenate two or more strings into one.
This function can have two or more arguments, each of the strings to merge.

Examples:

SELECT
    CONCAT('Hello', 'world!');

    concat 
--------------
 Helloworld!
(1 row)


SELECT
    CONCAT('Hello', ' ', 'world!');

    concat 
--------------
 Hello world!
(1 row)


CONCAT_WS is a function to concatenate two or more strings with a separator. This function can have three or more arguments:

• separator is the string to between the others, it could be a space or whatever; 
• string1 is the first string to merge
• string2 is the other string to merge
• string_n is the third or more string to merge;

Example:

SELECT
    CONCAT_WS(' ', 'Hello', 'world!');

    concat 
--------------
 Hello world!
(1 row)