PostgreSQL :- Datatype – bpchar

Background

I went back and started reviewing the script uses for creating the customer table in the Northwind database.

northwind

pthom

We are using the script furnished by pthom.

It is published via GitHub and availed here.

Here is the portion that creates the customer table

--
-- Name: customers; Type: TABLE; Schema: public; Owner: -; Tablespace:
--
CREATE TABLE customers (
    customer_id bpchar NOT NULL,
    company_name character varying(40) NOT NULL,
    contact_name character varying(30),
    contact_title character varying(30),
    address character varying(60),
    city character varying(15),
    region character varying(15),
    postal_code character varying(10),
    country character varying(15),
    phone character varying(24),
    fax character varying(24)
);

What is bpchar?

The customer_id column is defined as bpchar not null.

I am thinking what is bpchar?

Google end up defining bpchar as

Effectively in bpchar b stands for blank and p stands for padded and bpchar is same as char(n) or character(n) , a blank padded to n length string…

So it appears to be fixed length character data type.

Varchar

BTW, in PostgreSQL varchar is defined using this template

column-name character varying(length) nullability

And, so company name will be :-

company_name character varying(40) NOT NULL

bpchar (Length Defined)

If you find that you will like your character columns length rigidly defined, then issue something such as

column-name char(length) nullability

And, so company name will be :-

customer_id char(5) NOT NULL

Metadata

Column

information_schema.columns

Let us use the information_schema.columns view to better understand how column definition is exposed.

SQL


 select
	     tblISC.table_catalog as "catalog"

       , tblISC.table_schema  as "schema"

       , tblISC.table_name    as "table"

       , tblISC.column_name   as "column"

       , tblISC.ordinal_position as "postion"

       , tblISC.data_type        as "dataType"

       , tblISC.udt_name         as "userdefinedName"

       , tblISC.character_maximum_length as "maxLength"

       , case

             when tblISC.character_maximum_length is null then 'Null'
             else cast(tblISC.character_maximum_length as varchar(30))
         end as "maxLengthInterpreted"

       , tblISC.character_octet_length as "octetLength"

       , tblISC.character_octet_length
       		/ nullif
       		   (
       	            tblISC.character_maximum_length
       	          , 0
               ) as "bytesPerCharacter"

from   information_schema.columns tblISC

where  tblISC.table_schema = 'public'

and    tblISC.table_name in ( 'customers')

Output

Image – bpchar

northwind.customer.bpchar.01.20190728.1231PM

Image – char(n)

northwind.customer.char.lengthSpecfied.02.20190728.1233PM

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