Thread: deleting records is failing

This question is probably real easy to answer, but I've got myself into a
vicious loop and can't see the forest through the trees.

I have a set of records spanning several dates, and I want to delete some
records within a specific date range.  For example, I have a record for each
day from Sept 1 through Nov 1, and I want to delete the records for Sept 21
through 10/20 and leave all the rest.

Here is my query that's deleting all the records for the episode
DELETE FROM tracker WHERE tracker.episode_id = 238 AND
( rug_tracker.date_of_service < '09/12/2005'
OR rug_tracker.date_of_service > '10/20/2005' );

I would really appreciate someone showing me the way through the forest.

Thank you,

Tim

On Dec 8, 2005, at 12:13 , Tim Vadnais wrote:

> I have a set of records spanning several dates, and I want to
> delete some
> records within a specific date range.  For example, I have a record
> for each
> day from Sept 1 through Nov 1, and I want to delete the records for
> Sept 21
> through 10/20 and leave all the rest.
>
> Here is my query that's deleting all the records for the episode
> DELETE FROM tracker WHERE tracker.episode_id = 238 AND
> ( rug_tracker.date_of_service < '09/12/2005'
> OR rug_tracker.date_of_service > '10/20/2005' );

DELETE FROM tracker
WHERE episode_id = 238
AND date_of_service BETWEEN '2005-09-21'::date and '2005-10-20'::date;

Note that between is inclusive, so if you don't want Sept 21 (or is
it Sept 12? you've used one in your explanation and another in your
SQL) and Oct 20, you can use BETWEEN '2005-09-22' AND '2005-10-19'
instead.

Michael Glaesemann
grzm myrealbox com

Thank you.

I've modified the code to read:

DELETE from tracker WHERE tracker.episode_id = 277 AND
tracker.date_of_service BETWEEN '09/12/2005'::date - interval '1 day' AND
'10/20/2005'::date + interval '1 day'

And got:

SELECT * from tracker where tracker.episode_id = 277;
53716 |  277 |     27 | 10/27/2005
53717 |  277 |     28 | 10/28/2005
53720 |  277 |     29 | 11/05/2005
53721 |  277 |     30 | 11/06/2005

Which suggest to me that it's a true between (exclusive).

I really appreciate your help.

<<Tim makes note of *really* useful BETWEEN conditional.>>

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Michael Glaesemann
Sent: Wednesday, December 07, 2005 7:21 PM
To: Tim Vadnais
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] deleting records is failing


On Dec 8, 2005, at 12:13 , Tim Vadnais wrote:

> I have a set of records spanning several dates, and I want to
> delete some
> records within a specific date range.  For example, I have a record
> for each
> day from Sept 1 through Nov 1, and I want to delete the records for
> Sept 21
> through 10/20 and leave all the rest.
>
> Here is my query that's deleting all the records for the episode
> DELETE FROM tracker WHERE tracker.episode_id = 238 AND
> ( rug_tracker.date_of_service < '09/12/2005'
> OR rug_tracker.date_of_service > '10/20/2005' );

DELETE FROM tracker
WHERE episode_id = 238
AND date_of_service BETWEEN '2005-09-21'::date and '2005-10-20'::date;

Note that between is inclusive, so if you don't want Sept 21 (or is
it Sept 12? you've used one in your explanation and another in your
SQL) and Oct 20, you can use BETWEEN '2005-09-22' AND '2005-10-19'
instead.

Michael Glaesemann
grzm myrealbox com




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Dec 8, 2005, at 13:01 , Tim Vadnais wrote:

> Thank you.
>
> I've modified the code to read:
>
> DELETE from tracker WHERE tracker.episode_id = 277 AND
> tracker.date_of_service BETWEEN '09/12/2005'::date - interval '1
> day' AND
> '10/20/2005'::date + interval '1 day'

If you're going to be doing a lot of this, you might want to wrap it
in a simple SQL function, such as :

create or replace function oo_between (
    date -- date to check
    , date -- start date of interval
    , date -- end date of interval
    ) returns boolean
strict
immutable
language sql as $$
    select $1 > $2 and $1 < $3;
$$;

Untested, and check the direction of the inequalities. I always seem
to screw that up. I chose the oo_ prefix because this is sometimes
called an open-open interval. BETWEEN on the other hand tests a
closed-closed interval.

You can call it like this:

DELETE FROM tracker
WHERE episode_id = 227
AND oo_between(date_of_service, '2005-09-12'::date, '2005-10-20'::date);

Michael Glaesemann
grzm myrealbox com