PostgreSQL INSERT
Introduction
The INSERT statement is one of the fundamental operations in PostgreSQL that allows you to add new rows to a database table. As a beginner, mastering this command is essential for building and populating databases with your data.
In this tutorial, we'll explore how to use the PostgreSQL INSERT statement to add data to your tables. We'll cover basic insertions, multiple row insertions, and more advanced techniques that will help you efficiently manage your database.
Basic INSERT Syntax
The most basic form of the INSERT statement follows this syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Let's break down this syntax:
INSERT INTO table_name: Specifies the table where you want to add data(column1, column2, ...): Lists the columns you want to populate (this part is optional)VALUES (value1, value2, ...): Provides the values for each column in the same order
Simple INSERT Example
Let's start with a basic example. Assume we have a students table with the following structure:
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
enrollment_date DATE
);
To add a single student to this table:
INSERT INTO students (first_name, last_name, email, enrollment_date)
VALUES ('John', 'Doe', '[email protected]', '2023-09-01');
When this command is executed successfully, PostgreSQL will respond with:
INSERT 0 1
This message indicates one row was inserted successfully. The 0 refers to the OID (Object Identifier), which is typically not used in modern PostgreSQL.
What Happens When You Execute an INSERT?
When you run an INSERT statement:
- PostgreSQL verifies that the table exists
- Checks that the columns you specified exist
- Validates that the values match the column data types
- Adds the new row to the table
- Updates any indexes associated with the table
- Returns a confirmation message
Inserting Data Without Specifying Columns
You can omit the column list if you provide values for all columns in the exact order they are defined in the table:
INSERT INTO students
VALUES (DEFAULT, 'Jane', 'Smith', '[email protected]', '2023-09-02');
In this example:
DEFAULTis used for thestudent_idcolumn, which will use the default value (SERIAL will auto-increment)- The remaining values are provided in the order of the table columns
Note: While this approach works, explicitly listing columns is considered a best practice as it makes your code more resilient to table structure changes.
Inserting Multiple Rows
PostgreSQL allows you to insert multiple rows in a single statement, which is much more efficient than executing separate INSERT statements:
INSERT INTO students (first_name, last_name, email, enrollment_date)
VALUES
('Michael', 'Johnson', '[email protected]', '2023-09-03'),
('Emily', 'Williams', '[email protected]', '2023-09-03'),
('Robert', 'Brown', '[email protected]', '2023-09-04');
The output will show:
INSERT 0 3
This confirms that three rows were inserted successfully.
INSERT with Default Values
If a column has a default value defined (like our SERIAL primary key), you can explicitly use the DEFAULT keyword:
INSERT INTO students (student_id, first_name, last_name, email, enrollment_date)
VALUES (DEFAULT, 'David', 'Miller', '[email protected]', '2023-09-05');
Alternatively, you can omit columns with default values from your column list:
INSERT INTO students (first_name, last_name, email, enrollment_date)
VALUES ('Sarah', 'Wilson', '[email protected]', '2023-09-05');
INSERT with Expressions
You can use expressions in your INSERT statements rather than just literal values:
INSERT INTO students (first_name, last_name, email, enrollment_date)
VALUES
('James', 'Taylor', LOWER('[email protected]'), CURRENT_DATE);
In this example:
LOWER()function converts the email to lowercaseCURRENT_DATEuses the current system date
INSERT with RETURNING Clause
The RETURNING clause allows you to retrieve values of columns from the newly inserted rows:
INSERT INTO students (first_name, last_name, email, enrollment_date)
VALUES ('Lisa', 'Anderson', '[email protected]', '2023-09-06')
RETURNING student_id, first_name, last_name;
Output:
student_id | first_name | last_name
------------+------------+-----------
7 | Lisa | Anderson
(1 row)
This is particularly useful when you need to know the automatically generated values (like SERIAL/identity columns) for the newly inserted row.
INSERT with SELECT Statement
You can insert data that comes from a query on another table using INSERT INTO ... SELECT:
-- Assuming we have a temporary_students table
INSERT INTO students (first_name, last_name, email, enrollment_date)
SELECT first_name, last_name, email, enrollment_date
FROM temporary_students
WHERE status = 'approved';
This powerful technique allows you to copy data from one table to another, optionally applying filters through the WHERE clause.
Real-World Examples
Example 1: User Registration System
Imagine you're building a user registration system. When a new user signs up, you might execute:
INSERT INTO users (username, email, password_hash, created_at)
VALUES
('newuser123', '[email protected]', 'hashed_password_here', NOW())
RETURNING user_id;
The RETURNING clause gives you the new user_id which you might need for creating related records or notifying the user.
Example 2: Logging Application Events
In an event logging system, you might record application events:
INSERT INTO system_logs (event_type, message, severity, logged_at, user_id)
VALUES
('LOGIN_ATTEMPT', 'Successful login from IP 192.168.1.1', 'INFO', NOW(), 42),
('CONFIG_CHANGE', 'System settings updated by admin', 'WARNING', NOW(), 42);
Example 3: E-commerce Order Processing
When a customer completes an order in an e-commerce application:
-- First insert the order header
INSERT INTO orders (customer_id, order_date, shipping_address, total_amount)
VALUES (1001, CURRENT_DATE, '123 Main St, Anytown, US', 145.99)
RETURNING order_id;
-- Then insert the order items (assuming order_id 5001 was returned)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
(5001, 101, 2, 29.99),
(5001, 203, 1, 86.01);
Common Errors and Troubleshooting
Unique Constraint Violations
If you try to insert a row that violates a unique constraint:
-- Assuming email must be unique
INSERT INTO students (first_name, last_name, email, enrollment_date)
VALUES ('Duplicate', 'Email', '[email protected]', '2023-09-07');
PostgreSQL will respond with an error:
ERROR: duplicate key value violates unique constraint "students_email_key"
DETAIL: Key (email)=([email protected]) already exists.
Handling NULL Values
To explicitly insert NULL into a column:
INSERT INTO students (first_name, last_name, email, enrollment_date)
VALUES ('James', 'No Email', NULL, '2023-09-08');
This works only if the column allows NULL values. If not, you'll get an error:
ERROR: null value in column "email" of relation "students" violates not-null constraint
Data Type Mismatches
PostgreSQL will reject inserts with incorrect data types:
-- Trying to insert a text value into a date column
INSERT INTO students (first_name, last_name, email, enrollment_date)
VALUES ('Bad', 'Date', '[email protected]', 'not-a-date');
This produces:
ERROR: invalid input syntax for type date: "not-a-date"
INSERT Performance Considerations
Bulk Inserts
For large datasets, use multi-row INSERT statements or the COPY command for significantly better performance:
-- Multi-row INSERT is faster than multiple single-row INSERTs
INSERT INTO log_events (event_type, created_at)
VALUES
('VIEW', NOW()),
('VIEW', NOW()),
('CLICK', NOW()),
-- ... potentially hundreds or thousands of rows
('PURCHASE', NOW());
Transaction Blocks
Wrap multiple related INSERT statements in a transaction to ensure they all succeed or fail together:
BEGIN;
INSERT INTO orders (customer_id, total_amount)
VALUES (1001, 299.99)
RETURNING order_id;
-- Assuming order_id 5002 was returned
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (5002, 101, 1);
INSERT INTO order_payments (order_id, payment_method, amount)
VALUES (5002, 'CREDIT_CARD', 299.99);
COMMIT;
If any statement fails, you can ROLLBACK to prevent partial data insertion.
ON CONFLICT Clause (Upsert)
PostgreSQL 9.5+ introduced the "upsert" feature with the ON CONFLICT clause, allowing you to handle conflicts elegantly:
INSERT INTO students (student_id, first_name, last_name, email, enrollment_date)
VALUES (1, 'Updated', 'Student', '[email protected]', '2023-09-10')
ON CONFLICT (email)
DO UPDATE SET
first_name = EXCLUDED.first_name,
last_name = EXCLUDED.last_name,
enrollment_date = EXCLUDED.enrollment_date;
This attempts to insert a new record, but if the email already exists, it updates the existing record instead.
Summary
In this tutorial, we've covered:
- Basic PostgreSQL INSERT syntax
- How to insert single and multiple rows
- Working with default values and expressions
- Using the RETURNING clause to get information about inserted rows
- Inserting data from queries with INSERT INTO SELECT
- Real-world examples of INSERT statements
- Common errors and how to troubleshoot them
- Performance considerations for INSERT operations
- The ON CONFLICT clause for handling conflicts
The INSERT statement is a fundamental part of data manipulation in PostgreSQL. With these techniques, you can efficiently add data to your database tables in various scenarios, from simple single-row insertions to complex data loading operations.
Exercises
To practice what you've learned, try these exercises:
-
Create a
productstable with columns forproduct_id,name,price, andcategory, then insert five different products. -
Insert multiple rows into your products table in a single statement.
-
Use an INSERT with a RETURNING clause to add a product and retrieve its automatically generated ID.
-
Create a
product_reviewstable that references products, then write a transaction that inserts both a product and a review for that product. -
Write an "upsert" statement using ON CONFLICT that either adds a new product or updates its price if the product name already exists.
Additional Resources
- PostgreSQL Official Documentation on INSERT
- PostgreSQL COPY command for high-performance data loading
- PostgreSQL Transactions
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)