• Blog posts

    Bio

    DC_-_FAQ1.png

    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.

    DC_-_FAQ2.png

    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.

    DC_-_FAQ3.png

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

     DC_-_FAQ4.png

    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.

    DC_-_FAQ5.png

    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.

     DC_-_FAQ6.png

    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.

    DC_-_FAQ7.png

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

    DC_-_FAQ8.png

    select *

    from information_schema.tables

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

    DC_-_FAQ9.png

    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.

    DC_-_FAQ10.png

    select *

    from information_schema.tables

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

     DC_-_FAQ11.png

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

    DC_-_FAQ12.png

     

Comments

0 comments

Please sign in to leave a comment.