PostGIS – Count Database Tables

Question:

Is it possible to Count the Number of Tables in my PostGIS Database?

Answer:

Yes, this is possible by querying the Information Schema for your PostGIS Database.

select *

from information_schema.tables

This statement will list all the tables within your PostGIS Database, but it will include system tables and tables within Schema that you may not be interested in… we will try and filter this list later.

To get a Count of the tables in your PostGIS Database, simply change the Select Statement to use a COUNT (*).

select count(*)

from information_schema.tables

Instead of listing each Table Name, the query now simply Counts the results / list of unique table names = 323.

As we saw earlier the list of Tables in the Information Schema list will include system tables and tables in Schema that you may not be interested in. To make the query more useful we will edit the query to only Select the Tables in specific Schemas using the IN clause.

select count(*)

from information_schema.tables

where table_schema IN ('public','netherlands','osmm')

The Count of Tables now only lists those Tables in the specified Schema = 92.

If we want to then LIST the tables we can change the Count(*) back to a Simple Select *

select *

from information_schema.tables

where table_schema IN ('public','netherlands','osmm')

Notice – that the Information Schema list also defines the TYPE of table e.g. View, Base Table etc…. so the last change we will make is to add a WHERE clause to only list Tables that are BASE TABLES.

select *

from information_schema.tables

where table_schema IN ('public','netherlands','osmm') and table_type = 'BASE TABLE'

If we Select each row we can use a Copy and Paste to then copy the Table List into XLS.