Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > 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 > ; > > <pre> > ✓ > 8 rows affected > </pre> <!-- --> > 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; > > <pre> > 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 §$%&amp;/ > 6 | 0 | 0 | %&amp; .;:; > 7 | 0 | 0 | > 8 | 0 | 0 | <em>null</em> > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1b8655fa1821da5029bd7a55264e0fa0)*
back to fiddle