Inserting data into a PostgreSQL database is a fundamental operation in database management. It involves adding new records to a table, allowing you to store and organize information efficiently. In PostgreSQL, the statement is used for this purpose.
In PostgreSQL, the INSERT INTO statement is used to add new records into a table. This statement allows you to specify both the table name and the values you want to insert into specific columns. Let's explore the syntax and usage with examples.
Basic Syntax
The INSERT INTO statement is crucial for populating tables with data, whether you’re adding a single record or multiple records in a single query.
The basic syntax for a simple statement is as follows:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Here, is the name of the table you want to insert data into, and , , etc., represent the columns in the table. The clause contains the actual data you want to insert.
Example:
Consider a table named with columns , , and . To insert a new employee, you would use:
INSERT INTO employees (id, name, salary)
VALUES (1, 'John Doe', 50000);
This adds a new record to the table with the specified values for , , and .
Inserting Multiple Rows
You can insert multiple rows in a single statement by providing multiple sets of values:
INSERT INTO employees (id, name, salary)
VALUES (2, 'Jane Smith', 60000),
(3, 'Bob Johnson', 55000),
(4, 'Alice Brown', 70000);
This inserts three new records into the table in one go.
Inserting Data from Another Table
You can also insert data into a table from another table using the statement. This is useful for copying data or filtering it based on certain conditions.
INSERT INTO new_employees (id, name, salary)
SELECT id, name, salary
FROM old_employees
WHERE salary > 60000;
In this example, data from the table is inserted into the table only for those employees with a salary greater than 60000.
Handling Default Values
If a column has a default value defined, and you want to use that default, you can exclude the column from the statement.
INSERT INTO products (product_name, price)
VALUES ('Laptop'); -- Assuming 'price' has a default value
Here, the default value for will be used.
Returning Inserted Values
PostgreSQL allows you to retrieve values generated during the operation using the clause.
INSERT INTO customers (name, email)VALUES ('John Doe', 'john@example.com')
RETURNING customer_id;
This can be useful when you need to get the auto-generated primary key after inserting a new record.
Bulk Insert
For large datasets, it's often more efficient to use the command, especially when loading data from external files.
COPY employees FROM '/path/to/employees.csv' DELIMITER ',' CSV HEADER;
Here, data from a CSV file is loaded into the table.
Handling NULL Values
If a column allows values, you can explicitly insert using the keyword:
INSERT INTO employees (first_name, last_name, salary)
VALUES ('Sam', 'Jones', NULL);
Conclusion
The statement in PostgreSQL is a powerful tool for adding data to your database tables. Whether you're inserting a single record or multiple records, from literal values or another table, understanding the syntax and options available can greatly enhance your ability to manage and manipulate data within your PostgreSQL database.