Found what I was looking for. POSIX regex is a bit different from LIKE or SIMILAR TO.but it allows for more finely tuned pattern matching. The result should be the same as the 12152 query
SELECT COUNT(DISTINCT(id))
FROM posts
WHERE cooked ~* '\[quote'