Modify Data with SQL in PostgreSQL

Table of Contents

1. Introduction to SQL sublanguages
2. Module 1: The INSERT instruction

• INSERT statement syntax
• Inserting literal values (VALUES)
• Insert default values (DEFAULT)
• INSERT from query result
• INSERT via advanced views and constructions
• Conflict management: ON CONFLICT
• The RETURNING clause with INSERT

3. Module 2: The DELETE instruction

• The DMV Demo Database
• DELETE statement syntax
• Demo DELETE
• RETURNING with DELETE
• TRUNCATE statement

4. Module 3: The UPDATE and MERGE instructions

• UPDATE statement syntax
• Demo UPDATE
• MERGE statement
• MERGE demo

5. Managing concurrency with transactions and isolation levels

• ACID Properties
• Transaction usage (DTL)
• Concurrency phenomena
• Insulation levels

6. Module 5: Good data modification practices

• Isolation strategies
• Error handling
• Large Data Changes
• Tests and validation of changes

1. Introduction to SQL sublanguages

The SQL language is segmented into several sublanguages, each having a distinct role:

DDL (Data Definition Language)

The DDL includes three statements: CREATE, ALTER and DROP. These instructions form the foundation of any database creation. They define objects that store data, manage data, and host server-side code, such as functions and stored procedures.

DCL (Data Control Language)

The DCL helps manage security access by defining what users can or cannot do with database objects. In Postgres, this is done via roles (roles). The DCL has only two instructions:

• GRANT: grants permission to a role
• REVOKE: removes a permission previously granted to a role

DML (Data Manipulation Language)

The DML is the heart of this training. It includes three instructions:

• INSERT: load new data into a table
• UPDATE: modifies existing data
• DELETE: deletes existing data
• MERGE: bonus instruction combining the previous three

DQL (Data Query Language)

The DQL returns data to the client application. The most common statement is SELECT. Postgres adds the RETURNING clause, which allows DML instructions to return generated or modified data.

DTL (Data Transaction Language)

The DTL includes START/BEGIN, SAVEPOINT, COMMIT, ROLLBACK and SET. A transaction begins with a BEGIN or START, may contain one or more SAVEPOINT, and always ends with a COMMIT or a ROLLBACK.

2. The INSERT statement

Syntax of INSERT statement

The simplified syntax of the INSERT statement in Postgres is as follows:

