PostgreSQL - INSERT

PostgreSQL - The INSERT Statement

The PostgreSQL INSERT INTO statement is used to add one or more new rows to a table. You can insert a single row with a VALUES clause or multiple rows at once.

Syntax

The basic syntax for inserting a single row is:

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

To insert multiple rows in a single statement, you can provide multiple tuples in the VALUES clause:

INSERT INTO table_name (column1, column2, ...)
VALUES
  (value1_row1, value2_row1, ...),
  (value1_row2, value2_row2, ...),
  (value1_row3, value2_row3, ...);

If you are providing values for all columns in their defined order, you can omit the column list. However, explicitly listing columns is a best practice for clarity and to avoid errors if the table structure changes.

Example: Modern Table and Inserts

Let’s create a more modern employees table. We’ll use BIGSERIAL for an auto-incrementing primary key, VARCHAR for variable-length strings, NUMERIC for precise salary figures, and TIMESTAMPTZ for timestamps with time zone information.

CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    age INT NOT NULL CHECK (age > 18),
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    hire_date TIMESTAMPTZ DEFAULT NOW()
);

Now, let’s insert a few rows into the employees table:

-- Single row insert, explicitly listing columns
INSERT INTO employees (full_name, age, department, salary)
VALUES ('Alice Wonderland', 32, 'Engineering', 95000.00);

-- Multi-row insert
INSERT INTO employees (full_name, age, department, salary)
VALUES
    ('Bob Builder', 25, 'Engineering', 80000.00),
    ('Charlie Chaplin', 27, 'Marketing', 85000.00);

-- Inserting a row with a NULL value for department
INSERT INTO employees (full_name, age, department, salary)
VALUES ('Diana Prince', 29, NULL, 110000.00);

Getting Data Back After an Insert: The RETURNING Clause

A very common requirement is to get the ID or other values of the row you just inserted. Instead of running a separate SELECT query, you can use the RETURNING clause. This is both more efficient and reliable.

INSERT INTO employees (full_name, age, department, salary)
VALUES ('Eve Adamson', 28, 'Sales', 72000.00)
RETURNING id, hire_date;

This statement would return the id and hire_date of the newly created record for ‘Eve Adamson’.

Output Message

Upon a successful insert, PostgreSQL returns a message like INSERT 0 1, where the 0 is a legacy OID value (no longer used by default) and 1 is the number of rows inserted.

Best Practice: Upsert with ON CONFLICT

What if you want to insert a new row, but if a row with a certain unique key already exists, you want to update it instead? This is known as an ‘upsert’ operation. PostgreSQL provides a clean solution with ON CONFLICT.

First, let’s add a unique constraint on the full_name.

ALTER TABLE employees ADD CONSTRAINT unique_full_name UNIQUE (full_name);

Now, we can use ON CONFLICT to handle duplicates. DO NOTHING will silently ignore the insert, while DO UPDATE will modify the existing row.

-- This will fail because 'Bob Builder' already exists
-- INSERT INTO employees (full_name, age, department) VALUES ('Bob Builder', 26, 'Maintenance');

-- This will update Bob's salary if he already exists
INSERT INTO employees (full_name, age, department, salary)
VALUES ('Bob Builder', 26, 'Maintenance', 82000.00)
ON CONFLICT (full_name)
DO UPDATE SET
    department = EXCLUDED.department,
    salary = EXCLUDED.salary,
    age = EXCLUDED.age;