Remove duplicate data leaving only the most recent record – Postgres

Remove duplicate data leaving only the most recent record – Postgres

Problem Description:

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:

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 – 1

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