INSERT INTO { table_name } [AS alias]
  [( column_name [, ...] )]
  [OVERRIDING { SYSTEM | USER } VALUE]
  { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
  [ON CONFLICT conflict_target conflict_action]
  [RETURNING { * | output_expression [AS alias] } [, ...] ]

Key syntax points:

• The INTO keyword is required in Postgres (some other DBMS treat it as optional).
• It is strongly recommended to include the list of target columns with each INSERT to improve readability and avoid disasters during subsequent changes to the table structure.
• The OVERRIDING clause controls how system-generated values ​​(IDENTITY columns, sequence values) behave.
• The ON CONFLICT clause defines how to handle constraint violations (duplicates, etc.).
• The RETURNING clause is non-standard, but allows an INSERT to return data simultaneously.

Inserting literal values ​​(VALUES)

The VALUES clause, also called a row constructor, is the simplest method for inserting data.

Demonstration table used in examples:

DROP TABLE IF EXISTS t;

CREATE TABLE t (
    identity_column_always  INT  NOT NULL  GENERATED ALWAYS AS IDENTITY,
    identity_column_default INT  NOT NULL  GENERATED BY DEFAULT AS IDENTITY,
    default_column          INT  NOT NULL  DEFAULT (0),
    unique_column           INT  NULL      UNIQUE,
    constraint_column       INT  NULL      CHECK (constraint_column > 0)
);

Inserting multiple lines with VALUES:

INSERT INTO t (default_column, unique_column, constraint_column)
VALUES  (1, 1, 1),
        (2, 2, 2);

SELECT * FROM t;

The two IDENTITY columns automatically generated values ​​1 and 2 respectively.

Insertion with SELECT without FROM:

INSERT INTO t (default_column, unique_column, constraint_column)
SELECT 3, 3, 3;

SELECT * FROM t;

It is possible to use a SELECT without a FROM clause to obtain the same result as a VALUES.

Inserting default values ​​(DEFAULT)

Postgres supports two main types of defaults:

• Columns with a DEFAULT constraint
• IDENTITY or sequence-based columns (auto-magic columns)

Important note: The misuse of surrogate keys (surrogate keys, magic identifiers) is one of the most widespread SQL practices and also one of the most harmful. It undermines data consistency, complicates queries, and hurts performance. It allows the insertion of logical duplicates, introduces unnecessary layers of abstraction, and prevents the query optimizer from using statistical histograms effectively.

Omitting columns to trigger defaults:

-- En omettant default_column, Postgres insère la valeur par défaut (0)
INSERT INTO t (unique_column, constraint_column)
VALUES (4, 4);

Explicit use of the DEFAULT keyword:

INSERT INTO t (default_column, unique_column, constraint_column)
VALUES (DEFAULT, 5, 5);

SELECT * FROM t;

Insertion with override for a column GENERATED BY DEFAULT:

INSERT INTO t (identity_column_default, unique_column, constraint_column)
VALUES (12, 6, 6);

SELECT * FROM t;

Attempt to override GENERATED ALWAYS (causes an error):

-- Ceci provoque une erreur
INSERT INTO t (identity_column_always, unique_column, constraint_column)
VALUES (12, 7, 7);

Override with OVERRIDING SYSTEM VALUE:

INSERT INTO t (identity_column_always, unique_column, constraint_column)
OVERRIDING SYSTEM VALUE
VALUES (12, 7, 7);

SELECT * FROM t;

The OVERRIDING SYSTEM VALUE clause tells Postgres to use the value we provide. OVERRIDING USER VALUE tells Postgres to ignore the supplied value and use the system-generated value.

INSERT from a query result

One of the most common uses of INSERT is to combine an insert with the results of a query, allowing you to exploit the full power of SQL composability.

-- Doubler le nombre de lignes dans la table t en utilisant les lignes existantes
INSERT INTO t (default_column, unique_column, constraint_column)
SELECT  default_column * 5, unique_column * 10, constraint_column + 10
FROM    t;

SELECT * FROM t;

Important: The keyword DEFAULT cannot not be used in the SELECT list of an INSERT...SELECT as with VALUES. Postgres will throw an error: DEFAULT is not allowed in this context. To trigger the default values, simply do not include the column in the INSERT column list.

-- Déclencher la valeur par défaut en ne spécifiant pas default_column
INSERT INTO t (unique_column, constraint_column)
SELECT  unique_column + 100, constraint_column * 10
FROM    t;

SELECT * FROM t;

INSERT via views and advanced constructs

Insertion via views

Views (views) are persistent database objects defined by a SELECT statement. They can be used as DML targets under certain conditions.

Rules for updating a view:

• The view FROM clause must reference exactly one base table or another updatable view.
• The view definition cannot contain: WITH, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET.
• Set operators UNION, INTERSECT, EXCEPT are not allowed.
• Aggregate functions, window functions, and set returning functions in the SELECT list also prevent updating.

-- Création d'une vue updatable
CREATE VIEW t_view AS
SELECT  unique_column,
        constraint_column
FROM    t;

-- Insertion via la vue
INSERT INTO t_view (unique_column, constraint_column)
VALUES (200, 250);

SELECT * FROM t;

-- Les colonnes non sélectionnées dans la vue reçoivent leur valeur par défaut
INSERT INTO t_view (unique_column)
VALUES (300);

SELECT * FROM t;

Note: CTEs (Common Table Expressions, WITH clauses) cannot be used as targets for DML operations in Postgres.

Conflict management: ON CONFLICT

Conflict resolution (conflict resolution) allows you to manage constraint violations during an INSERT. Conflicts can arise from PRIMARY KEY, UNIQUE, NOT NULL, EXCLUDE constraints (Postgres functionality).

ON CONFLICT DO NOTHING:

-- Ignorer les lignes en conflit et continuer avec les lignes non conflictuelles
INSERT INTO t_view (unique_column)
VALUES (300)
ON CONFLICT DO NOTHING;

SELECT * FROM t;

-- Exemple avec plusieurs lignes
INSERT INTO t_view (unique_column)
VALUES (300), (400)
ON CONFLICT DO NOTHING;

SELECT * FROM t;

ON CONFLICT DO UPDATE (UPSERT):

When we do not want to ignore duplicates but rather update the existing row with the values ​​of the conflicting row, we use DO UPDATE. You must then specify the conflict target (conflict target).

-- Mettre à jour constraint_column en cas de conflit sur unique_column
INSERT INTO t_view (unique_column, constraint_column)
VALUES  (300, 999),
        (500, 999)
ON CONFLICT (unique_column)
DO UPDATE SET constraint_column = EXCLUDED.constraint_column;

SELECT * FROM t;

Important: The EXCLUDED keyword refers to a virtual schema that contains the values ​​of the conflicting row (the row that could not be inserted). Unqualified identifiers to the left of the = sign always refer to the base target table.

The RETURNING clause with INSERT

Postgres allows DML statements, including INSERT, to return data.

Simple RETURNING:

INSERT INTO t_view (unique_column, constraint_column)
VALUES  (300, 998),
        (500, 998)
ON CONFLICT (unique_column)
DO UPDATE SET constraint_column = EXCLUDED.constraint_column
RETURNING *;

RETURNING with CTE to capture and log results:

DROP TABLE IF EXISTS new_table;

WITH insert_cte AS (
    INSERT INTO t (unique_column, constraint_column)
    VALUES (500, 9999), (600, 9999)
    ON CONFLICT (unique_column)
    DO UPDATE SET constraint_column = EXCLUDED.constraint_column
    RETURNING *
)
SELECT  *, NOW() AS ts, current_user AS sysuser
INTO    new_table
FROM    insert_cte;

SELECT * FROM new_table;

This pattern is excellent for retrieving IDENTITY values ​​generated during INSERT, which are often needed immediately for follow-up operations.

Cleaning:

DROP VIEW t_view;
DROP TABLE t;

3. The DELETE statement

The DMV Demo Database

For modules 2 to 5, a simplified DMV (Department of Motor Vehicles) database is used. It has two entities: people (persons) and cars (cars).

Data model:

• A person is identified by their ssn (social security number)
• A car is identified by its vin (Vehicle Identification Number)
• A car can have zero or one owner (owner_ssn)
• The relationship between owner_ssn of cars and ssn of people is a foreign key

Fundamental rules of relationships (sets):

• All columns in a table must have unique names
• Tables have no inherent order
• All rows must be unique
• It is strongly recommended to avoid referencing columns by their ordinal position, as the order of columns may change

Demonstration data:

• Ben: owner of the Ford and the BMW
• Megan: owner of the Toyota
• Rory: no car
• Tesla: no owner

Best practice: Representing dates according to the ANSI standard (YYYY-MM-DD) is strongly recommended to avoid issues with different locales.

DELETE statement syntax

DELETE FROM [ONLY] { table_name } [[AS] alias]
  [USING from_list]
  [WHERE condition | WHERE CURRENT OF cursor_name]
  [RETURNING { * | output_expression [AS alias] } [, ...] ]

Key points:

• DELETE FROM without a WHERE clause deletes all rows from the table (the table will still exist but will be empty).
• The ONLY keyword controls whether inherited tables are included in the operation.
• The USING clause allows you to reference additional tables to filter rows to delete (similar to a JOIN).
• The WHERE clause contains both row filters and join predicates with the USING tables.
• RETURNING works the same as INSERT.

DELETE demonstration

Validation before deletion (good practice):

-- Toujours valider d'abord avec un SELECT avant d'exécuter le DELETE
SELECT  ssn
FROM    people
WHERE   first_name = 'Megan'
        AND
        last_name = 'Moore';

Crucial best practice: First write the query as a SELECT and verify that it returns exactly what you expect before transforming it into a DELETE.

Basic DELETE:

DELETE  FROM people
WHERE   first_name = 'Megan'
        AND
        last_name = 'Moore';

This statement fails due to the foreign key constraint: update or delete on table people violates foreign key constraint on table cars. Megan’s cars are still referenced.

Foreign key behaviors during DELETE:

DELETE Megan’s cars (with subquery):

DELETE FROM cars
WHERE   owner_ssn = (   SELECT  ssn
                        FROM    people
                        WHERE   first_name = 'Megan'
                                AND
                                last_name = 'Moore'
                    );

Alternative syntax with USING:

-- Alternative avec la clause USING
DELETE FROM cars
USING   people
WHERE   cars.owner_ssn = people.ssn
        AND
        people.first_name = 'Megan'
        AND
        people.last_name = 'Moore';

Deletion of person:

-- Maintenant on peut supprimer Megan
DELETE  FROM people
WHERE   first_name = 'Megan'
        AND
        last_name = 'Moore';

-- Vérification
SELECT  *
FROM    people LEFT OUTER JOIN cars
        ON people.ssn = cars.owner_ssn;

DELETE via a view:

CREATE VIEW people_over_forty AS
SELECT  *
FROM    people
WHERE   birth_date < '1995-01-01';

-- DELETE via la vue
DELETE FROM people_over_forty
WHERE   first_name = 'Ben'
        AND
        last_name = 'Chen';

Important point: If Ben is not in the view (because he is not over 40), the DELETE counts 0 and does not attempt to delete, so no constraints are violated. Views can thus serve as layers of protection.

RETURNING with DELETE

RETURNING with correlated subqueries:

DELETE FROM cars
WHERE   make = 'BMW'
RETURNING   vin,
            (   SELECT  first_name
                FROM    people
                WHERE   people.ssn = cars.owner_ssn
            ) AS first_name,
            (   SELECT  last_name
                FROM    people
                WHERE   people.ssn = cars.owner_ssn
            ) AS last_name;

Limitation: A scalar subquery can only return one column. If you need several columns from the joined table, you must repeat the subquery. To avoid this repetition, it is best to use USING.

RETURNING with USING (recommended approach):

DELETE FROM cars
USING   people
WHERE   cars.make = 'BMW'
        AND
        people.ssn = cars.owner_ssn
RETURNING   cars.vin,
            people.first_name,
            people.last_name;

This pattern is important: the join logic is only written once and the people columns are directly accessible in RETURNING.

The TRUNCATE statement

TRUNCATE is used to quickly delete all rows from one or more tables. Conceptually, it’s like a DELETE without a WHERE clause, but with significant performance benefits and notable behavioral differences.

Debunking: Many manuals misclassify TRUNCATE as a DDL statement. This classification is mainly historical (inherited from Oracle). Logically, TRUNCATE behaves exactly like an unqualified DELETE: it deletes data, it does not touch the schema.

Syntax:

TRUNCATE [TABLE] [ONLY] table_name [, ...]
  [RESTART IDENTITY | CONTINUE IDENTITY]
  [CASCADE | RESTRICT]

Options:

• RESTART IDENTITY: Resets IDENTITY values to their original seed.
• CONTINUE IDENTITY: keeps the current values ​​of the IDENTITY counter.
• CASCADE: also truncates all related tables. Extremely dangerous, because it deletes all rows from all referenced tables, not just conflicting rows.
• RESTRICT: default behavior, prevents TRUNCATE if foreign keys reference the table.

Differences between DELETE and TRUNCATE:

Examples:

-- Échec : people est référencé par cars
TRUNCATE TABLE people;

-- Succès : cars n'est pas référencé
TRUNCATE TABLE cars;

SELECT * FROM cars; -- Table vide

-- Toujours en échec même si cars est vide (TRUNCATE regarde le schéma)
TRUNCATE TABLE people;

-- CASCADE permet de tout tronquer d'un coup
TRUNCATE TABLE people CASCADE;

4. UPDATE and MERGE statements

UPDATE statement syntax

Conceptually, an UPDATE is a combination of a DELETE followed by an INSERT. Some internal Postgres processes actually handle UPDATE exactly this way.

UPDATE [ONLY] { table_name } [[AS] alias]
SET { column_name = { expression | DEFAULT }
    | ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
    } [, ...]
[FROM from_list]
[WHERE condition]
[RETURNING { * | output_expression [AS alias] } [, ...] ]

Types of expressions in SQL:

• Table expression: resolves to a set (columns and rows), e.g. : base table, VALUES, subquery, view
• Row expression: special case of an expression table with a single row
• Scalar expression: special case of a row expression with a single column, resolves to a single atomic value

Key points:

• The FROM clause allows you to specify additional tables (similar to the FROM of a SELECT) to limit rows and derive values.
• The WHERE clause contains filter predicates and join predicates.
• RETURNING allows you to return data after modification, with access to the states before (old) and after (new) the update. This is unique to UPDATE among DML statements.

Behavior of FROM in Postgres:

Postgres joins all table expressions in the FROM clause of an UPDATE with a CROSS JOIN (Cartesian product) with the target table. Correct filtering is entirely the responsibility of the developer in the WHERE clause.

Demonstration UPDATE

Single UPDATE with literal value:

SELECT  *
FROM    people
WHERE   first_name = 'Ben'
        AND
        last_name = 'Chen';

-- Corriger la date de naissance de Ben
UPDATE  people
SET     birth_date = '1995-01-04'
WHERE   ssn = '933290491'; -- SSN de Ben

Golden rule: Always include a WHERE clause in an UPDATE. An UPDATE without WHERE updates all rows in the table.

UPDATE with subquery (multiple columns):

-- Mettre à jour la date de naissance de Ben pour qu'elle corresponde à celle de Megan
UPDATE  people
SET     birth_date = (  SELECT  birth_date
                        FROM    people
                        WHERE   ssn = '355295949') -- SSN de Megan
WHERE   ssn = '933290491'; -- SSN de Ben

UPDATE with FROM and implicit JOIN:

-- Mettre à jour birth_date ET last_name en une seule instruction avec FROM
UPDATE  people AS t
SET     birth_date = s.birth_date,
        last_name = s.last_name
FROM    people AS s
WHERE   t.ssn = '933290491'  -- Ben (cible)
        AND
        s.ssn = '355295949'; -- Megan (source)

The alias is necessary for the source (s) so that Postgres distinguishes between the two instances of the people table. You cannot specify aliases for target columns in SET, they are always assumed to belong to the target table.

UPDATE with RETURNING (old and new):

UPDATE  people AS t
SET     birth_date = s.birth_date,
        last_name = s.last_name
FROM    people AS s
WHERE   t.ssn = '933290491'
        AND
        s.ssn = '355295949'
RETURNING old.birth_date, new.birth_date, old.last_name, new.last_name;

The ability to return both old and new values ​​is incredibly useful for auditing changes and resolving update anomalies.

The MERGE statement

Since UPDATE can be thought of as a DELETE followed by an INSERT, MERGE is the all-in-one version that combines INSERT, UPDATE and DELETE in a single statement.

States of rows in a MERGE:

Syntax:

MERGE INTO [ONLY] { target_table } [[AS] alias]
USING { table_name | query } [[AS] alias]
  ON join_condition
{ WHEN MATCHED [AND condition] THEN { UPDATE SET ... | DELETE | DO NOTHING }
| WHEN NOT MATCHED BY TARGET [AND condition] THEN { INSERT ... | DO NOTHING }
| WHEN NOT MATCHED [BY SOURCE] [AND condition] THEN { UPDATE SET ... | DELETE | DO NOTHING }
} [...]
[RETURNING { * | output_expression [AS alias] } [, ...] ]

Important warning: The USING clause in MERGE is different from the USING clause in DELETE. It is not limited to a CROSS JOIN (although one can be made with a TRUE predicate).

Types of joins (theta joins): The join predicate in a MERGE does not necessarily have to be an equality comparison. Dr. Codd defined 10 types of theta joins distinguished by the comparison operator: =, <>, <, <=, >, >=, and in version 2, four additional specialized joins for finding proximal values.

MERGE Demo

Creating the source table:

CREATE TABLE cars_update (
    vin     CHAR(17)    NOT NULL,
    make    VARCHAR(10) NOT NULL,
    model   VARCHAR(10) NOT NULL,
    color   VARCHAR(10) NOT NULL,
    PRIMARY KEY (vin)
);

INSERT INTO cars_update (vin, make, model, color)
VALUES  ('P9R8F4T1H6K2M7C5B', 'Porsche', '914',   'Silver'),  -- Nouvelle voiture
        ('1N4AL21E47C175495', 'Tesla',    'S',     'Pink');    -- Repeinte

Viewing current status with FULL OUTER JOIN:

SELECT  *
FROM    cars_update AS u
        FULL OUTER JOIN
        cars AS t
        ON t.vin = u.vin;

MERGE complete:

MERGE   INTO cars AS c
USING   cars_update AS cu
        ON c.vin = cu.vin
WHEN MATCHED THEN
    UPDATE SET color = cu.color
WHEN NOT MATCHED BY TARGET THEN
    INSERT (vin, make, model, color)
    VALUES (cu.vin, cu.make, cu.model, cu.color)
WHEN NOT MATCHED BY SOURCE THEN
    DO NOTHING;

Strong recommendation: Although MERGE seems convenient, it is strongly recommended to avoid MERGE in practice. Breaking the logic into separate explicit INSERT, UPDATE and DELETE is almost always safer, clearer and easier to reason about. MERGE hides a lot of complexity behind a single instruction, making its behavior difficult to predict, especially when something goes wrong.

5. Managing concurrency with transactions and isolation levels

ACID Properties

Relational database systems must follow the ACID properties, an acronym proposed in 1983 by professors Andreas Reuter and Theo Haerder.

Example of ATM withdrawal:

The example of an ATM withdrawal illustrates atomicity: checking the balance, debiting the account, and issuing the tickets are three independent operations that must all succeed or all fail together.

Transaction usage (DTL)

Instructions for starting a transaction:

• BEGIN
• BEGIN WORK
• BEGIN TRANSACTION
• START TRANSACTION

All are 100% equivalent. The BEGIN TRANSACTION syntax is the most recommended because it is the most popular and supported by all the main DBMSs.

Instructions for completing a transaction:

• COMMIT / COMMIT WORK / COMMIT TRANSACTION
• ROLLBACK / ROLLBACK WORK / ROLLBACK TRANSACTION

Simple transaction example:

BEGIN TRANSACTION;

UPDATE  people
SET     last_name = 'Doe'
WHERE   first_name = 'Ben';

SELECT  *
FROM    people;
-- Les changements sont visibles à l'intérieur de la transaction, même avant COMMIT

ROLLBACK TRANSACTION;
-- Ben récupère son nom original

Note on pgAdmin: By default, pgAdmin automatically wraps each statement with BEGIN TRANSACTION and COMMIT TRANSACTION (auto commit). This behavior can be disabled in pgAdmin preferences or in the execution options of each query window.

Important: In a transaction, all changes are visible in the entire transaction scope, whether committed or not. This behavior may surprise developers with little experience.

SAVEPOINT:

Postgres allows transactions to be segmented into small blocks, allowing you to roll back only part of the work done.

BEGIN TRANSACTION;

-- ... opérations ...

SAVEPOINT mon_savepoint;

-- ... autres opérations ...

ROLLBACK TO SAVEPOINT mon_savepoint; -- Annule seulement depuis le savepoint

COMMIT TRANSACTION;

Tip: Before using SAVEPOINT, make sure that different sections of the job would not benefit from having separate transactions. It’s always cheaper not to do the job at all than to do it and then cancel it.

Competition phenomena

The SQL standard committee formally defines four concurrency phenomena:

1. Lost Update

A concurrency phenomenon where one transaction overwrites the modification of another transaction.

Transaction A : modifie X
Transaction B : modifie X (écrase A)
Transaction A : COMMIT
Transaction B : COMMIT → La modification de A est perdue

Good news: This phenomenon cannot happen in Postgres nor in major relational DBMSs.

2. Dirty Read

Occurs when a transaction reads a resource modified by another transaction which eventually rollbacks its changes.

Transaction A : modifie X
Transaction B : LIT X (valeur modifiée, jamais commitée)
Transaction A : ROLLBACK
→ Transaction B a agi sur une valeur qui n'a jamais existé en base

Good news: Dirty reads cannot occur in Postgres. The READ UNCOMMITTED isolation level is treated as READ COMMITTED in Postgres.

3. Non-Repeatable Read

Occurs when a transaction cannot read a consistent version of a resource more than once due to changes made by other transactions.

Transaction B : LIT X (valeur = A)
Transaction A : modifie X (valeur = B), COMMIT
Transaction B : LIT X à nouveau (valeur = B, différente !)

4. Phantom Rows

A variation of non-repeatable reading. One transaction reads a set of rows matching a filter, then another transaction inserts a new row that satisfies that filter.

Transaction B : SELECT lignes WHERE valeur BETWEEN 1 AND 10 → 3 lignes
Transaction A : INSERT une ligne avec valeur = 4, COMMIT
Transaction B : SELECT lignes WHERE valeur BETWEEN 1 AND 10 → 4 lignes !

Insulation levels

The SQL standard defines four isolation levels to manage these phenomena:

*In Postgres, REPEATABLE READ also prevents phantom rows, which goes beyond the SQL standard. The behavior of REPEATABLE READ in Postgres is equivalent to the SERIALIZABLE level of the SQL standard.

Recommendation: Always explicitly specify the required isolation level when starting a transaction, because the default values ​​can be changed by the database administrator.

Example with READ COMMITTED:

-- TRANSACTION A
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT  color
FROM    cars
WHERE   make = 'Ford';
-- Retourne 'White'

-- Pendant ce temps, TRANSACTION B modifie et commite
-- BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- UPDATE cars SET color = 'Silver' WHERE make = 'Ford';
-- COMMIT TRANSACTION;

SELECT  color
FROM    cars
WHERE   make = 'Ford';
-- Retourne maintenant 'Silver' → NON-REPEATABLE READ (par conception)

COMMIT TRANSACTION;

Example with REPEATABLE READ:

-- TRANSACTION A
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT  *
FROM    cars
WHERE   color = 'Blue';
-- Retourne BMW uniquement

-- TRANSACTION B met à jour Ford en blue et COMMITE

SELECT  *
FROM    cars
WHERE   color = 'Blue';
-- Retourne TOUJOURS seulement BMW → Phantom row ELIMINÉE dans Postgres

COMMIT TRANSACTION;

SERIALIZABLE:

SERIALIZABLE is the most restrictive isolation level. In Postgres, it eliminates a state-of-the-art anomaly called SERIALIZABLE anomaly (not defined by the SQL standard). It emulates the serial execution of transactions as if they were executed one after the other.

For most applications, REPEATABLE READ is sufficiently restrictive. The vast majority of applications encountered in practice use the default READ COMMITTED isolation level.

6. Best practices for modifying data

Isolation Strategies

The isolation strategy is a crucial part of application design. Getting it wrong can have a devastating impact on data consistency and the future of the organization.

Critical Warning: Unlike client code bugs, which can usually be fixed, corrupted data can be lost forever. In some areas (hospitals, vehicles, industrial machines), insulation errors can literally become a matter of life and death.

Why not always use REPEATABLE READ?

Consistency is not the only requirement. In some applications, particularly those with long transactions, a more current view of the data may be necessary. Additionally, isolation comes at a cost in terms of competition and performance.

Two isolation paradigms:

Pessimistic approach (Locking)

Assume conflicts are probable and take preventive action:

• Shared lock: Used for reads, allows other transactions to read but blocks changes.
• Exclusive lock: Used for modifications, blocks both reads and writes by other transactions.
• Advantage: requires a single copy of the data.
• Disadvantage: contention. Transactions may have to wait for locks to be released, severely limiting concurrency.

Optimistic approach (MVCC - Multiversion Concurrency Control)

Assumes conflicts will be rare. The system checks if a transaction is valid just before it commits.

• Oracle creates snapshots of data for each transaction.
• Postgres uses the write-ahead log (transaction log) to determine which version of a row should be visible for each transaction and to detect potential conflicts.
• Disadvantage: May result in wasted work (transactions canceled after work done). Maintaining multiple versions represents a significant cost in CPU, memory and disk.

Multidimensional decision:

The choice of an isolation strategy depends on:

• Business requirements
• Transaction characteristics (duration, scope)
• The Database Engine Isolation Paradigm
• Available hardware resources

Different workloads on the same database may require completely different isolation strategies.

Error handling

Error handling is another area of ​​development that receives far less attention than it deserves.

Data integrity priority:

Maintaining data integrity is the most important responsibility of any database system. A UI bug can be fixed with little consequence. A failed business process can be restarted. Data integrity errors are often irreversible.

Integrity protection mechanisms:

• Well-designed data model: normalization, correct selection of data types, respect for domains, strict type matching.
• Primary keys and candidate keys (alternate keys)
• Foreign keys for referential integrity
• CHECK Constraints
• NOT NULL constraints

These constraints must be defined at the schema level, not in client-side logic. It is much better to encounter errors during development or in QA than to discover corruption later in production.

Well-designed transactions:

According to the SQL standard, not all constraint violations automatically trigger a rollback of the entire transaction. Some constraints can be deferred until the end of the transaction (deferred constraints). These behaviors are configurable at the server, database, session, or table level.

PL/pgSQL for error handling:

Postgres separates procedural logic from declarative SQL via PL/pgSQL, a procedural language dedicated to writing functions, procedures and triggers. It adds control structures, supports complex logic, and allows explicit error handling that is difficult or impossible to express in pure SQL.

Changes to large volumes of data

Changes to large volumes of data carry much greater risks than small, targeted changes:

• Longer execution time
• Higher resource consumption (CPU, memory, disk I/O, transaction log)
• Severely limited concurrency (often scheduled during off-peak hours)
• Much higher error correction cost

Comparison of strategies for emptying a table:

Strategies to reduce impact:

1. Batching: divide the operation into small blocks and spread the work over off-peak hours.

2. *Partitioning: Postgres supports partitioning, which allows a logical table to be composed of several physical tables. Data can be loaded into a new partition and attached to the parent via a metadata-only operation, significantly reducing concurrency impact.

Testing and validation of changes

Common sense rules for any data change, whether small, medium or large:

1. Never run data modification scripts directly in production without having thoroughly tested them on a recent copy of the production database in a non-productive environment.

2. If a complete copy of production data is not possible, use a representative data set that is as close to the production data as possible.

3. Write and execute as many validation SQL queries as possible to ensure that the scripts do exactly what is expected of them.

4. Think about all potential failure points and simulate them in the test environment.

5. Always prepare a written action plan in advance, as detailed as possible, including steps for running scripts and verifying results, and don’t forget failure response plans.

6. Rehearse the plan several times in advance and measure the time required to execute it and to recover from failure.

7. Always take a full backup of the production database as close as possible to the time you begin the modification process.

8. Restore the backup to a non-productive server to verify that it can indeed be restored successfully.

These are not just coding techniques, they are fundamental principles for developing robust and reliable applications.

7. Summary of DML statements

8. References and additional resources

• Celko on SQL Identifiers and the Properties of Relational Keys (InformationWeek)
• Uniqueness, Keys, and Identity (Red Gate Simple-Talk)
• Formal critique of SQL isolation levels: A Critique of ANSI SQL Isolation Levels (ACM, 1995)

Search Terms

modify · data · sql · postgresql · databases · statement · insert · language · delete · update · merge · syntax · approach · changes · concurrency · control · demonstration · dml · dtl · inserting · isolation · levels · query · read