Inserting Data into PostgreSQL Database

In this lecture, we will learn how to insert data into a PostgreSQL table that is created with constraints. We will be using the student_cons table that was set up in the previous lecture. It is important to understand how to insert data in such a way that it follows the defined constraints in order to maintain the integrity of your database.

The student_cons table Review Recall the structure of our student_cons table:

CREATE TABLE student_cons(
    roll_number     BIGSERIAL NOT NULL PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    gender          VARCHAR(20) NOT NULL,
    date_of_birth   DATE NOT NULL
);

This table includes constraints such as NOT NULL on all columns and uses BIGSERIAL for automatically incrementing the roll_number.

Basic INSERT Command

To insert data into a table in PostgreSQL, you use the INSERT INTO statement. Here's how you can add a new student to the student_cons table:

INSERT INTO student_cons (first_name, last_name, gender, date_of_birth)
VALUES ('John', 'Doe', 'Male', '1998-04-30');

Explanation:

• INSERT INTO student_cons: Specifies the table into which the data will be inserted.

• (first_name, last_name, gender, date_of_birth): These are the columns for which we are inserting data. Note that roll_number is not included because it is a BIGSERIAL type, which PostgreSQL will automatically increment.

• VALUES ('John', 'Doe', 'Male', '1998-04-30'): These are the values being inserted into the respective columns. Each value must conform to the data type and constraints of its column.

Handling Constraints

• Since all columns are set with the NOT NULL constraint, every INSERT statement must provide a non-null value for each of these columns.

• The primary key (roll_number) is managed by PostgreSQL due to the BIGSERIAL data type, so it automatically increments with each new record, ensuring uniqueness.