Introduction
Inserting new records into a PostgreSQL database is a fundamental task for any application that stores data. This tutorial guides you through various examples, starting with the basics and advancing to more complex operations.
Before you begin, ensure you have PostgreSQL installed and accessible. You’ll need to be familiar with SQL syntax and have permissions to insert data into your targeted database.
Basic Insert Statements
Starting with the most straightforward case: inserting a single record into a table. Suppose you have a table named users with the fields id, name, and age.
INSERT INTO users (name, age) VALUES ('Alice', 23);
This statement will insert a new record with the specified values into the users table.
Inserting Multiple Records
PostgreSQL allows inserting multiple records at once:
INSERT INTO users (name, age)
VALUES
('Bob', 34),
('Charlie', 29);
Returning Data on Insert
Using the RETURNING clause, you can immediately retrieve data from the newly inserted record:
INSERT INTO users (name, age) VALUES ('Diana', 22) RETURNING id;
This returns the id of the inserted record, which can be useful for linking actions in your application.
Insert with Select
You can combine INSERT with SELECT to insert records based on data from other tables:
INSERT INTO users (name, age)
SELECT employee_name, employee_age FROM employees WHERE department = 'Sales';
This would insert records containing names and ages from the employees table where the department is Sales.
Concurrent Inserts and Transactions
Efficiently handling concurrent inserts ensures data integrity. Encapsulate your insert statement within a transaction:
BEGIN;
INSERT INTO users (name, age) VALUES ('Erica', 28);
COMMIT;
This ensures that either the full set of operations are completed or none at all if an error occurs.
Insert with Conflict Handling
PostgreSQL provides an ON CONFLICT clause to handle duplicates:
INSERT INTO users (id, name, age) VALUES (1, 'Fred', 45)
ON CONFLICT (id) DO NOTHING;
Insertion is skipped where a conflict on the id column occurs. You can also DO UPDATE if you desire.
Advanced Insertion Techniques
Building on our skills, we can explore batch inserts, bulk-loading data from CSV files, and insertions utilizing custom functions and triggers for complex data-handling logic.
Batch Inserts
For buffer many records and insert them in a single operation to improve performance:
INSERT INTO users (name, age) VALUES
('Gina', 31),
('Hank', 42),
...
('Zeke', 58);
Note that too large batches may require adjustment of PostgreSQL’s configuration parameters.
Bulk Loading from CSV
Use the COPY command to efficiently load data from a CSV file directly into your table:
COPY users(name, age) FROM '/path/to/users.csv' DELIMITER ',' CSV HEADER;
This presupposes a well-structured CSV file with a header matching your table columns.
Insert Using Functions and Triggers
PostgreSQL allows creating functions or triggers that can perform complex operations before or after an insert operation:
CREATE OR REPLACE FUNCTION update_user_count()
RETURNS TRIGGER AS $
BEGIN
UPDATE counts SET user_count = user_count + 1 WHERE id = 1;
RETURN NEW;
END;
$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_update_user_count
AFTER INSERT ON users
FOR EACH ROW EXECUTE FUNCTION update_user_count();
This trigger automatically updates a user count every time a new user is inserted.
Conclusion
Mastering various methods to insert records into a PostgreSQL database allows developers to choose the right approach for their requirements, from simple single inserts to complex bulk loading. Always test your statements and consider transaction safety during concurrent operations.