Counting the number of occurrences of a substring within a string in PostgreSQL

Return count of character,

 SELECT (LENGTH('1.1.1.1') - LENGTH(REPLACE('1.1.1.1','.',''))) AS count
--RETURN COUNT OF CHARACTER '.'

A common solution is based on this logic: replace the search string with an empty string and divide the difference between old and new length by the length of the search string

(CHAR_LENGTH(name) - CHAR_LENGTH(REPLACE(name, 'substring', ''))) 
/ CHAR_LENGTH('substring')

Hence:

UPDATE test."user"
SET result = 
    (CHAR_LENGTH(name) - CHAR_LENGTH(REPLACE(name, 'o', ''))) 
    / CHAR_LENGTH('o');

Other way:

UPDATE test."user" SET result = length(regexp_replace(name, '[^o]', '', 'g'));

A Postgres'y way of doing this converts the string to an array and counts the length of the array (and then subtracts 1):

select array_length(string_to_array(name, 'o'), 1) - 1

Note that this works with longer substrings as well.

Hence:

update test."user"
    set result = array_length(string_to_array(name, 'o'), 1) - 1;