Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-pro· GoogleHugging Face LMArena Human Preference 140K

Can this PostgreSQL code: WITH moved_rows AS ( DELETE FROM products WHERE "date" >=…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Geminigemini-2.5-flash’s answer.
  1. User

    Can this PostgreSQL code:

    WITH moved_rows AS ( DELETE FROM products WHERE "date" >= '2010-10-01' AND "date" < '2010-11-01' RETURNING * ) INSERT INTO products_log SELECT * FROM moved_rows;

    ...be implemented like this on Oracle (assuming id is the PK column):

    declare v_cnt number; begin for rec in ( SELECT * FROM products WHERE "date" >= '2010-10-01' AND "date" < '2010-11-01' ) loop delete from products where id = rec.id; v_cnt := SQL%ROWCOUNT; if v_cnt > 0 then insert into products_log values rec; end if; end loop; end;

    Any alternative solutions?

  2. Gemini Assistant

    Excellent question. This is a very common pattern: archiving or logging data before deleting it.

    Let's break down your question.

    Analysis of Your Proposed Oracle Solution

    First, to answer your direct question: Yes, the PL/SQL loop you've written can implement the logic. It will iterate through the target rows, delete them one by one, and insert them into the log table.

    However, it is highly discouraged for several critical reasons, making it a poor translation of the efficient PostgreSQL statement.

    1. Performance: This is the biggest drawback. The approach is "row-by-row" (sometimes called "slow-by-slow"). For every single row you want to move, you perform:

      • A cursor fetch (from the PL/SQL engine).
      • A DELETE statement (a context switch to the SQL engine).
      • An INSERT statement (another context switch to the SQL engine). If you are moving 1 million rows, this results in over 2 million context switches, which is incredibly inefficient compared to a single set-based operation. The PostgreSQL code executes as one atomic, set-based operation.
    2. Concurrency / Data Integrity: This is a more subtle but dangerous flaw. The SELECT statement in your cursor (for rec in (SELECT * ...) establishes a read-consistent view of the data at the moment it starts. Consider this race condition:

      • Time 1: Your PL/SQL block opens the cursor and has a list of rows to process.
      • Time 2: Another session DELETEs one of those rows and commits.
      • Time 3: Your loop gets to that row. It fetches the data as it existed at Time 1.
      • Time 4: Your delete from products where id = rec.id statement runs. It finds nothing to delete, so SQL%ROWCOUNT is 0.
      • Time 5: Your if v_cnt > 0 condition fails, and the INSERT into the log is skipped.
      • Result: A row was deleted from the products table but was never logged, leading to data loss.
    3. Verbosity and Complexity: The PL/SQL block is much more code to write, read, and maintain than a single, declarative SQL statement.


    Alternative Solutions in Oracle

    Here are much better ways to achieve this in Oracle, from best to worst.

    Alternative 1: DELETE ... RETURNING BULK COLLECT (The Best Practice)

    This is the most direct and performant equivalent to the PostgreSQL DELETE ... RETURNING clause. It performs the delete as a single set-based operation and efficiently captures the deleted rows into a PL/SQL collection, which can then be inserted in a single bulk operation.

    This method is fast, efficient, and transactionally safe, avoiding the race condition described above because the DELETE and RETURNING happen in one atomic step.

    -- First, ensure you have a PL/SQL type that matches the table structure.
    -- You can define it manually or, more easily, use %ROWTYPE.
    DECLARE
        -- Define a collection type based on the table's row structure.
        TYPE t_products_tab IS TABLE OF products%ROWTYPE;
        
        -- Declare a variable of that collection type.
        v_deleted_rows t_products_tab;
    BEGIN
        -- Delete the rows in a single SQL statement, and return all columns
        -- of the deleted rows into our collection in one go.
        DELETE FROM products
        WHERE
            "date" >= DATE '2010-10-01' AND
            "date" < DATE '2010-11-01'
        RETURNING id, name, "date", price -- list all columns explicitly
        BULK COLLECT INTO v_deleted_rows;
    
        -- Now, bulk-insert the entire collection of deleted rows
        -- into the log table in a single, highly efficient operation.
        FORALL i IN 1 .. v_deleted_rows.COUNT
            INSERT INTO products_log VALUES v_deleted_rows(i);
            
        -- Optionally, print how many rows were moved.
        DBMS_OUTPUT.PUT_LINE('Moved ' || SQL%ROWCOUNT || ' rows.');
    END;
    /
    

    Note: Using DATE '2010-10-01' is the standard, unambiguous way to write a date literal in SQL.

    Alternative 2: Two-Step SQL Statements

    This approach uses two simple, set-based SQL statements. It is much better than the row-by-row loop but has a potential concurrency issue, though different from the one in your PL/SQL loop.

    -- Step 1: Copy the rows you are about to delete into the log table.
    INSERT INTO products_log (id, name, "date", price)
    SELECT id, name, "date", price
    FROM products
    WHERE
        "date" >= DATE '2010-10-01' AND
        "date" < DATE '2010-11-01';
    
    -- Step 2: Delete those same rows from the original table.
    DELETE FROM products
    WHERE
        "date" >= DATE '2010-10-01' AND
        "date" < DATE '2010-11-01';
    
    -- Important: Both statements must be in the same transaction.
    -- A COMMIT should only happen after both succeed.
    COMMIT;
    
    • Caveat: This method is also subject to a race condition. If a new row matching the WHERE clause is inserted by another session after the INSERT (Step 1) but before the DELETE (Step 2), that new row will be deleted without ever being logged. This is generally only safe if you can guarantee no concurrent modifications or if you first lock the table (LOCK TABLE products IN EXCLUSIVE MODE;), which can severely impact concurrency.

    Alternative 3: Using a Trigger (Architectural Approach)

    You could also solve this by creating a trigger that automatically logs any deleted row.

    CREATE OR REPLACE TRIGGER trg_log_deleted_products
    AFTER DELETE ON products
    FOR EACH ROW
    BEGIN
        -- :OLD contains the values of the row that was just deleted.
        INSERT INTO products_log (id, name, "date", price)
        VALUES (:OLD.id, :OLD.name, :OLD."date", :OLD.price);
    END;
    /
    

    With this trigger in place, your original operation becomes just a simple DELETE statement:

    DELETE FROM products
    WHERE
        "date" >= DATE '2010-10-01' AND
        "date" < DATE '2010-11-01';
    COMMIT;
    
    • Pros: The logic is automatic and enforced for all deletes, preventing mistakes.
    • Cons: It adds overhead to every single delete operation on the products table, whether you want it logged or not. It also makes the logic less explicit; someone reading the DELETE statement may not be aware that a logging operation is happening in the background.

    Summary and Recommendation

    MethodPerformanceConcurrency SafetySimplicityRecommendation
    PostgreSQL CTEExcellentExcellentExcellentN/A (Source)
    Your PL/SQL LoopVery PoorPoor (Prone to data loss)PoorDo Not Use
    DELETE RETURNING BULK COLLECTExcellentExcellentGoodHighly Recommended
    Two-Step SQLGoodPoor (Prone to data loss)ExcellentUse with caution, only in controlled environments
    AFTER DELETE TriggerGoodExcellentGoodRecommended if logging is a permanent business rule

    Conclusion: The best and most direct translation of your PostgreSQL code into Oracle is Alternative 1: DELETE ... RETURNING BULK COLLECT. It matches the performance, efficiency, and transactional safety of the original.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.