PostgreSQL :- Datatypes

Background

Datatypes is one of key building blocks of a database system.

Common Ones

Here are the some of the common ones in PostgreSQL.

  1. Bool
    • Values
      • true
      • false
  2. Character
    • pg_catalog::name
      • Internal Type for Object Names
      • 64 bytes Character
    • information_schema::sql_identifier
      • ANSI Compatible
    • Char
      • Fixed Length
    • Varchar
      • Variable Length
    • text
      • Variable Length unlimited in size
  3. Datetime
    • Date
    • Time
    • timestamp
    • timestamptz
  4. uuid
    • Unique Identifier
  5. Money
  6. JSON
    • jsonb
  7. XML
  8. Defined Objects such as Tables

Code

SQL

select 

            tblPT.oid::text
                as oid

          , tblPT.typname
                as "type"

        --, tblPT.typnamespace as typeNS
          , tblNS.nspname
                as "namespace"

          , tblPA.rolname
                as "owner"

          , tblPT.typcategory
                as "categoryID"

          , case tblPT.typcategory
                when 'A' then 'Array'
                when 'B' then 'Boolean'
                when 'C' then 'Composite'
                when 'D' then 'Date/time'
                when 'E' then 'Enum'
                when 'G' then 'G'
                when 'I' then 'Network Address'
                when 'N' then 'Numeric'
                when 'P' then 'Pseudo'
                when 'S' then 'String'
                when 'T' then 'Timespan'
                when 'U' then 'User Defined'
                when 'V' then 'Bit String'
                when 'X' then 'Unknown'
                else null
            end as category     

          , tblPT.typlen
                as "length"

          , tblPC.relname
                as "correspondentObject"

          --, tblPT.typelem as "typelem"
          , tblPTA.typname
                as "ancenstor"

from   pg_catalog.pg_type tblPT

left outer join pg_catalog.pg_type tblPTA

    on tblPT.typelem = tblPTA.oid

inner join pg_catalog.pg_namespace tblNS

    on tblPT.typnamespace = tblNS.oid

left outer join pg_catalog.pg_class tblPC

    on tblPT.typrelid = tblPC.oid

left outer join pg_catalog.pg_authid tblPA

    on tblPT.typowner = tblPA.oid

where (
            -- If typarray is not 0 then it identifies another row in pg_type
            -- which is the "true" array type having this type as element
            ( tblPT.typarray != 0 )

     )      

-- and ( tblPT.typcategory = 'S')

order by
    tblPT.oid

Output

pg_catalog.pg_type.01.20190816.1110PM

References

  1. PostgreSQL
    • Data Types
    • System Catalog

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