PostgreSQL: How to Insert Data into a Table

Welcome to The Coding College, where we guide you through mastering programming and databases! In this tutorial, we’ll focus on inserting data into tables in PostgreSQL. Adding records to a database is an essential operation that allows you to build and manage dynamic applications effectively.

What is the INSERT Statement?

The INSERT statement in PostgreSQL is used to add new rows of data into a table. It’s one of the most commonly used commands in SQL, enabling you to populate your database with meaningful information.

Basic Syntax of INSERT

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
  • table_name: The name of the table where data will be inserted.
  • column1, column2, ...: The names of the columns to insert data into.
  • value1, value2, ...: The values corresponding to the columns.

Example: Insert a Single Row

Let’s say you have a table called students:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    email VARCHAR(150)
);

To insert data into this table:

INSERT INTO students (name, age, email)
VALUES ('John Doe', 20, '[email protected]');

Inserting Multiple Rows

You can insert multiple rows in a single query:

INSERT INTO students (name, age, email)
VALUES 
('Alice Smith', 22, '[email protected]'),
('Bob Johnson', 19, '[email protected]'),
('Clara Brown', 21, '[email protected]');

Using Default Values

If a column has a default value (e.g., id SERIAL), you can omit it in the INSERT statement:

INSERT INTO students (name, age, email)
VALUES ('David Lee', 23, '[email protected]');

PostgreSQL will automatically assign a value to the id column.

Inserting Data into Specific Columns

If you want to insert data only into specific columns, omit others in the query:

INSERT INTO students (name, age)
VALUES ('Eva Green', 25);

The email column will be set to NULL (if allowed).

Insert Data from Another Table

You can also insert data into a table from another table using a SELECT query:

INSERT INTO students (name, age, email)
SELECT name, age, email
FROM backup_students;

This is helpful when migrating or copying data.

Preventing Errors with ON CONFLICT

To handle duplicate records, use the ON CONFLICT clause:

INSERT INTO students (id, name, age, email)
VALUES (1, 'John Doe', 20, '[email protected]')
ON CONFLICT (id) DO NOTHING;

Or update the record if it already exists:

INSERT INTO students (id, name, age, email)
VALUES (1, 'John Doe', 21, '[email protected]')
ON CONFLICT (id) DO UPDATE 
SET age = EXCLUDED.age;

Verify Inserted Data

After inserting data, you can verify it using a SELECT query:

SELECT * FROM students;

Common Errors and Solutions

  1. Column Count Mismatch
    Error: INSERT has more expressions than target columns.
    Solution: Ensure the number of values matches the number of specified columns.
  2. Data Type Mismatch
    Error: invalid input syntax for type ...
    Solution: Verify that values match the column’s data type (e.g., INTEGER, VARCHAR).
  3. Duplicate Key Value
    Error: duplicate key value violates unique constraint.
    Solution: Use ON CONFLICT or check for existing records before inserting.

Learn More at The Coding College

For more PostgreSQL tutorials and coding tips, visit The Coding College. We provide step-by-step guidance on database operations to help you build efficient and scalable applications.

Conclusion

The INSERT statement is a vital part of PostgreSQL that allows you to populate your database with meaningful data. By understanding its syntax and features like ON CONFLICT, you can handle a variety of data insertion scenarios effectively.

Have questions or need assistance? Share your queries below, and the team at The Coding College is here to help!

Leave a Comment