Overview of Data Types

Let us get an overview of supported datatypes in Postgres.

  • Here is the sample CREATE TABLE command for the review.

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    user_first_name VARCHAR(30) NOT NULL,
    user_last_name VARCHAR(30) NOT NULL,
    user_email_id VARCHAR(50) NOT NULL,
    user_email_validated BOOLEAN DEFAULT FALSE,
    user_password VARCHAR(200),
    user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A
    is_active BOOLEAN DEFAULT FALSE,
    created_dt DATE DEFAULT CURRENT_DATE,
    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Copy to clipboard

  • While creating tables in RDBMS databases, we should specify data types for the columns.

    • SERIAL is nothing but integer which is populated by a special database object called as sequence. It is typically used for surrogate primary key.

    • When SERIAL is specified, a sequence with table_name_serial_column_seq naming convention will be created. In our case it is users_user_id_seq.

    • INT or INTEGER is used to define columns with integer values. Most of the ids are defined as integer.

    • FLOAT or DOUBLE can be used to define columns used to store price, salary etc.

    • VARCHAR with length is used to define variable length columns such as name, email id etc.

    • CHAR can be used to define fixed length string columns - single character fields such as gender which store M or F, three character days or months etc.

    • BOOLEAN is used to store true and false values.

    • We can also use DATE or TIMESTAMP to store date or time respectively.

  • We can add columns, drop columns, modify columns by changing data types as well as specify default values using ALTER TABLE command.

  • Let us perform these tasks to understand about Data Types. Drop and recreate users table with the following details.

    • user_id - integer

    • user_first_name - not null and alpha numeric or string up to 30 characters

    • user_last_name - not null and alpha numeric or string up to 30 characters

    • user_email_id - not null and alpha numeric or string up to 50 characters

    • user_email_validated - true or false (boolean)

    • user_password - alpha numeric up to 200 characters

    • user_role - single character with U or A (for now we will use VARCHAR(1))

    • is_active - true or false (boolean)

    • created_dt - not null and date with out timestamp. It should be defaulted to system date.

%load_ext sql

Copy to clipboard

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Copy to clipboard

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Copy to clipboard

%sql DROP TABLE IF EXISTS users

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%%sql

CREATE TABLE users (
  user_id INT,
  user_first_name VARCHAR(30) NOT NULL,
  user_last_name VARCHAR(30) NOT NULL,
  user_email_id VARCHAR(50) NOT NULL,
  user_email_validated BOOLEAN,
  user_password VARCHAR(200),
  user_role VARCHAR(1),
  is_active BOOLEAN,
  created_dt DATE DEFAULT CURRENT_DATE
)

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%%sql 

SELECT table_catalog, 
    table_name,
    column_name,
    data_type,
    character_maximum_length,
    column_default,
    is_nullable,
    ordinal_position
FROM information_schema.columns 
WHERE table_name = 'users'
ORDER BY ordinal_position

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
9 rows affected.

Copy to clipboard

table_catalog table_name column_name data_type character_maximum_length column_default is_nullable ordinal_position
itversity_retail_db users user_id integer None None YES 1
itversity_retail_db users user_first_name character varying 30 None NO 2
itversity_retail_db users user_last_name character varying 30 None NO 3
itversity_retail_db users user_email_id character varying 50 None NO 4
itversity_retail_db users user_email_validated boolean None None YES 5
itversity_retail_db users user_password character varying 200 None YES 6
itversity_retail_db users user_role character varying 1 None YES 7
itversity_retail_db users is_active boolean None None YES 8
itversity_retail_db users created_dt date None CURRENT_DATE YES 9