Database Essentials using Postgres - Creating Tables and Indexes - Overview of Data Types

In this article, we will discuss the various data types supported in PostgreSQL and how to create a table with specific data types using SQL commands.

Explanation for the video

Watch the video for a visual explanation on data types in PostgreSQL and how to create a table with specific data types.

Key Concepts Explanation

Supported Data Types

  • SERIAL: Integer populated by a special database object called a sequence. Typically used for surrogate primary keys.
  • INT or INTEGER: Columns with integer values.
  • FLOAT or DOUBLE: Columns used to store price, salary, etc.
  • VARCHAR: Variable length columns for strings like name, email.
  • CHAR: Fixed-length string columns for single characters like gender.
  • BOOLEAN: Stores true or false values.
  • DATE or TIMESTAMP: Store date or time respectively.

Altering Table Data Types

  • Add, drop, modify columns by changing data types.
  • Specify default values using the ALTER TABLE command.

Hands-On Tasks

  1. Drop and recreate a table named users with the following details:
    • user_id: Integer
    • user_first_name: Not null alphanumeric string up to 30 characters
    • user_last_name: Not null alphanumeric string up to 30 characters
    • user_email_id: Not null alphanumeric string up to 50 characters
    • user_email_validated: Boolean
    • user_password: Alphanumeric up to 200 characters
    • user_role: Single character with U or A (use VARCHAR(1))
    • is_active: Boolean
    • created_dt: Not null date without timestamp defaulted to system date.
%load_ext sql
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
%sql DROP TABLE IF EXISTS users

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

Conclusion

In this article, we covered the overview of data types in PostgreSQL and how to create a table with specific data types. Practice these hands-on tasks to solidify your understanding and feel free to engage with the community for further learning. Happy coding!

Watch the video tutorial here