Essential DML Commands for Efficient Database Management

PostgreSQL is a powerful and feature-rich database management system. Whether you're inserting, updating, or deleting data, practicing these commands can significantly enhance your efficiency. Let's explore key Data Manipulation Language (DML) operations with simple explanations and practical examples.

INSERT

The INSERT statement allows you to add new records into a table, either one at a time or in bulk.

Inserting a Single Record

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

Inserting Multiple Records

This approach improves efficiency when dealing with large datasets.

1. With Specified Column Names

INSERT INTO tableName (col1, col2) VALUES (value,value),(value,value),(value,value);        

2. Inserting Multiple Rows Without Specifying Column Names

INSERT INTO tableName VALUES 
(value,value),
(value,value),
(value,value);        

Key Points:

• Inserting multiple rows at once improves performance.
• The ON CONFLICT clause helps handle duplicate entries.
• PostgreSQL allows inserting array data directly into tables.
• The RETURNING clause retrieves inserted row data.

UPDATE

The UPDATE statement modifies existing data based on conditions.

UPDATE table_name
SET column1 = value1,
    column2 = value2, ...
WHERE condition;        

Types of UPDATE operations

1. Updating a single row
2. Updating multiple rows
3. Updating multiple columns

Key Points:

• The WHERE clause ensures that only specific records are updated.
• The RETURNING clause can fetch updated rows.
• Helps maintain dynamic and up-to-date data.

DELETE

The DELETE statement removes records from a table.

DELETE FROM table_name
WHERE condition;        

Types of DELETE operations

1. Deleting a Single Row
2. Deleting multiple rows

Key Points:

• Always use the WHERE clause to avoid deleting all records accidentally.
• ON DELETE CASCADE ensures child rows are deleted when a parent row is removed.
• The RETURNING clause retrieves deleted row details.
• Enable logging to maintain an audit trail of delete operations.

UPSERT (INSERT ON CONFLICT)

The UPSERT operation merges INSERT and UPDATE, ensuring data integrity by preventing duplicate entries.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_column)
DO NOTHING | DO UPDATE SET column1 = value1, column2 = value2, ...;        

Key Points:

• This is particularly useful for maintaining data integrity and avoiding duplicate entries.
• EXCLUDED Keyword: This keyword allows you to reference the values that you attempted to insert during the update operation, providing flexibility in how you modify existing records.
• Conflict Detection: The ON CONFLICT clause is used to specify which column(s) may cause a conflict during the insert operation. Typically, this would be a unique constraint or unique index.
• Conflict Resolution: If a conflict is detected, you can either:

1. Use DO NOTHING to skip the insert if a conflict occurs, or
2. Use DO UPDATE to update the existing row with new values based on the conflict.

Refer this notes Modifying data, for sample queries and additional concepts like RETURNING clause, ON DELETE CASCADE.

What are your favorite PostgreSQL commands? Share your thoughts in the comments! ??