PostgreSQL - INSERT Query: A Beginner's Guide

Hello there, future database wizards! Today, we're going to dive into the wonderful world of PostgreSQL and learn about one of its most fundamental operations: the INSERT query. Don't worry if you're new to programming - I'll guide you through this step-by-step, just like I've done for countless students over my years of teaching. So, grab a cup of coffee (or tea, if that's your thing), and let's get started!

What is an INSERT Query?

Before we jump into the syntax and examples, let's understand what an INSERT query actually does. Imagine you have a big book (that's our database) with several pages (tables). An INSERT query is like writing a new entry in one of these pages. It's how we add new information to our database tables.

Syntax

Now, let's look at the basic syntax of an INSERT query in PostgreSQL:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Don't let this intimidate you! Let's break it down:

• INSERT INTO: This is how we tell PostgreSQL that we want to add new data.
• table_name: This is the name of the table where we want to add our data.
• (column1, column2, column3, ...): These are the columns in our table that we want to fill with data.
• VALUES: This keyword introduces the actual data we're inserting.
• (value1, value2, value3, ...): These are the values we're adding, corresponding to the columns we specified.

Examples

Example 1: Inserting a Single Row

Let's say we have a table called students with columns for id, name, and age. Here's how we might add a new student:

INSERT INTO students (id, name, age)
VALUES (1, 'John Doe', 20);

What's happening here? We're adding a new student with an ID of 1, named John Doe, who is 20 years old. It's like filling out a form for a new student joining our class!

Example 2: Inserting Multiple Rows

PostgreSQL allows us to insert multiple rows in one go. It's like enrolling several students at once:

INSERT INTO students (id, name, age)
VALUES 
    (2, 'Jane Smith', 22),
    (3, 'Bob Johnson', 21),
    (4, 'Alice Brown', 23);

This query adds three new students to our table in one shot. Efficient, right?

Example 3: Inserting with Default Values

Sometimes, our tables have default values for certain columns. We can skip specifying these:

INSERT INTO students (name, age)
VALUES ('Charlie Davis', 19);

In this case, if our id column is set to auto-increment, PostgreSQL will automatically assign the next available ID.

Output

After executing an INSERT query, PostgreSQL typically returns a message indicating how many rows were inserted. For example:

INSERT 0 1

This means one row was successfully inserted. If we inserted multiple rows, it might look like:

INSERT 0 3

indicating three rows were added.

Advanced INSERT Techniques

Inserting Data from Another Table

Sometimes, we might want to copy data from one table to another. PostgreSQL makes this easy:

INSERT INTO new_students (name, age)
SELECT name, age FROM students WHERE age < 21;

This query copies all students under 21 from the students table to a new_students table. It's like creating a special class for younger students!

Inserting with a RETURNING Clause

PostgreSQL has a neat feature that allows us to see what was inserted:

INSERT INTO students (name, age)
VALUES ('David Wilson', 24)
RETURNING *;

This query not only inserts David into our table but also shows us the complete row that was added, including any default or automatically generated values.

Common INSERT Methods

Here's a table summarizing the INSERT methods we've discussed:

Method	Description	Example
Basic Insert	Inserts a single row	INSERT INTO students (name, age) VALUES ('John Doe', 20);
Multi-row Insert	Inserts multiple rows at once	INSERT INTO students (name, age) VALUES ('Jane', 22), ('Bob', 21);
Insert with Defaults	Lets the database fill in default values	INSERT INTO students (name) VALUES ('Alice');
Insert from Select	Copies data from another table	INSERT INTO new_students SELECT * FROM students WHERE age < 21;
Insert with Returning	Inserts and returns the added data	INSERT INTO students (name, age) VALUES ('David', 24) RETURNING *;

Conclusion

And there you have it, folks! We've journeyed through the land of PostgreSQL INSERT queries. From adding a single student to our class to enrolling a whole bunch at once, you now have the power to populate your databases with ease.

Remember, practice makes perfect. Try creating your own tables and inserting different types of data. Maybe create a table for your favorite books or movies and start adding to it. Before you know it, you'll be inserting data like a pro!

Happy coding, and may your databases always be well-populated and error-free!

Credits: Image by storyset