Answering this question,
Given this table (constructed from the question):
CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL); INSERT INTO wordcount (description) VALUES ('What a great day'); INSERT INTO wordcount (description) VALUES ('This is a product. It is useful');
produce this result:
id | word_count | unique_word_count | Description ---------+------------+-------------------+--------------- 1 | 4 | 4 | What a great day 2 | 7 | 6 | This is a product. It is useful
I gave the (correct) answer, which you can find here.
However in a comment, the OP then asked a further question - what if the string in question was
['a', ' ', ' ', 'b'] and my solution broke down completely - for starters, the string wouldn't even
INSERT into the table.
So, the question now is, how does one deal with strings like this - i.e. with apostrophes, square brackets &c. I'm going to give my own answer and also offer a bonus for a more elegant solution.
Solutions with multiple options will be highly prized as will those which show evidence of "thinking outside the box" (sorry for the cliché - but it fits here! :-) ). I'm also going to give a detailled explanation of my reasoning - that will earn kudos also! Options which mention other servers will also gain merit. Obviously, I can only award the bonus to one person, but I will upvote all decent answers.
I can only offer a bonus in two days - so I'll post my answer and will offer the bonus (+100) when I'm allowed to. Also, any solutions which deal with strings which my own can't deal with - haven't exhaustively tested yet.