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)

generate_series #1 of #100PostgreSQLFunctions

This is the first post 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.

generate_series is a function to generate a series of values between two points called start and stop. This function has three arguments:

• start is the first value of our series; 
• stop is the last possible value of our series; 
• step (optional) indicates the interval to generate every value from start to stop. If not indicated, it's equal to one by default.

Examples:

SELECT

    *

FROM generate_series(1, 10);

 generate_series 

-----------------

               1

               2

               3

               4

               5

               6

               7

               8

               9

              10

(10 rows)

SELECT

    *

FROM generate_series(1, 10, 2);

 generate_series 

-----------------

               1

               3

               5

               7

               9

(5 rows)

generate_series is useful also to create a series of dates

SELECT
    *
FROM generate_series('2019-1-1'::date, '2019-12-31'::date, '1 Month');

    generate_series     

------------------------

 2019-01-01 00:00:00-05

 2019-02-01 00:00:00-05

 2019-03-01 00:00:00-05

 2019-04-01 00:00:00-04

 2019-05-01 00:00:00-04

 2019-06-01 00:00:00-04

 2019-07-01 00:00:00-04

 2019-08-01 00:00:00-04

 2019-09-01 00:00:00-04

 2019-10-01 00:00:00-04

 2019-11-01 00:00:00-04

 2019-12-01 00:00:00-05

(12 rows)

I like SQL!

SELECT
    and_repeat ILIKE 'SQL'
FROM
    my_heart;

View this post on Instagram

🙈🐵🙈🐵 @aki_the_miniaussie @aussiesdoingthings

A post shared by Aussies Doing Things (@aussiesdoingthings) on Nov 2, 2019 at 8:05am PDT