How to filter a character followed by numbers string pattern in PostgreSQL?
You can use the operator ~ that uses the full power of regular expressions
select *
from (values ('#1234'), ('#3333'), ('#abcd')) as boats (number)
where number ~* '#\d{4}'

A stricter version of the regex would be '^#\d{4}$', so that it matches only strings that start with # then have 4, and only 4 numbers, and then there is the end of the string.