mysql - 将两个查询从 MySQL 语法转移到 PostgreSQL(tt-rss 相关)

Question

我在安装 tt-rss 时遇到了一些问题 - 我的数据库太大了，所以现在清除不起作用。

我找到了一个手动解决方案，但查询是针对 MySQL 的，而我使用的是 PostgreSQL，所以它不起作用。

这是某种连接，但我的基本 SQL 知识对我没有帮助，在尝试与文档作斗争之后，我在这里 :)

有问题的查询:

DELETE from ttrss_user_entries USING ttrss_entries, ttrss_user_entries 
WHERE id = ref_id 
  AND date_entered < DATE_SUB(NOW(), INTERVAL 14 DAY) 
  AND marked = false;

第二个(我猜是删除 ttrss_entries 中剩余的东西):

DELETE FROM ttrss_entries 
WHERE (SELECT COUNT(int_id) 
       FROM ttrss_user_entries 
       WHERE ref_id = id) = 0;

提前谢谢你:)

/编辑

抱歉，没有想到没有结构它会很模糊 :D

所以，ttrss_entries 看起来像这样:

CREATE TABLE ttrss_entries
(
  id serial NOT NULL,
  title text NOT NULL,
  guid text NOT NULL,
  link text NOT NULL,
  updated timestamp without time zone NOT NULL,
  content text NOT NULL,
  content_hash character varying(250) NOT NULL,
  cached_content text,
  no_orig_date boolean NOT NULL DEFAULT false,
  date_entered timestamp without time zone NOT NULL,
  date_updated timestamp without time zone NOT NULL,
  num_comments integer NOT NULL DEFAULT 0,
  comments character varying(250) NOT NULL DEFAULT ''::character varying,
  plugin_data text,
  author character varying(250) NOT NULL DEFAULT ''::character varying,
  CONSTRAINT ttrss_entries_pkey PRIMARY KEY (id),
  CONSTRAINT ttrss_entries_guid_key UNIQUE (guid)
)

和 ttrss_user_entries:

CREATE TABLE ttrss_user_entries
(
  int_id serial NOT NULL,
  ref_id integer NOT NULL,
  uuid character varying(200) NOT NULL,
  feed_id integer,
  orig_feed_id integer,
  owner_uid integer NOT NULL,
  marked boolean NOT NULL DEFAULT false,
  published boolean NOT NULL DEFAULT false,
  tag_cache text NOT NULL,
  label_cache text NOT NULL,
  last_read timestamp without time zone,
  score integer NOT NULL DEFAULT 0,
  last_marked timestamp without time zone,
  last_published timestamp without time zone,
  note text,
  unread boolean NOT NULL DEFAULT true,
  CONSTRAINT ttrss_user_entries_pkey PRIMARY KEY (int_id),
  CONSTRAINT ttrss_user_entries_feed_id_fkey FOREIGN KEY (feed_id)
      REFERENCES ttrss_feeds (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT ttrss_user_entries_orig_feed_id_fkey FOREIGN KEY (orig_feed_id)
      REFERENCES ttrss_archived_feeds (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT ttrss_user_entries_owner_uid_fkey FOREIGN KEY (owner_uid)
      REFERENCES ttrss_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT ttrss_user_entries_ref_id_fkey FOREIGN KEY (ref_id)
      REFERENCES ttrss_entries (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)

我不想弄乱上面的原始查询，因为我不完全确定我对事情的掌握，我可能会改变一些重要的东西:)

/编辑2

我在这个查询上取得了成功:

SELECT * FROM ttrss_user_entries
JOIN ttrss_entries ON (ref_id = id)
WHERE ttrss_entries.author = 'something';

但是这个查询显示了两个表中的列，所以我不确定这是否会从两个表中删除内容o_O'

还有 ttrss_entries.date_entered < DATE_SUB(NOW(), INTERVAL 14 DAY)给我带来麻烦:ERROR: syntax error at or near "14"

Answer 1

这是第一个删除查询:

DELETE FROM ttrss_user_entries dd
WHERE dd.marked = false
AND EXISTS (
     SELECT *
       FROM ttrss_entries ex
      WHERE ex.id = dd.ref_id
        AND ex.date_entered < now() - '14 day'::interval
      );

这是第二次删除:

    -- remove unreferenced rows from _entries
DELETE FROM ttrss_entries dd
WHERE NOT EXISTS (
       SELECT *
       FROM ttrss_user_entries nx
       WHERE nx.ref_id = dd.id
        );

更新:posgres 还支持(非标准的)USING 语法，它基本上是一个JOIN。它几乎类似于 mysql 语法；只需将逗号替换为 USING，并删除目标表的双重条目。这是第一次删除:

DELETE FROM ttrss_user_entries dd
USING ttrss_entries ex
WHERE ex.id = dd.ref_id
AND dd.marked = false
AND ex.date_entered < now() - '14 day'::interval
      ;

最终查询:

SELECT tue.* FROM ttrss_user_entries tue
JOIN ttrss_entries te ON tue.ref_id = te.id
WHERE te.author = 'something'
  ;

或者(更清楚一点，避免列出两次(如果 1:N 是相反的)):

SELECT *
FROM ttrss_user_entries tue
WHERE EXISTS (
  SELECT *
  FROM ttrss_entries te
  WHERE te.id = tue.ref_id
  AND te.author = 'something'
  ;