Q:

PostgreSQL return word after matching first character

I would like to return the words beginning with ':' from 'select :barns, :fowl, :cows from farm' using regexp_matches.
So far I have:
select regexp_matches('select :barns, :fowl, :cows from farm', '([:])\w+', 'g')

But this only returns the set {:}, {:}, {:}. I would like it to return {:barns}, {:fowl}, {:cows}
Thanks!

A:

That was an issue with the regex syntax. To get the desired word, you will have to group it under ()
            SELECT
                regexp_matches(
                    'select :barns, :fowl, :cows from farm',
                    '(:\w+)',
                    'g'
                )

