删除重复数据，只留下最近的记录- Postgres

Question

我有一个问题，我有一个表在我的postgres数据库中，它有几个列，但我需要检查两列，只删除最旧的记录

的例子:

<表类> id_insert id_store id_client XXX 打鼾声 YYY inserted_at tbody><<tr>2332223331aaaaddsdaddsdaad2022-11-28 19:53:34.8985632223331aaaararsaddsadaw2022-11-29 23:33:16.593

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');

删除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;

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

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

所以你不能用

delete t from public.tray_orders t