postgresqlamazon-aurora

Remove duplicate data leaving only the most recent record - Postgres


I have a question where I have a table in my postgres db where it has several columns, but I need to check two columns and delete only the oldest record

Example:

id_insert id_store id_client XXX ZZZ YYY inserted_at
23 3222 3331 aaaa ddsdad dsdaad 2022-11-28 19:53:34.898
56 3222 3331 aaaa rarsad dsadaw 2022-11-29 23:33:16.593

in the case of the table above, I need to create a query that deletes the line "23" where the id_store and id_client are the same, but I need to make the date inserted_at newer

I tried to use a query that I used in sql server, but it is not working in postgres

with cte as (select id, cliente_id,max(inserted_at) max_data
                        from public.tray_orders
                        group by id, cliente_id)
                        delete t
                        from public.tray_orders t
                        inner join cte
                        on  (t.id = cte.id)
                        and (t.inserted_at <> cte.max_data)
                        

Error:

SQL Error [42601]: ERROR: syntax error at or near "t"
  Position: 193

Solution

  • CREATE temp TABLE tray_orders (
        id_insert bigint,
        id_store bigint,
        id_client bigint,
        XXX text,
        ZZZ text,
        YYY text,
        inserted_at timestamptz
    );
    
    INSERT INTO tray_orders
        VALUES (23, 3222, 3331, 'aaaa', 'ddsdad', 'dsdaad', '2022-11-28 19:53:34.898'),
        (56, 3222, 3331, 'aaaa', 'rarsad', 'dsadaw', '2022-11-29 23:33:16.593');
    

    delete record that id_insert = 23

     BEGIN;
        WITH cte AS (
            SELECT
                id_store,
                id_client,
                max(inserted_at) AS max_data
            FROM
                tray_orders
            GROUP BY
                1,
                2)
        DELETE FROM tray_orders t USING cte
        WHERE t.id_store = cte.id_store
            AND t.inserted_at <> max_data
        RETURNING
            *;
        
        TABLE tray_orders;
        
        ROLLBACK;
    

    manual(https://www.postgresql.org/docs/current/sql-delete.html):

    DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
        [ USING from_item [, ...] ]
    

    So you can not use

    delete t from public.tray_orders t