Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE wordcount (
  id serial PRIMARY KEY
, description text
);

INSERT INTO wordcount (description) VALUES 
 ('What a great day')
,('This is a product. It is useful')
,($$['a', ' ', ' ', 'b']$$)
,($$['a', ' ', ' ', 'b' b ]$$)
,(' ?an? under_score : !an! under_score §$%&/ ')
,('%& .;:;')       -- NO word at all
, ('')             -- empty string
, (NULL)           -- NULL
;

CREATE TABLE



INSERT 0 8


-- keep rows with 0 words
SELECT id
     , COALESCE(cardinality(arr), 0) AS word_count
     , unique_word_count
     , description
FROM  (
   SELECT *, string_to_array(trim(regexp_replace(description, '\W+', ' ', 'g')), ' ') AS arr
   FROM   wordcount
   ) a
LEFT   JOIN LATERAL (
   SELECT count(DISTINCT elem) AS unique_word_count
   FROM   unnest(arr) elem
   ) b ON true;


id
word_count
unique_word_count
description


1
4
4
What a great day

2
7
6
This is a product. It is useful

3
2
2
['a', ' ', ' ', 'b']

4
3
2
['a', ' ', ' ', 'b' b ]

5
4
2
 ?an? under_score : !an! under_score §$%&/

6
0
0
%& .;:;

7
0
0


8
0
0
null



SELECT 8


-- faster, drop rows with 0 words
SELECT id
     , count(*) AS word_count
     , count(DISTINCT elem) AS unique_word_count
     , description
FROM  (
   SELECT id, description
        , unnest(string_to_array(trim(regexp_replace(description, '\W+', ' ', 'g')), ' ')) AS elem
   FROM   wordcount
   ) sub
GROUP  BY id, description;


id
word_count
unique_word_count
description


1
4
4
What a great day

2
7
6
This is a product. It is useful

3
2
2
['a', ' ', ' ', 'b']

4
3
2
['a', ' ', ' ', 'b' b ]

5
4
2
 ?an? under_score : !an! under_score §$%&/



SELECT 5


-- keep rows with 0 words:
SELECT id
     , count(*) AS word_count
     , count(DISTINCT elem) AS unique_word_count
     , description
FROM  (
   SELECT id, description, elem
   FROM   wordcount
   LEFT   JOIN LATERAL unnest(string_to_array(trim(regexp_replace(description, '\W+', ' ', 'g')), ' ')) AS elem ON true
   ) sub
GROUP  BY id, description;


id
word_count
unique_word_count
description


1
4
4
What a great day

2
7
6
This is a product. It is useful

3
2
2
['a', ' ', ' ', 'b']

4
3
2
['a', ' ', ' ', 'b' b ]

5
4
2
 ?an? under_score : !an! under_score §$%&/

6
1
0
%& .;:;

7
1
0


8
1
0
null



SELECT 8


-- Simpler (and faster?) - but '_' splits words:
SELECT w.id
     , count(*) AS word_count
     , count(DISTINCT token) AS unique_word_count
     , w.description
FROM   wordcount w, ts_parse('default', w.description) t
WHERE  t.tokid = 1 -- 'asciiword'
GROUP  BY w.id;


id
word_count
unique_word_count
description


1
4
4
What a great day

2
7
6
This is a product. It is useful

3
2
2
['a', ' ', ' ', 'b']

4
3
2
['a', ' ', ' ', 'b' b ]

5
6
3
 ?an? under_score : !an! under_score §$%&/



SELECT 5


-- Keep '_' from splitting words:
SELECT w.id
     , count(*) AS word_count
     , count(DISTINCT token) AS unique_word_count
     , w.description
FROM   wordcount w, ts_parse('default', replace(w.description, '_', 'x')) t
WHERE  t.tokid = 1 -- 'asciiword'
GROUP  BY w.id;


id
word_count
unique_word_count
description


1
4
4
What a great day

2
7
6
This is a product. It is useful

3
2
2
['a', ' ', ' ', 'b']

4
3
2
['a', ' ', ' ', 'b' b ]

5
4
2
 ?an? under_score : !an! under_score §$%&/



SELECT 5


-- Again, to keep all rows:
SELECT w.id
     , count(token) AS word_count
     , count(DISTINCT token) AS unique_word_count
     , w.description
FROM   wordcount w
LEFT   JOIN LATERAL (
   SELECT t.token
   FROM   ts_parse('default',description) t
   WHERE  t.tokid = 1 -- 'asciiword'
   ) t ON true
GROUP  BY w.id;


id
word_count
unique_word_count
description


1
4
4
What a great day

2
7
6
This is a product. It is useful

3
2
2
['a', ' ', ' ', 'b']

4
3
2
['a', ' ', ' ', 'b' b ]

5
6
3
 ?an? under_score : !an! under_score §$%&/

6
0
0
%& .;:;

7
0
0


8
0
0
null



SELECT 8
id	word_count	unique_word_count	description
1	4	4	What a great day
2	7	6	This is a product. It is useful
3	2	2	['a', ' ', ' ', 'b']
4	3	2	['a', ' ', ' ', 'b' b ]
5	4	2	?an? under_score : !an! under_score §$%&/
6	0	0	%& .;:;
7	0	0
8	0	0	null