Re: Get tables ending with numbers

Sathesh S <sathesh(dot)sundaram(at)hotmail(dot)com> writes:
> Im trying to get tables ending with numbers (last 8 characters should be numbers).

> For example: I have the tables "test_20160215" and "test_20160131" and "test_1". When i run the below sql im not getting any output.

> Select relname from pg_class where relname like '%[0-9]'

> Can someone please give some idea on this.

You're confusing SQL LIKE patterns with POSIX regexp patterns. "%" is
a wildcard only in the former; "[...]" is special only in the latter.
(The great thing about standards is there are so many to choose from :-()

A correct implementation of your stated requirement is
where relname ~ '[0-9]$'
or if you want to be picky about "last 8 characters" you could do
where relname ~ '[0-9]{8}$'
See
https://www.postgresql.org/docs/current/static/functions-matching.html

regards, tom lane