Insert an SQL Row If Does Not Already Exist

Question

I am currently using the code:

INSERT INTO tradecounts 
SELECT symbol, tstamp, COUNT(*) AS trades 
FROM (SELECT d.* FROM janw1 d ORDER BY entry_date ASC LIMIT 200000) d 
GROUP BY tstamp, symbol HAVING COUNT(*) > 50

Which is working as intended however I would like to use python to run this statement every n number of seconds and when it does that it appends duplicate records so I need to put a WHERE NOT EXISTS statement in but I am having trouble with the syntax

insert INTO tradecounts 
SELECT symbol, tstamp, COUNT(*) AS trades 
FROM (SELECT d.* FROM janw1 d ORDER BY entry_date ASC LIMIT 200000) d 
WHERE NOT EXISTS 
GROUP BY tstamp, symbol HAVING COUNT(*) > 50

Any suggestions as to where I am going wrong would be much appreciated. Thank you!

create a unique index on symbol, tstamp then use insert .. on conflict (..) do nothing — user330315
– user330315, Commented Jan 4, 2021 at 11:08
Do you really want ascending on entry_date? That seems counterintuitive? — Gordon Linoff
– Gordon Linoff, Commented Jan 4, 2021 at 12:09

PSK · Accepted Answer · 2021-01-04 11:04:30Z

1

Following query should work.

INSERT INTO tradecounts 
SELECT symbol, tstamp, COUNT(*) AS trades 
FROM (SELECT d.* FROM janw1 d ORDER BY entry_date ASC LIMIT 200000) d 
WHERE NOT EXISTS(
SELECT 1 FROM tradecounts tc where tc.symbol=d.symbol and tc.tstamp=d.tstamp
)
GROUP BY tstamp, symbol HAVING COUNT(*) > 50

answered Jan 4, 2021 at 11:04

PSK

18k5 gold badges35 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2021-01-04 12:05:17Z

Use on conflict. First define a unique index or constraint on the first two columns;

create unique index unq_symbol_tstamp on tradecounts(symbol, tstamp);

insert INTO tradecounts (symbol, tstamp, trades)
    SELECT symbol, tstamp, COUNT(*) AS trades 
    FROM (SELECT d.*
          FROM janw1 d
          ORDER BY entry_date ASC
          LIMIT 200000
         ) d 
    GROUP BY tstamp, symbol
    HAVING COUNT(*) > 50
    ON CONFLICT (symbol, tstamp) DO
        UPDATE SET trades = excluded.trades;

This also has the advantage that is will update any timestamps where the count has changed.

Actually, you want to be a little careful to be sure you have full timestamps. That suggests:

create unique index unq_symbol_tstamp on tradecounts(symbol, tstamp);

insert INTO tradecounts (symbol, tstamp, trades)
    SELECT symbol, tstamp, COUNT(*) AS trades 
    FROM (SELECT d.*, MIN(tstamp) OVER () as min_tstamp
          FROM (SELECT d.*
                FROM janw1 d
                ORDER BY entry_date DESC, tstamp DESC
                LIMIT 200000
              ) d 
         ) d 
    WHERE tstamp > min_tstamp
    GROUP BY tstamp, symbol
    HAVING COUNT(*) > 50
    ON CONFLICT (symbol, tstamp) DO
        UPDATE SET trades = excluded.trades;

Collectives™ on Stack Overflow

Insert an SQL Row If Does Not Already Exist

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related