1

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?

GMB
  • 216,147
  • 25
  • 84
  • 135
echo
  • 767
  • 2
  • 9
  • 24

1 Answers1

5

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';
GMB
  • 216,147
  • 25
  • 84
  • 135
  • This works great for number 1-9, for the case where there is a unknown number of digit suffix, would LIKE 'rangtable' cover all cases such as rangtable110 or rangtable001? I imagine something complex like regex is off the table with this kind of query? – echo Feb 08 '19 at 01:09
  • 1
    Yes `%` is the wildcard character, it matches any sequence of 0 to N characters. – GMB Feb 08 '19 at 01:14
  • ...and if you want more (actually: less) you'll have to think more. – wildplasser Feb 08 '19 at 01:15