PostgreSQL :- Listing Column Names

Background

Let us expose the columns in each of our tables.

Code

Outline

  1. Column Length
    • The Column length is one of the more perplexing attribute
    • The reason being the existence of a couple of columns that intertwine to properly decode it
      • Attributes
        • attlen ( Attribute Length )
        • atttypid ( Attribute Type ID )
        • atttypmod ( Record Type Specific Data )
          • Varchar
            • base length (4) + defined length
  2. pg_catalog.format_type
    • Get SQL name of a Data Type
    • Accepts type ID ( atttypid ) and Type Mode ( atttypmod )
  3. The data returned by pg_catalog.format_type can be parsed using :-
    • Regular Expression
    • Position Function

SQL


; with ctePGAttributeBaseline
(
      attrelid

    , attnum  

    , atttypid

    , atttypmod

    , columnType

)
as
(
    select

          tblPA.attrelid

        , tblPA.attnum  

        , tblPA.atttypid

        , tblPA.atttypmod

        , pg_catalog.format_type
                (
                      tblPA.atttypid
                    , tblPA.atttypmod
                )
            as "columnType"

    from pg_catalog.pg_attribute tblPA

)
, ctePGAttribute
(
      attrelid

    , attnum  

    , atttypid

    , atttypmod

    , columnType

    , lengthViaRegEx

    , lengthViaPos
)

as
(
    select

          attrelid

        , attnum  

        , atttypid

        , atttypmod

        , columnType

        , (
            regexp_matches
            (
                 columnType
                ,'\(([^()]*)\)'
            )
        )[1] as "lengthViaRegEx"

        , case
            when
                position('(' in columnType ) > 0
                and position(')' in columnType ) > 0
                then substring
                        (
                              columnType
                            , position('(' in columnType )
                            , position(')' in columnType )

                        )
            else null
         end as "lengthViaPostion"

    from ctePGAttributeBaseline ctePGAB

)

select
          tblNS.nspname
            as "schema"

        , tblC.relname
            as "object"

        , tblPA.attnum
            as "columnNumber"

        , tblPA.attname
            as "column"

        , tblPT.typname
            as "columnType"

        /*  

            , tblPA.attlen
                as "columnLength"

            , tblPA.atttypid

            , tblPA.atttypmod

        */

        , tblPT.typlen  

        , columnType

        , case
            when (tblPT.typlen>0)
                then tblPT.typlen::text

            when (tblPT.typlen=-1)
                then ctePGA.lengthViaRegEx
          end
              as "columnLenViaRegEx"

        , case
            when (tblPT.typlen>0)
                then tblPT.typlen::text

            when (tblPT.typlen=-1)
                then ctePGA.lengthViaPos
          end
              as "columnLenViaPosition"

        , case tblPA.attnotnull
            when true then 'No'
            when false then 'Yes'
          end
          as "nullability"

        /*
          , tblPTD.adsrc
            as "default"
        */

        , pg_catalog.pg_get_expr
            (
                  tblPTD.adbin
                , tblPTD.adrelid
            )
            as "columnExpr"

        , tblPA.attidentity
            as "identityID"

        , case tblPA.attidentity
            when '' then ''
            when 'a' then 'Generate Always'
            when 'd' then 'Generate By Default'
          end as "identity"

from pg_catalog.pg_class tblC

join pg_catalog.pg_namespace tblNS

    on tblC.relnamespace = tblNS.oid

join pg_catalog.pg_attribute tblPA

    on tblC.oid = tblPA.attrelid

join pg_catalog.pg_type tblPT

    on tblPA.atttypid = tblPT.oid

left outer join pg_catalog.pg_attrdef  tblPTD

    on tblPA.atthasdef = true
    and tblPA.attrelid = tblPTD.adrelid
    and tblPA.attnum = tblPTD.adnum

left outer join ctePGAttribute ctePGA

    on  tblPA.attrelid = ctePGA.attrelid

    and tblPA.attnum  = ctePGA.attnum

where  tblC.relkind = 'r'

/*
 * The number of the column.
 * Ordinary columns are numbered from 1 up.
 * System columns, such as oid, have (arbitrary) negative numbers.
 */
AND    tblPA.attnum > 0 

/*
 * This column has been dropped and is no longer valid.
*/
and   tblPA.attisdropped = false

and   tblNS.nspname not in
        (
              'information_schema'
            , 'pg_catalog'
        )

order by

          tblNS.nspname
        , tblC.relname
        , tblPA.attnum
        , tblPA.attname

Output

pg_catalog.pg_attribute.01.20190817.0124PM.PNG

Summary

The most difficult part is trying to figure out a column’s defined length.

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