Databases
StackOverflow2013
Postsdbo
POTransferring two queries from MySQL syntax to PostgreSQL (tt-rss related)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTransferring two queries from MySQL syntax to PostgreSQL (tt-rss related)
    primarykey
    data
    text
    <p>I'm having some trouble with my tt-rss install - my DB got too big, so now purging is not working.</p> <p>I've found a manual solution, but the query is for MySQL and i'm using PostgreSQL, so it's not working.</p> <p>It's some kind of join, but my rudimentary SQL knowledge is not helping me, and after trying to fight with documentation, here i am :)</p> <p>The query in question:</p> <pre><code>DELETE from ttrss_user_entries USING ttrss_entries, ttrss_user_entries WHERE id = ref_id AND date_entered &lt; DATE_SUB(NOW(), INTERVAL 14 DAY) AND marked = false; </code></pre> <p>The second one (which i guess is deleting the leftover things in ttrss_entries):</p> <pre><code>DELETE FROM ttrss_entries WHERE (SELECT COUNT(int_id) FROM ttrss_user_entries WHERE ref_id = id) = 0; </code></pre> <p>Thank you in advance :)</p> <p>/Edit</p> <p>Sorry, haven't thought that without the structure it's hazy :D</p> <p>So, ttrss_entries looks like this:</p> <pre><code>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) ) </code></pre> <p>and ttrss_user_entries:</p> <pre><code>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 ) </code></pre> <p>I don't want to mess with the original queries above, because i'm not entirely sure in my grasp of things, and i may change something important :)</p> <p>/Edit2</p> <p>I'm having success with this query:</p> <pre><code>SELECT * FROM ttrss_user_entries JOIN ttrss_entries ON (ref_id = id) WHERE ttrss_entries.author = 'something'; </code></pre> <p>But this query is showing columns from both tables, so i'm not sure if this will delete things from both tables or not o_O'</p> <p>Also <code>ttrss_entries.date_entered &lt; DATE_SUB(NOW(), INTERVAL 14 DAY)</code> is giving me trouble: <code>ERROR: syntax error at or near "14"</code></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNarwhal
    1. USNarwhal
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTransferring two queries from MySQL syntax to PostgreSQL (tt-rss related)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you please post the table structure?
      singulars
      1. POTransferring two queries from MySQL syntax to PostgreSQL (tt-rss related)
      1. USHariKrishnan
    2. COCan you please show fully qualified field names (e.g use correlation names ("aliases") for them ) ? For instance: I cannot tell where the `id` and `ref_id` stem from, they could come from ttrss_user_entries or ttrss_entries.
      singulars
      1. POTransferring two queries from MySQL syntax to PostgreSQL (tt-rss related)
      1. USwildplasser
    3. COThe post above is updated. I've made some headway, so there's another question at the bottom :)
      singulars
      1. POTransferring two queries from MySQL syntax to PostgreSQL (tt-rss related)
      1. USNarwhal
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload