PostgreSQL Insert Statement

Basic Insert Syntax:

The INSERT statement in PostgreSQL is used to add new rows of data into a table. It can be used to insert one or more rows at a time.


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

Example:

To insert a single row into a table named 'employees':


INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com');

Console Output:

INSERT 0 1

Inserting Multiple Rows

Batch Insert:

PostgreSQL allows you to insert multiple rows in a single INSERT statement, which can improve performance by reducing the number of round trips to the server.


INSERT INTO employees (first_name, last_name, email)
VALUES 
('Jane', 'Smith', 'jane.smith@example.com'), 
('Robert', 'Brown', 'robert.brown@example.com');

Console Output:

INSERT 0 2

Using INSERT with SELECT

Insert with Data from Another Table:

You can also use the INSERT statement to copy data from one table to another using a SELECT statement.


INSERT INTO employees_archive (first_name, last_name, email)
SELECT first_name, last_name, email FROM employees WHERE status = 'inactive';

Console Output:

INSERT 0 5

Handling Conflicts with ON CONFLICT

Conflict Resolution:

The ON CONFLICT clause allows you to specify an alternative action to take when a proposed row conflicts with an existing row in the table.


INSERT INTO employees (id, first_name, last_name, email)
VALUES (1, 'Alice', 'Johnson', 'alice.johnson@example.com')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

Console Output:

INSERT 0 1

Returning Data After Insert

Using RETURNING Clause:

The RETURNING clause allows you to return values from the inserted row, which can be useful for obtaining the generated primary key.


INSERT INTO employees (first_name, last_name, email)
VALUES ('Mark', 'Twain', 'mark.twain@example.com')
RETURNING id;

Console Output:

id: 101

Inserting JSON Data

Working with JSON:

PostgreSQL supports storing JSON data. You can insert JSON data directly into JSON or JSONB columns.


INSERT INTO json_table (data)
VALUES ('{"name": "Anna", "age": 28, "city": "New York"}');

Console Output:

INSERT 0 1

Using DEFAULT Values

Inserting with Defaults:

You can use the DEFAULT keyword to insert default values defined in the table schema.


INSERT INTO employees (first_name, last_name, email)
VALUES ('Emily', 'Clark', DEFAULT);

Console Output:

INSERT 0 1

Inserting Data with Subqueries

Subquery in Insert:

You can use subqueries in the VALUES clause to dynamically insert data based on conditions or calculations.


INSERT INTO employees (first_name, last_name, email)
VALUES ((SELECT first_name FROM temp_employees WHERE id = 1),
        (SELECT last_name FROM temp_employees WHERE id = 1),
        'temp@example.com');

Console Output:

INSERT 0 1