Creating and Managing Tables in PostgreSQL

Welcome to another crucial tutorial designed to further your understanding and skills in data engineering, particularly focusing on SQL database management. In this guide, we'll explore the process of creating and managing tables using PostgreSQL, crucial for performing basic DML and CRUD operations. This blog post is perfect for learners ready to apply their knowledge practically but have not yet delved into Data Definition Language (DDL).

Introduction

Creating tables is a fundamental aspect of database management, falling under DDL operations. DDL includes all database operations related to managing tables and other database structures. Before you can perform any data manipulation or query operations, you first need a place to store the data—hence the need for tables.

For those seeking a comprehensive learning platform, we recommend labs.itversity.com. It offers an advanced Big Data Cluster and expert support to help you master various data engineering tools and techniques.

Prerequisites

Before proceeding, ensure you have:

  • Access to a PostgreSQL server (in this case, hosted on pg.itversity.com).
  • An understanding of basic SQL commands.
  • Jupyter Notebook setup with SQL extension loaded for executing SQL commands directly.

Step 1: Connect to the Database

Start by connecting to your PostgreSQL database. This is essential to execute any SQL commands:

%load_ext sql
%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@pg.itversity.com:5432/itversity_sms_db

Step 2: Create the Table

Execute the following SQL command to create a new table in your database:

%%sql
DROP TABLE IF EXISTS users;
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' for user, 'A' for admin
    is_active BOOLEAN DEFAULT FALSE,
    create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

After creating the table, it's a good idea to watch this instructional video to see a practical demonstration:

Watch the video tutorial here.

Step 3: Validate Table Creation

After creating the table, it's good practice to validate that it has been correctly set up:

%%sql
SELECT * FROM information_schema.tables
WHERE table_catalog = 'itversity_sms_db' AND table_schema = 'public'
LIMIT 10;

Conclusion

Creating and managing tables is a foundational skill in database management, crucial for storing and manipulating data effectively. Remember, the ability to define and adjust database structures accurately is key to efficient data management and retrieval.

Dive into our courses and utilize our advanced Big Data Cluster with dedicated expert support to take your data engineering skills to the next level!


Thank you for visiting our resources! Explore labs.itversity.com for further learning opportunities in SQL, Python, Hadoop, Spark, and more. Engage with our community at discuss.itversity.com for updates and discussions that can help advance your data engineering skills.