Using WITH + DELETE clause in a single query in postgresql
<p>I have the following table structure, for a table named <code>listens</code> with PRIMARYKEY on (uid,timestamp)</p>

<pre><code>     Column     |            Type             |                      Modifiers                       
----------------+-----------------------------+------------------------------------------------------    
 id             | integer                     | not null default nextval('listens_id_seq'::regclass)
 uid            | character varying           | not null
 date           | timestamp without time zone | 
 timestamp      | integer                     | not null
 artist_msid    | uuid                        | 
 album_msid     | uuid                        | 
 recording_msid | uuid                        | 
 json           | character varying           | 
</code></pre>

<p>I need to remove all the entries for a particular user <strong>(uid)</strong> which are older than the max timestamp, say max is 123456789 (in seconds) and delta is 100000, then, all records older than <strong>max-100000</strong>.</p>

<p>I have managed to create a query when the table contains a single user but i am unable to formulate it to work for every user in the database. This operation needs to be done for every user in the database.</p>

<pre><code>WITH max_table as (
    SELECT max(timestamp) - 10000 as max 
    FROM listens 
    GROUP BY uid) 
DELETE FROM listens 
WHERE timestamp &lt; (SELECT max FROM max_table);
</code></pre>

<p>Any solutions?</p>