将两个查询从MySQL语法传输到PostgreSQL (与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;

(预先谢谢:)

/Edit

对不起，我还没有想过，如果没有这个结构，它是模糊的。

所以，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
)

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

/Edit2

我在这个查询上很成功：

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