PostgreSQL INSERT Statement

The INSERT statement in PostgreSQL is used to add new rows of data into a table. It is one of the most commonly used SQL operations. With PostgreSQL’s powerful data types and constraints, you can insert data in flexible and controlled ways.

1. Basic INSERT Syntax

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

- table_name → the table where data will be inserted - columns → list of columns you want to insert data into - values → the actual data values

2. Example: Inserting into a Table

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50),
  salary NUMERIC(10,2)
);

-- Insert a row
INSERT INTO employees (name, department, salary)
VALUES ('Alice Johnson', 'HR', 50000.00);

-- Insert multiple rows
INSERT INTO employees (name, department, salary) VALUES
('Bob Smith', 'IT', 65000.00),
('Charlie Brown', 'Finance', 70000.00);

3. Inserting Data without Specifying Columns

If you want to insert values for all columns in the table, you can omit the column list. However, the values must match the exact column order defined in the table.

INSERT INTO employees VALUES (4, 'David Wilson', 'Marketing', 55000.00);

4. Using DEFAULT Values

PostgreSQL allows columns to have DEFAULT values. You can explicitly use DEFAULT in an INSERT statement, or simply skip that column when inserting data.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2) DEFAULT 0.00,
  in_stock BOOLEAN DEFAULT TRUE
);

-- Using DEFAULT keyword
INSERT INTO products (name, price, in_stock)
VALUES ('Laptop', DEFAULT, DEFAULT);

-- Skipping DEFAULT columns
INSERT INTO products (name) VALUES ('Keyboard');

5. Inserting Multiple Rows at Once

You can insert multiple rows in a single statement for better performance.

INSERT INTO products (name, price, in_stock) VALUES
('Mouse', 20.00, TRUE),
('Monitor', 150.00, TRUE),
('Desk', 200.00, FALSE);

6. Inserting Data from Another Table

You can use INSERT INTO ... SELECT to copy data from another table or from the result of a query.

CREATE TABLE archived_employees AS TABLE employees WITH NO DATA;

-- Insert employees with salary greater than 60,000
INSERT INTO archived_employees
SELECT * FROM employees WHERE salary > 60000;

7. INSERT with RETURNING Clause

PostgreSQL supports the RETURNING clause, which returns values from the inserted row(s). This is very useful when working with auto-incrementing primary keys.

INSERT INTO employees (name, department, salary)
VALUES ('Emma White', 'Operations', 48000.00)
RETURNING id, name;

This query will insert a row and immediately return the id and nameof the inserted employee.

8. Handling Conflicts (UPSERT)

PostgreSQL provides a way to handle conflicts with ON CONFLICT. This is useful when inserting rows that may already exist.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(100) UNIQUE,
  name VARCHAR(100)
);

-- If email already exists, update the name instead of inserting
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice Wonderland')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name;

Conclusion

The INSERT statement in PostgreSQL is versatile, supporting single-row, multi-row, and query-based inserts. With DEFAULT, RETURNING, and ON CONFLICT, you have fine-grained control over how data is added and handled. In the next tutorial, we will explore how to update existing datain PostgreSQL tables using the UPDATE statement.