PostgreSQL check number of tables that match regex

It there a good way to check the number of existing table in the database that matches a particular format?

For example, I am looking for any table names that starts with 'rangtable', such as 'rangtable1', 'rangtable2', 'rangtable3'….etc, in this case, it returns three.

I am not sure if there is a query or term I need to search for in the SQL realm. Best I came up with is to do a full table query and then do a fetchall (I am using Python with PostgreSQL) than try to do some regex matching with the result.

Is there a better way?

Answer

You can query the Postgres INFORMATION_SCHEMA.TABLES view :

SELECT COUNT(*) AS cnt 
FROM INFORMATION_SCHEMA.TABLES 
WHERE table_name LIKE 'rangtable%';

If you need to match against a regular expression (which is not the case for the simple pattern you provided), you can use operator SIMILAR TO, or better yet POSIX operator ~ :

SELECT COUNT(*) AS cnt 
FROM INFORMATION_SCHEMA.TABLES 
WHERE table_name ~ '^rangtable';