agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Richard Klingler <richard@klingler.net>
To: pgsql-sql@lists.postgresql.org
Subject: Clean up shop database
Date: Wed, 19 Jan 2022 12:03:17 +0100
Message-ID: <20220119120317479683.54287f97@klingler.net> (raw)

Good morning (o;


I am in the process of migrating an online shop to another system and 
therefore
also want to clean out products that haven't been re-stocked for a time.

Now this simple query returns all order ids younger than 750 days:

select orderid, orderdate from orders
where (now() - orderdate) < INTERVAL '1000 days'
order by orderdate asc

So it shows me orders beginning from January 1st 2020...all fine.


Now I want to list all products which stock is 0 and have only been 
ordered
before those 750 days..so I use the above query in wrap it in the select
with a "not in":

select p.productid as id, p.name_de as name
from product p, orderitems i, orders
where p.productid = i.orderitems2productid
and i.orderitems2orderid not in (select orderid from orders where 
(now() - orderdate) < INTERVAL '750 days')
and p.pieces < 1
and p.active = 't'
group by id
order by id desc


Besides that this query takes over 70 seconds...it also returns 
products that have been ordered after January 1st 2020.

So somehow this "not in" doesn't work as I am expecting it (o;


thanks in advance
richard

view thread (12+ messages)  latest in thread

Message-ID: <20220119120317479683.54287f97@klingler.net>
Permalink:  ../20220119120317479683.54287f97@klingler.net/
Also on:    postgresql.org/message-id/20220119120317479683.54287f97@klingler.net

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: richard@klingler.net, pgsql-sql@lists.postgresql.org
  Subject: Re: Clean up shop database
  In-Reply-To: <20220119120317479683.54287f97@klingler.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox