Select rows from my table where a character column matches a pattern I'm using Postgres 9.5. I have a name column of type "character varying".  I wonder how to select rows where the column begins with a number, followed by a space, followed by any other data.

So I would want rows where the column looks like:

12 DAVE
1 abcDEF
12345 LAST


But not when it looks like:

MYCOLUMN
1TEXT
=END
Use a regular expression:

SELECT *
FROM   tbl
WHERE  name ~ '^\d+ .+';


The regular expression explained:

^ .. start of string
\d  .. class shorthand for digits
+ .. 1 or more of the preceding atom
 .. plain space
.+ .. any character, again 1 or more of those

There are many different characters that look like a space. ' ' is just the plain space character. You might want to use the class shorthand \s instead to cover all of them.