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
);

• 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

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

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

%sql DROP TABLE IF EXISTS users

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

[]

%%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
)

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

[]

%%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

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