2

I want to delete records from a table that are not found in a subquery. But it's not having the desired result.

The table has multiple rows per SKU. Each price row has a valid_from and valid_to date fields. I want to erase all prices but keep those that are currently valid (where valid_to is greater or equal to current date). I want to maintain future prices (where valid_from is in the future), but one step at a time.

Here's what I tried:

delete from prices
    where prices.id <> (
        select distinct on (sku) prices.id
        from prices p2
            where
                p2.valid_to >= CURRENT_DATE
            and p2."type" = 'regular'
            and prices.id = p2.id
        order by sku, p2.valid_from desc, p2.inserted_at desc
    )

The table has around 500k rows. This subquery returns around 23k rows. I expect that my above query will delete everything but those 23k rows.

    select distinct on (sku) prices.id
    from prices
        where
            valid_to >= CURRENT_DATE
        and "type" = 'regular'
    order by sku, valid_from desc, inserted_at desc

But it's not deleting anything. Why does it not work the way I expect it to?

CREATE TABLE "public"."prices" ( 
    "id" Bigint DEFAULT nextval('prices_id_seq'::regclass) NOT NULL,
    "type" Character Varying( 255 ) NOT NULL,
    "unit_price" Numeric( 11, 3 ) NOT NULL,
    "wholesale_price" Numeric( 11, 3 ),
    "min_wholesale_quantity" Numeric( 11, 3 ),
    "valid_from" Date NOT NULL,
    "valid_to" Date NOT NULL,
    "product_cost" Numeric( 11, 3 ) NOT NULL,
    "tax_rate" Numeric( 11, 3 ) NOT NULL,
    "store_product_id" Bigint,
    "inserted_at" Timestamp Without Time Zone NOT NULL,
    "updated_at" Timestamp Without Time Zone NOT NULL,
    "sku" Character Varying( 2044 ),
    PRIMARY KEY ( "id" ) );

CREATE INDEX "prices_store_product_id_index" ON "public"."prices" USING btree( "store_product_id" Asc NULLS Last );

CREATE INDEX "index_sku" ON "public"."prices" USING btree( "sku" Asc NULLS Last );

ALTER TABLE "public"."prices"
    ADD CONSTRAINT "prices_store_product_id_fkey" FOREIGN KEY ( "store_product_id" )
    REFERENCES "public"."store_products" ( "id" ) MATCH SIMPLE
    ON DELETE Cascade
    ON UPDATE No Action;
  • 2
    Inside the subquery you require " and prices.id = p2.id", outside you specify "<>". An id can't be equal and different at the same time. – Gerard H. Pille Jul 5 '18 at 22:32
  • Is prices.id not unique? – Gerard H. Pille Jul 5 '18 at 23:03
  • 1
    Please Edit your question and add the CREATE TABLE statements for the tables in question adding the desired output. Those answering need a Minimal, Complete, and Verifiable example. Use formatted text please, no screen shots. – Evan Carroll Jul 5 '18 at 23:07
  • @GerardH.Pille each sku can have multiple prices, where valid_from and valid_to is in the past or future. I want to erase all prices and only leave those currently valid. I know my query will also erase future prices (those where valid_from is in the future), but one step at a time. – Mohamad Jul 5 '18 at 23:12
  • @EvanCarroll done. – Mohamad Jul 5 '18 at 23:12
0

Use the "not in", and don't refer to the outer query:

delete from prices
where (sku,id) Not in (
    select p2.sku, p2.id
    from prices p2
        where p2.valid_to >= CURRENT_DATE
        and p2."type" = 'regular'
)
  • You're query does not include the distinct on clause. I'll try it, but I suspect it might work as intended. – Mohamad Jul 5 '18 at 22:43
  • I tried it and it is taking much too long. Around 5 minutes has passed and it's still running. – Mohamad Jul 5 '18 at 22:48
  • I'll add the sku, but the query may take a couple of days. Also, the "prices.id" in the subselect was an error. – Gerard H. Pille Jul 5 '18 at 22:52
  • You should really almost never use NOT IN. In most senses, it's inferior to NOT EXISTS – Evan Carroll Jul 5 '18 at 22:54
  • @EvanCarroll postgresql is wise enough to handle this the best way – Gerard H. Pille Jul 5 '18 at 22:56
0

Based on your comment "I want to erase all prices and only leave those currently valid. I know my query will also erase future prices (those where valid_from is in the future), but one step at a time", here are two steps, erase only old prices:

delete from prices
  where p2.valid_to < CURRENT_DATE
     or p2."type" != 'regular'

If this is too much for a single transaction, add a test on the number of rows (limit, rownum < ...) and repeat until all old prices are gone.

  • That won’t work either. I think sample data would help. When I get to the office I will add some. But many prices are valid until 01-01-9999. So an SKU can have multiple valid prices separated only by their ‘valid_from’. – Mohamad Jul 6 '18 at 10:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.