PostgreSQL join: delete records present in one table, but not another

I have a Drupal website embedding a Flash game.

The registered website users are listed in the drupal_users table - here the list of those, registered over a week ago:

# select uid, created from drupal_users where 
      to_timestamp(created) < (now() - interval '7 days') limit 5;
 uid  |  created
------+------------
 9903 | 1300257067
 9904 | 1300259929
 9750 | 1299858284
 9751 | 1299858603
 8083 | 1285514989
(5 rows)

The Flash game users are listed in another table - the pref_users and have "DE" string prepended to their id:

# select id from pref_users where id like 'DE%' limit 5;
   id
--------
 DE9054
 DE9055
 DE9056
 DE9057
 DE9058
(5 rows)

I would like to get rid of the (probably SPAM robots) users who registered at my website over a week ago, but still haven't played the Flash game. I.e. I'd like to delete drupal_users records, which are not present in the pref_users table.

At the same time I'd prefer not to do something like:

# delete from drupal_users where 
    to_timestamp(created) < (now() - interval '7 days') and
    'DE'||uid not in (select id from pref_users where id like 'DE%');

because I'm not sure, how big the select statement above is allowed to be (maybe there is a limit? I'm using PostgreSQL 8.4.7 and CentOS 5.5/64 bit. Before Drupal7 I was using phpBB3 and sometimes I had seen such kind of SQL-statements fail while deleting old forum posts from phpBB3 admin console).

So my question is, if the statement above can be rewritten as some kind of SQL-join?

Solution

Rewriting a delete as a SQL join is not possible, AFAIK. But why don't you like

delete from drupal_users where 
to_timestamp(created) < (now() - interval '7 days') and
'DE'||uid not in (select id from pref_users where id like 'DE%');

The size of this statement is static (you don't generate any dynamic SQL here), so this is a perfectly valid approach, and should run pretty fast (if that's what you are concerned about).