0

I have this PostgreSQL table for storing words:

CREATE TABLE IF NOT EXISTS words
(
    id bigint NOT NULL DEFAULT nextval('processed_words_id_seq'::regclass),
    keyword character varying(300) COLLATE pg_catalog."default",
    keyword_count double precision
)

insert into words (words)
VALUES ('while swam is interesting');

How I can count the words in column keyword and insert the number into column keyword_count using SQL query?

7
  • 2
    I don't understand exactly, but this sounds like a bad design. Don't store redundant data. Dec 4, 2022 at 14:11
  • Please provide example data to make your question clearer.
    – J.D.
    Dec 4, 2022 at 14:21
  • for example I have these words while swam is interesting. How I can count them? Dec 4, 2022 at 14:23
  • Why is the keyword_count DOUBLE PRECISION? Counts are INTEGERs! Also, less space!
    – Vérace
    Dec 5, 2022 at 14:35
  • Good point. What would be the recommend filed type? Dec 5, 2022 at 14:36

2 Answers 2

1

If your words are always separated by spaces, you could use regexp_count:

select regexp_count('while swam is interesting','\S+')

As mentioned in a comment, you should never store in a table something that can be easily computed at run time, otherwise you increase the risk of inconsistent data.

1
  • Here is another regex (for fun) select regexp_count ('while swam is interesting', '(^\w|\w$|\W\w|\w\W).')
    – Sahap Asci
    Dec 4, 2022 at 17:36
1

One way to count the words separated by whitespaces :

SELECT ARRAY_LENGTH(REGEXP_SPLIT_TO_ARRAY(keyword, '\s'), 1)
FROM words ;

As per insert the number into column keyword_count using SQL query, try:

UPDATE words set keyword_count = ARRAY_LENGTH(REGEXP_SPLIT_TO_ARRAY(keyword, '\s'), 1);

Demo

regexp_split_to_array()

array_length()

1
  • Thank you for the answer. Should I use stored procedure for this? Can you show me an example please? Dec 4, 2022 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.