PostgreSQL – List Databases

Background

Let us list databases.

Code

Outline

  1. Access pg_catalog.pg_database
    • pg_catalog.pg_tablespace
      • Default Table space Name
        • pg_database.dattablespace = pg_tablespace.oid
    • pg_catalog.pg_authid
      • Authorizer
        • pg_database.datdba = pg_authid.oid
    • pg_catalog.pg_encoding_to_char
      • Encoding
        • pg_catalog.pg_encoding_to_char(pg_database.encoding )
    • pg_catalog.pg_stat_file
      • Database Creation Date
        • pg_catalog.pg_stat_file
          (
          ‘base’
          ||’/’
          ||pg_database.oid
          ||’/’
          ||’PG_VERSION’
          )

SQL


select 

          tblD.datname as database

        , ltrim
            (
                tblD.oid::text
                , ','
            ) as databaseID

        -- , tblD.dattablespace as tablespaceIDDefault

        , tblTSD.spcname as tableSpaceDefault

        -- , tblD.datdba as ownerID

        , tblAuth.rolname as "owner"

        --, tblD.encoding
        --  as encodingID

        , pg_catalog.pg_encoding_to_char
            (
                tblD.encoding
            )
            as "encoding"

        , tblD.datcollate

        , pg_size_pretty
            (
                pg_database_size
                (
                    -- datname::text
                    tblD.oid
                )
            ) as "databaseSize"

        , (pg_stat_file
            (
                'base'
                ||'/'
                ||tblD.oid
                ||'/'
                ||'PG_VERSION'
            )
          )
          .modification
          --.creation
          AS datecreated

        , tblD.datistemplate

        , tblD.datallowconn

from   pg_catalog.pg_database tblD

inner join pg_catalog.pg_tablespace tblTSD

        on tblD.dattablespace = tblTSD.oid

left outer join pg_catalog.pg_authid tblAuth

        on tblD.datdba = tblAuth.oid

order by
    tblD.datname asc 

Output

pg_catalog.pg_database.01.20190812.1255AM

References

  1. PostgreSQL
    • Functions and Operators
      • System Administrative Functions
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s