PostgreSQL INSERT INTO: A Complete Guide to Adding Data

Posted on by

Your database tables are ready. You have designed the perfect structure. But without any data, a database is just an empty shell. So, how do you start filling it? You need a way to add new records, and that is where the PostgreSQL INSERT INTO command comes in. It is the fundamental tool for populating your tables.

In this complete guide, you will learn exactly how to insert data into PostgreSQL. We will start with the basic syntax for adding a single row. Then, we will show you how to efficiently add multiple rows at once. Finally, you will discover how to retrieve data from the very row you just inserted. This guide is perfect for beginners and anyone needing a clear refresher.

What is the INSERT INTO Command in PostgreSQL?

The INSERT INTO command is a core part of SQL’s Data Manipulation Language (DML). DML includes the four essential operations for managing data, often called CRUD: Create, Read, Update, and Delete. The INSERT statement is the “Create” part of this set. Its main job is simple but critical: to add one or more new rows of data into a table.

Think about any application you use. When you sign up for a new account, your information is inserted into a users table. When you buy something online, the order details are inserted into a sales table. This action of adding new records is essential for any dynamic application. Understanding how to use the PostgreSQL INSERT INTO command is a foundational skill for working with databases.

Prerequisites: Setting Up Your Practice Table

To truly learn how to insert data into PostgreSQL, you need a table to practice on. Let’s create one now. This hands-on approach helps you understand the concepts much better. We will build a simple employees table to store information about staff members.

First, open your SQL client, such as psql or DBeaver. Then, run the following command to create the table. This script defines several columns with different data types that are common in real-world scenarios.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary NUMERIC(10, 2)
);

Here is a quick breakdown of our table. The id is a self-incrementing primary key. The first_name and last_name fields cannot be empty. The email must be unique for each employee. Finally, hire_date and salary store the employee’s start date and pay.

The Basic Syntax of INSERT INTO

The syntax for the PostgreSQL INSERT INTO statement is straightforward. It tells the database exactly which table to modify, which columns to fill, and what values to add. Understanding its structure is the first step to populating a table in PostgreSQL.

Here is the standard syntax for the command:

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

Let’s break down each part of this statement:

  • INSERT INTO table_name: This part specifies the target table where you want to add the new row. You replace table_name with the actual name of your table, like employees.
  • (column1, column2, ...): This is the list of columns you intend to provide data for. While this list is technically optional, including it is a strong best practice. It makes your SQL code clearer and less prone to errors if the table structure changes later.
  • VALUES (value1, value2, ...): This clause contains the actual data you want to insert. The values here correspond directly to the columns listed earlier.

The most important rule is that the number of values and their data types in the VALUES clause must perfectly match the number of columns and their data types in the column list. If you list three columns, you must provide exactly three values of the correct types.

Practical Example: Inserting Your First Single Row

Now that you understand the syntax, let’s use the PostgreSQL INSERT INTO command to add our first new record. We will add an employee named Alice Johnson to the employees table we created earlier. This practical example will solidify your understanding.

The best way to write an insert statement is by explicitly listing the columns. This method is safe and readable. It ensures your command works even if columns are added to the table later.

INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES ('Alice', 'Johnson', 'alice.j@example.com', '2023-08-15', 75000.00);

After you run this command, PostgreSQL will confirm the operation. You should see a message like INSERT 0 1, which means one row was successfully inserted.

To verify that the data is really there, you can query the table using a SELECT statement. This command fetches all records from the employees table.

SELECT * FROM employees;

The output will show the new row for Alice Johnson, confirming your first successful data insertion!

Power Move: Inserting Multiple Rows in a Single Command

Inserting one row at a time works, but it is not very efficient. Imagine you have hundreds or thousands of records to add. Running a separate PostgreSQL INSERT INTO command for each one would be slow. It creates a lot of back-and-forth communication between your application and the database.

Fortunately, PostgreSQL allows you to insert multiple rows in a single command. This technique is much faster and more efficient. To do this, you simply list multiple sets of values in the VALUES clause, separating each set with a comma. This is the standard way to handle a `PostgreSQL insert multiple rows` operation.

The syntax looks like this:

INSERT INTO table_name (column1, column2, ...)
VALUES
    (row1_value1, row1_value2, ...),
    (row2_value1, row2_value2, ...),
    (row3_value1, row3_value2, ...);

