How to Insert Data into PostgreSQL Tables

In the previous lesson, we learned several useful operations for managing tables in PostgreSQL. We covered how to display a list of existing tables, rename a table, rename a column, add a new column to a table, and modify the data type of a column.

Before we delve into specific examples, it’s important to review the fundamentals of data insertion in PostgreSQL. As we learned earlier, PostgreSQL organizes data into tables consisting of rows and columns. Each row represents a record, and each column represents a field in that record.

In this lesson, we will cover the process of inserting data into a PostgreSQL table. We’ll learn how to insert single and multiple rows of data into a table, as well as how to select data from one table and insert it into another.

PostgreSQL Insert Statement

After creating tables in PostgreSQL, the next step is to insert data into them using the INSERT command.

To do this, we use the INSERT INTO statement with the following syntax:

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

The INSERT statement allows us to insert a single row of data into the specified table. We need to specify the table name, followed by the column names in parentheses, and the corresponding values in parentheses after the keyword VALUES.

Insert Data Into PostgreSQL Table

Let’s take an example to demonstrate this. We already have a database called geeksveda2, and we create a new table called books with columns id, title, price, created_at, and tags using the CREATE TABLE command as shown.

CREATE TABLE books(
id integer NOT NULL,
title character varying(255),
price numeric,
created_at timestamp with time zone,
tags character varying(255)[]
);

And if we open the books table, you will see values as shown.

\d books

To insert a new book into this table, we can use the following command:

INSERT INTO books (id, title, price, created_at, tags)  
VALUES (1, 'PostgreSQL Book', 30, NOW(), ARRAY['Book', 'Database', 'Postgres']);

Here, we’re inserting a new book with an id of 1, the title ‘PostgreSQL Book‘, a price of 30, and the current timestamp using the NOW() function, and an array of tags.

To confirm that the data was successfully inserted, we can use the SELECT statement to query the table:

SELECT * FROM books;

This will display all the rows in the books table, including the newly inserted row.

It’s worth noting that if we don’t pass values for all columns while inserting data, we need to specify the column names to which we’re providing values, and the values should be passed in the same order as the columns mentioned in the command.

Insert Multiple Rows Into a Table in PostgreSQL

In the previous example, we inserted a single row into the books table. However, in real-world scenarios, we often need to insert multiple rows at once instead of executing the same command multiple times.

We can achieve this in PostgreSQL by using the following syntax:

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

Here, we can see that we can insert multiple rows at once by separating them with commas and enclosing them in parentheses. Each set of values enclosed in parentheses represents a single row to be inserted.

For example, let’s say we want to insert three new books into the books table with different values for each row.

INSERT INTO books (id, title, price, created_at, tags)  
VALUES  
(2, 'Docker', 30, NOW(), ARRAY['Book', 'Docker', 'DevOps']),  
(3, 'Linux', 30, NOW(), ARRAY['Book', 'Linux', 'System']),  
(4, 'Java', 30, NOW(), ARRAY['Book', 'Java', 'Programming']);

Here, we’re inserting three new rows into the books table with different values for each row.

To confirm that the data was successfully inserted, we can use the SELECT statement to query the table:

SELECT * FROM books;

This will display all the rows in the books table, including the newly inserted rows.

As a result, we can see that we’ve successfully added three new books to our books table.

Copying Data Between Tables in PostgreSQL

In some cases, we may need to insert data into a new table that already exists in one of our existing tables. Instead of rewriting the data again, we can simply copy it from the existing table.

To achieve this in PostgreSQL, we can use the following syntax:

INSERT INTO new_table (column1, column2, column3, ...)  
SELECT column1, column2, column3, ...  
FROM existing_table;

Here, we can see that we can insert data into a new table by selecting the required columns from an existing table using the SELECT statement. We need to specify the new table name and the columns to be inserted into, followed by the SELECT statement that selects the columns from the existing table.

Let’s take an example to demonstrate this. Suppose we have an existing table called courses that contain a course about Rust with the same information that we have in the books table.

We can quickly create a new row in our table and copy the Rust course information from products into the books table using the following command:

INSERT INTO books (id, title, price, created_at, tags)  
SELECT id, title, price, created_at, tags  
FROM courses  
WHERE title = 'Rust';

Here, we’re selecting only the rows from the products table where the title column value is ‘Rust‘, and inserting them into the books table with the corresponding columns.

To confirm that the data was successfully inserted, we can use the SELECT statement to query the books table:

SELECT * FROM books;

This will display all the rows in the books table, including the newly inserted row. As a result, we can see that we’ve successfully copied the Rust course information from products into our books table.

It’s worth noting that we can add conditions to our SELECT statement to select only specific data from the existing table as required.

Conclusion

In conclusion, PostgreSQL is a powerful relational database management system that provides a wide range of functionalities to manage data in tables consisting of rows and columns.

In this lesson, we learned how to insert data into PostgreSQL tables using the INSERT command. We discussed how to insert a single row of data and how to insert multiple rows at once. We also covered how to select data from one table and insert it into another table.

The SELECT statement is used to retrieve data from existing tables, and the INSERT INTO statement is used to insert data into new tables. Overall, by mastering these operations, we can easily manipulate and manage our data in PostgreSQL tables.