PostgreSQL INSERT INTO

Key INSERT Concepts

INSERT INTO products (name, price)
VALUES ('Laptop', 999.99);

INSERT INTO products (name, price)
VALUES ('Mouse', 25.99),
       ('Keyboard', 79.99);

INSERT INTO users (name)
VALUES ('Alice')
RETURNING id, name;

INSERT INTO orders (customer_id)
VALUES (1);
-- Uses default timestamp

🔹 Basic INSERT Statement

The simplest form of INSERT adds one row to a table. You specify the table name, column names in parentheses, and corresponding values. Column order must match the values order for correct data insertion.

-- Insert a single product
INSERT INTO products (product_name, price, stock_quantity)
VALUES ('Wireless Mouse', 29.99, 150);

🔹 Insert Multiple Rows

You can insert multiple rows in a single statement by separating value sets with commas. This is more efficient than multiple INSERT statements. It reduces database round trips and improves performance significantly.

-- Insert multiple employees at once
INSERT INTO employees (first_name, last_name, email, salary)
VALUES 
    ('Alice', 'Johnson', '[email protected]', 75000),
    ('Bob', 'Smith', '[email protected]', 68000),
    ('Carol', 'Williams', '[email protected]', 82000);

🔹 Insert with RETURNING Clause

The RETURNING clause returns values from the inserted row, including auto-generated IDs. This is useful when you need to know the ID of newly created records. It eliminates the need for a separate SELECT query.

-- Insert and get the generated ID back
INSERT INTO users (name, email)
VALUES ('David Brown', '[email protected]')
RETURNING id, name, email;

🔹 Insert with Default Values

When columns have default values defined, you can omit them from INSERT statements. PostgreSQL automatically uses the default value for those columns. This simplifies your code and ensures consistency across your database.

-- Insert using default values for some columns
INSERT INTO orders (customer_id, total_amount)
VALUES (5, 299.99);
-- order_date and status use their default values

-- Or explicitly use DEFAULT keyword
INSERT INTO orders (customer_id, total_amount, status)
VALUES (6, 149.99, DEFAULT);

🔹 Insert All Columns

If you're inserting values for all columns in order, you can omit the column list. However, explicitly listing columns is recommended for clarity and maintainability. It makes your code self-documenting and less prone to errors.

-- Without column names (must match table column order)
INSERT INTO categories
VALUES (DEFAULT, 'Electronics', 'Electronic devices and gadgets', CURRENT_TIMESTAMP);

-- With column names (recommended)
INSERT INTO categories (category_name, description)
VALUES ('Books', 'Physical and digital books');

🔹 Insert from SELECT Query

You can insert data from another table using a SELECT statement. This is powerful for copying or transforming data between tables. It's commonly used for data migration, archiving, or creating summary tables.

-- Copy data from one table to another
INSERT INTO archived_orders (order_id, customer_id, order_date, total_amount)
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date < '2024-01-01';