Overview of Data Types

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.