CODEWITHSUNDEEP
sqlpostgresql
c0lton
c0lton

Reputation: 61

Insert an SQL Row If Does Not Already Exist

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!

Upvotes: 0

Views: 97

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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;

Upvotes: 0

PSK
PSK

Reputation: 17943

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

Upvotes: 1

Related Questions