20 – Inserting Data into Tables in PostgreSQL

Introduction to Inserting Data in PostgreSQL

Inserting data into tables is a fundamental operation in PostgreSQL and other relational database management systems. It allows you to add new records to your database, making it possible to store and organize information effectively. In this guide, we will explore the various methods for inserting data into PostgreSQL tables, along with practical examples.

Using the INSERT Statement

The primary method for inserting data into PostgreSQL tables is through the use of the INSERT statement. The INSERT statement allows you to specify the table you want to insert data into and provide the values for each column. Here is the basic syntax for the INSERT statement:


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

For example, let’s say we have a ‘customers’ table with columns ‘customer_id,’ ‘customer_name,’ and ’email.’ To insert a new customer into the table, you would use the following query:


INSERT INTO customers (customer_id, customer_name, email)
VALUES (101, 'John Doe', 'johndoe@email.com');

This query inserts a new record with a ‘customer_id’ of 101, ‘customer_name’ of ‘John Doe,’ and ’email’ of ‘johndoe@email.com’ into the ‘customers’ table.

Inserting Data into Tables from Another Table

PostgreSQL allows you to insert data into a table by selecting and copying data from another table. This can be particularly useful for creating backup copies, archiving data, or transferring data between tables. Here’s an example:


INSERT INTO target_table (column1, column2, ...)
SELECT source_column1, source_column2, ...
FROM source_table
WHERE condition;

For instance, you can insert all orders placed in the year 2022 from the ‘orders’ table into the ‘archive_orders’ table using the following query:


INSERT INTO archive_orders (order_id, order_date, customer_id, total_amount)
SELECT order_id, order_date, customer_id, total_amount
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2022;

This query copies data from the ‘orders’ table to the ‘archive_orders’ table, but only for orders with ‘order_date’ in the year 2022.

Inserting Data into Tables with Default Values

When you need to insert data into a table but don’t have values for all columns, you can specify default values for the missing columns. PostgreSQL allows you to set default values for specific columns when creating the table, and these default values are used when no value is provided in the INSERT statement. Here’s an example:


CREATE TABLE products (
    product_id serial PRIMARY KEY,
    product_name VARCHAR,
    price numeric DEFAULT 0.00
);

-- Insert a product with only the name, the price will use the default value.
INSERT INTO products (product_name)
VALUES ('Sample Product');

In this example, the ‘price’ column has a default value of 0.00. When you insert a new product with only the ‘product_name,’ PostgreSQL automatically assigns the default value of 0.00 to the ‘price’ column.

Using the RETURNING Clause

The INSERT statement in PostgreSQL also allows you to retrieve values from the inserted record. This can be useful when you want to capture auto-generated values like serial or identity columns. The RETURNING clause provides a way to access these values. For example:


-- Create a table with a serial column
CREATE TABLE employees (
    employee_id serial PRIMARY KEY,
    first_name VARCHAR,
    last_name VARCHAR
);

-- Insert a new employee and retrieve the generated employee_id
INSERT INTO employees (first_name, last_name)
VALUES ('Alice', 'Smith')
RETURNING employee_id;

In this query, we create a table with a serial column ’employee_id.’ When inserting a new employee, we use the RETURNING clause to retrieve the generated ’employee_id’ value.

Inserting Multiple Rows at Once

You can insert multiple rows into a table with a single INSERT statement. To insert multiple rows, you provide a list of values separated by commas within the VALUES clause. Here’s an example:


-- Insert multiple employees
INSERT INTO employees (first_name, last_name)
VALUES
    ('John', 'Doe'),
    ('Jane', 'Smith'),
    ('Robert', 'Johnson');

This query inserts three employees into the ’employees’ table in a single statement.

Conclusion

Inserting data into tables is a fundamental operation in PostgreSQL and relational databases. The INSERT statement is the primary means of adding new records to your database tables. You can also copy data from one table to another and retrieve auto-generated values using the RETURNING clause. Understanding the methods for inserting data and their practical applications is essential for efficient database management and data organization.