Let’s add three more employees to our table at once. Notice how each person’s data is enclosed in its own set of parentheses.

INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES
    ('Bob', 'Smith', 'bob.s@example.com', '2023-09-01', 82000.00),
    ('Charlie', 'Brown', 'charlie.b@example.com', '2022-05-20', 68000.00),
    ('Diana', 'Prince', 'diana.p@example.com', '2024-01-10', 95000.00);

After running this, PostgreSQL will report that 3 rows were inserted. You can verify this again with SELECT * FROM employees; to see the fully populated table.

Advanced Usage: Getting Data Back with the RETURNING Clause

When you add a new record, you often need information about that new row immediately. The most common example is getting the auto-generated primary key. In our employees table, the id column is a SERIAL type, meaning PostgreSQL creates a unique ID for each new entry automatically. How do you get that new ID without running another query?

The answer is the powerful RETURNING clause. This is a fantastic extension in PostgreSQL that lets you get values back from the row you just inserted. It saves you from making a second trip to the database, which makes your application faster and your code cleaner. Using PostgreSQL INSERT RETURNING id is a common and highly effective pattern.

To use it, you simply add RETURNING followed by the column(s) you want at the end of your INSERT statement. For example, let’s add a new employee and immediately get their new ID.

INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES ('Eve', 'Adams', 'eve.a@example.com', '2024-02-28', 71000.00)
RETURNING id;

Instead of just INSERT 0 1, the command will now return the actual ID of the new employee, such as 5. You can also return the entire new row by using an asterisk (*).

INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES ('Frank', 'Miller', 'frank.m@example.com', '2023-11-12', 110000.00)
RETURNING *;

This command returns all the data for Frank Miller, including his newly assigned ID. The RETURNING clause is incredibly useful in application development when you need to use the new ID for subsequent database operations.

Common INSERT INTO Errors and How to Fix Them

As you work with the PostgreSQL INSERT INTO command, you will inevitably run into some errors. This is a normal part of learning. Let’s look at a few common issues and how you can quickly fix them.

Error 1: Column Count Mismatch

Problem: You provide a different number of values than the number of columns you listed.

Example Error Message:

ERROR: INSERT has more expressions than target columns

Bad Code:

-- Listing 4 columns but providing 5 values
INSERT INTO employees (first_name, last_name, email, hire_date)
VALUES ('Grace', 'Hopper', 'grace.h@example.com', '2024-03-05', 120000.00);

Solution: Ensure the number of columns in the list matches the number of values in the VALUES clause. Either add the missing column (salary) or remove the extra value.

Error 2: Data Type Mismatch

Problem: You try to insert a value that does not match the column’s data type, like putting text into a number field.

Example Error Message:

ERROR: invalid input syntax for type numeric: "Not a number"

Bad Code:

-- Trying to insert a string into the numeric 'salary' column
INSERT INTO employees (first_name, last_name, salary)
VALUES ('Heidi', 'Lamarr', 'Not a number');

Solution: Always provide data that matches the column’s defined type. In this case, replace the string with a valid number like 130000.00.

Error 3: Constraint Violation

Problem: You try to insert data that violates a table rule, such as leaving a NOT NULL column empty or duplicating a UNIQUE value.

Example Error Message:

ERROR: duplicate key value violates unique constraint "employees_email_key"

Bad Code:

-- Using an email address that already exists in the table
INSERT INTO employees (first_name, last_name, email)
VALUES ('Ivan', 'Ivanov', 'alice.j@example.com');

Solution: Check the data you are trying to insert. You must provide a unique value for the email column and ensure you do not leave any NOT NULL columns empty.

Conclusion

You have now learned the essentials of the PostgreSQL INSERT INTO command. We covered the basic syntax for adding a new record and showed how to efficiently insert multiple rows at once. You also discovered the power of the RETURNING clause to get back data immediately after an insertion.

Mastering this command is a crucial step toward becoming proficient with PostgreSQL. It is the foundation for building applications that can save and manage new information. The best way to get comfortable is to practice. Try creating your own tables and populating them with different kinds of data.

Now that you know how to add data, your next logical step is to learn how to modify it with the UPDATE command or remove it with the DELETE command. Keep practicing, and you will become an expert at manipulating data in PostgreSQL.