PostgreSQL provides a very rich set of types and functions. Some of the commonly used functions can be referenced in this article.

Here are a few common scenarios for function expression that we show with examples

First, we create the following sample data

CREATE TABLE table_test (
    id int PRIMARY KEY,    
    test_name varchar(10),  -- Name
    group_code varchar(10), -- Group number
    create_at timestamp,    -- Creation Time
    status bool,            -- state
    test_desc varchar(100)  -- describe
);

-- Insert the following sample data
INSERT INTO table_test (id, test_name, group_code, create_at, status, test_desc)
 VALUES 
(1, 'No.1', '01', now(), true, 'be assessed as A'),
(2, 'No.1', '01', now(), true, 'be assessed as A'),
(3, 'No.2', '02', now(), true, 'be assessed as B'),
(4, 'No.3', '01', now(), null, 'be assessed as AC'),
(5, 'No. 4', '03', now(), true, 'be assessed as C'),
(6, 'No. 5', '03', now(), true, 'be assessed as AB');

NULL correlation

When a NULL value exists for a related field in a query condition, if you use WHERE field= @ parameter value directly, you will never match a record with a NULL value, because NULL=any value, including NULL=NULL itself, will not return true, but will remain NULL.

At this point, NULL can be converted to a default value when the field value is NULL.

coalesce() can be used to express this function by returning the first non-null value in the list of parameters that support an unlimited number of parameters.

So if we need to query based on status (including NULL values, which are the default false when NULL is used), the parameter value may be true/false, then it can be expressed as coalesce(status, false), and when status is NULL, the natural return is false behind it.

At this point: coalesce (status, false) = (case when status is null then false else status end)

-- status Exists NULL Value, the following parameters may be true/false
SELECT * FROM table_test 
 WHERE coalesce(status, false) = ?;

SELECT * FROM table_test 
 WHERE (case when status is null then false else status end) = ?;

Time-dependent

(1) Current time and time formatted output

The function now() takes the current system time, which is equivalent to the system variable CURRENT_TIMESTAMP

Time can be formatted using to_char (current_timestamp,'YYYY-MM-DD HH24:MI:SS US'), with the following formatter meanings:

YYYY year, MM month, DD day, HH24 24 hour time, MI minutes, SS seconds, US microseconds

SELECT now(), CURRENT_TIMESTAMP, to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS US')
-- Input Result 
"2019-04-21 23:24:45.276729+08" 
"2019-04-21 23:24:45.276729+08" 
"2019-04-21 23:24:45 276729"

(2) Time difference

Seconds between two times are used:

extract(epoch FROM (time 1 - time 2))

With this function, we can make a function to represent how long time has passed.

CREATE OR REPLACE FUNCTION public.f_time_span(
    t timestamp without time zone)
    RETURNS character varying
    LANGUAGE 'plpgsql' 
AS $$

DECLARE 
    lv_span_double double precision; 
    lv_span int8;
BEGIN
    -- Gets the number of seconds that the current time differs from the parameter time
    SELECT extract(epoch FROM (now() - t )) into lv_span_double;
    -- Convert to Integer
    lv_span := cast(lv_span_double as int8);

    IF lv_span < 11 THEN
       RETURN 'Seconds ago';
    END IF;

    IF lv_span < 60 then
       RETURN lv_span || ' Seconds ago';
    END IF;

    IF lv_span < 3600 THEN
       RETURN (lv_span / 60) || ' Minutes ago';
    END IF;       

    IF lv_span < 86400 THEN
       RETURN (lv_span / 3600) || ' Hours ago';
    END IF;      

    IF lv_span < 30 * 86400 THEN
       RETURN (lv_span / 86400) || ' Days ago';
    END IF;    

    IF lv_span < 365 * 86400 THEN
       RETURN (lv_span / (30*86400)) || ' Month ago';
    END IF;

    RETURN (lv_span / (365*86400)) || ' last year';
END 
$$;

We should apply it to queries, such as as as a description of how long it has been created between now

SELECT test_name, create_at, f_time_span(create_at) span_desc FROM table_test

image

UUID Related

With UUID, you need to install the extension uuid-ossp, which can be obtained from uuid_generate_v4() or uuid_generate_v1() once the extension installation is successful.

CREATE EXTENSION "uuid-ossp";

SELECT uuid_generate_v4()
-- output "da28ce8a-ca9b-483f-918e-dce96fe7137f"

Aggregate correlation

PostgreSQL provides more convenient aggregation functions than SUM, COUNT, MAX, MIN, AVG, etc.

String_agg (expression,'delimiter') --Concatenates a string of text by an aggregated expression value using a delimiter

Array_agg -- Forms an array of aggregated expression values

For example, in the sample data, if we want to return a list of names within each group number, we can:

SELECT GROUP_CODE,
      string_agg(test_name, ','), 
      array_agg(test_name) 
  FROM table_test GROUP BY GROUP_CODE

The result is as follows

Other supplements

(1) MD5 encryption

In user information tables, password information is usually stored unexplained. One way is to use the password's MD5 for one-way encrypted storage, which makes it more secure. PostgreSQL wants to provide a direct MD5 function. md5(text) gets the result of 32-bit MD5 encryption directly.

(2) Regular correlation

PostgreSQL supports regular expressions, which provide great flexibility and expressive space for queries.

The syntax format is:'string'~'regular expression', meaning similar to IsMatch('string','regular expression')

~* means ignoring case,!~or!~* denies

You can also use not'string'~'regular expression' to deny

-- test_desc Contain B or C Of
SELECT * FROM table_test  WHERE test_desc ~ 'B|C';
-- test_desc Contain ABC Two letters adjacent to each other
SELECT * FROM table_test  WHERE test_desc ~ '[A-C]{2}';
-- test_desc A Ending
SELECT * FROM table_test  WHERE test_desc ~ 'A$';
-- test_desc No A Ending
SELECT * FROM table_test  WHERE test_desc !~ 'A$';

PostgreSQL Pragmatic Application (1/5) Tree Level
PostgreSQL Pragmatic Application (2/5) Insert Conflict
PostgreSQL Pragmatic Application (3/5) Subtable Replication
PostgreSQL Pragmatic Application (4/5) JSON
Common Expressions of PostgreSQL Pragmatic Application (Five-fifths)