0

I've spent the last few days learning node.js and pg-promise, and I'm trying to optimize my code to be as efficient and fast as possible. I'm not sure if I'm using pg-promise correctly, but I've been reviewing these posts and guides.

Nested queries with pg-promises

get JOIN table as array of results with PostgreSQL/NodeJS

Combine nested loop queries to parent array result - pg-promise

Data Imports

select=>insert

Ok, all of that said. The problem I'm coming across right now is the UPDATE statement isn't firing correctly, and I'm pretty confident that it's because the date field isn't correct. I'm not sure how to pass it in the correct date right now. The tulind function returns an array of value in the same order and count as was input. I need to update a table with those value with an index based on ticker + date.

My ideal state would be to use the 'massive-insert' strategy from the Data Imports link But I'm not quite sure how to implement that before solving this date issue.

Anyway, thank you beforehand for any help and let me know what other details I can provide to assist!

var tulind = require('tulind')
const argv = require('yargs').argv
let run_date = argv.t
const db = require('./pg_db.js')

//step 1: get list of tickers
//step 2: get the past 100 days of close_price to fed into tulind
//step 2: generate TI values from tulind
//step 3: update values back into postgres

db.tx(t => {
    //select all eligible stocks that need to have their SMA10 value updated, could be 6k+ records.
    return t.many(`select ticker from stocks.stock_md where date(last_sma10) <= date('${run_date}') or last_sma10 is null limit 1`)
    .then(ticker_list => {
        //console.log(ticker_list)
        //select 100 past data points to feed into the tulind function for each individual stock ticker
        return t.many(`select ticker, date, close_price from stocks.all_stocks where ticker='${ticker_list[0].ticker}' order by date desc limit 50`)
        .then(eod_data => {
            let cprice = eod_data.map(item => item.close_price)
            //calculate the 10 day SMA for all stocks and update stock.all_stocks
            //tulind will return an array of values for as many data points as is fed into the function
            tulind.indicators.sma.indicator([cprice],[10], function(err, sma10_results) {
                //eod_data.push("sma10: ", sma10_results) //this definitely doesn't work
                const queries = eod_data.map(d=>t.none(`UPDATE stocks.all_stocks SET sma10='$4' WHERE ticker='$1' AND date='$2'`, eod_data))
                //console.log(eod_data)
            })
        })
        .catch(error => {
            console.log(error)
        })
    return t.batch(queries);
    })
    .catch(error => {
        console.log(error)
    })
})
.then(data => {
    // SUCCESS
})
.catch(error => {
    // ERROR
})


<table border="1"><tbody><tr><th>ticker</th><th>open_price</th><th>close_price</th><th>date</th><th>SMA5</th><th>sma10</th><th>volume</th><th>signal</th><th>high</th><th>low</th><th>macd12</th><th>macd_signal9</th><th>macd26</th><th>obv</th><th>rsi7</th><th>rsi14</th></tr><tr><td>SEB</td><td>200.0000</td><td>200.0000</td><td>2000-01-03</td><td> </td><td> </td><td>500</td><td> </td><td>200.0000</td><td>200.0000</td><td> </td><td> </td><td> </td><td>-173264</td><td> </td><td> </td></tr><tr><td>SEB</td><td>200.0000</td><td>200.0000</td><td>2000-01-04</td><td> </td><td> </td><td>1000</td><td> </td><td>200.0000</td><td>200.0000</td><td> </td><td> </td><td> </td><td>-173264</td><td> </td><td> </td></tr><tr><td>SEB</td><td>201.0000</td><td>199.0000</td><td>2000-01-05</td><td> </td><td> </td><td>300</td><td> </td><td>201.0000</td><td>199.0000</td><td> </td><td> </td><td> </td><td>-174264</td><td> </td><td> </td></tr><tr><td>SEB</td><td>197.0000</td><td>194.0000</td><td>2000-01-06</td><td> </td><td> </td><td>1200</td><td> </td><td>197.1000</td><td>194.0000</td><td> </td><td> </td><td> </td><td>-174564</td><td> </td><td> </td></tr><tr><td>SEB</td><td>194.0000</td><td>194.0000</td><td>2000-01-07</td><td>197.4000000000029</td><td> </td><td>0</td><td> </td><td>194.0000</td><td>194.0000</td><td> </td><td> </td><td> </td><td>-174564</td><td> </td><td> </td></tr><tr><td>SEB</td><td>193.3000</td><td>193.1000</td><td>2000-01-10</td><td>196.0200000000029</td><td> </td><td>300</td><td> </td><td>193.3000</td><td>193.1000</td><td> </td><td> </td><td> </td><td>-174564</td><td> </td><td> </td></tr><tr><td>SEB</td><td>193.0000</td><td>193.0000</td><td>2000-01-11</td><td>194.6200000000029</td><td> </td><td>300</td><td> </td><td>193.0000</td><td>193.0000</td><td> </td><td> </td><td> </td><td>-174864</td><td> </td><td> </td></tr><tr><td>SEB</td><td>193.0000</td><td>191.0000</td><td>2000-01-12</td><td>193.0200000000029</td><td> </td><td>500</td><td> </td><td>193.0000</td><td>188.3000</td><td> </td><td> </td><td> </td><td>-175164</td><td> </td><td> </td></tr><tr><td>SEB</td><td>192.0000</td><td>192.0000</td><td>2000-01-13</td><td>192.6200000000029</td><td> </td><td>500</td><td> </td><td>192.0000</td><td>188.0000</td><td> </td><td> </td><td> </td><td>-174664</td><td> </td><td> </td></tr><tr><td>SEB</td><td>193.0000</td><td>189.0000</td><td>2000-01-14</td><td>191.6200000000029</td><td>194.50999999998996</td><td>600</td><td> </td><td>193.0000</td><td>186.0000</td><td> </td><td> </td><td> </td><td>-175164</td><td> </td><td> </td></tr><tr><td>SEB</td><td>188.0000</td><td>188.0000</td><td>2000-01-18</td><td>190.60000000000292</td><td>193.30999999998994</td><td>100</td><td> </td><td>188.0000</td><td>188.0000</td><td> </td><td> </td><td> </td><td>-175764</td><td> </td><td> </td></tr><tr><td>SEB</td><td>187.0000</td><td>192.0000</td><td>2000-01-19</td><td>190.40000000000293</td><td>192.50999999998996</td><td>300</td><td> </td><td>192.0000</td><td>187.0000</td><td> </td><td> </td><td> </td><td>-175664</td><td> </td><td> </td></tr><tr><td>SEB</td><td>194.0000</td><td>189.0000</td><td>2000-01-20</td><td>190.00000000000293</td><td>191.50999999998996</td><td>1100</td><td> </td><td>200.0000</td><td>185.5000</td><td> </td><td> </td><td> </td><td>-175964</td><td> </td><td> </td></tr><tr><td>SEB</td><td>189.0000</td><td>189.0000</td><td>2000-01-21</td><td>189.40000000000293</td><td>191.00999999998993</td><td>300</td><td> </td><td>189.0000</td><td>185.0000</td><td> </td><td> </td><td> </td><td>-175964</td><td> </td><td> </td></tr><tr><td>SEB</td><td>191.0000</td><td>194.8000</td><td>2000-01-24</td><td>190.5600000000029</td><td>191.0899999999899</td><td>1000</td><td> </td><td>198.0000</td><td>191.0000</td><td> </td><td> </td><td> </td><td>-175664</td><td> </td><td> </td></tr></tbody></table>
CREATE TABLE stocks.stock_md (
    ticker varchar NOT NULL,
    company_name varchar NULL,
    sector varchar NULL,
    industry varchar NULL,
    daily_update varchar NULL,
    last_update date NULL,
    exchange varchar NULL,
    last_obv date NULL,
    last_sma5 date NULL,
    last_sma10 date NULL,
    last_rsi7 date NULL,
    last_rsi14 date NULL
)

CREATE TABLE stocks.all_stocks (
    ticker varchar NOT NULL,
    open_price varchar NULL,
    close_price varchar NULL,
    "date" varchar NOT NULL,
    "SMA5" varchar NULL,
    sma10 varchar NULL,
    volume varchar NULL,
    signal varchar NULL,
    high varchar NULL,
    low varchar NULL,
    macd12 varchar NULL,
    macd_signal9 varchar NULL,
    macd26 varchar NULL,
    obv varchar NULL,
    rsi7 varchar NULL,
    rsi14 varchar NULL,
    CONSTRAINT all_stocks_pk PRIMARY KEY (ticker, date)
)
Thad
  • 1
  • 3
  • You need to stop using ES6 strings injection there, and start using the query formatting supported by `pg-promise`, or else the values won't be escaped correctly. That's most likely the source of your problems. – vitaly-t Jun 11 '18 at 23:05
  • Super fast response, thank you!! I need data from two different objects, but I'm not sure how to get the formatting correct. I need the values from sma10_result then the ticker and date from eod_data. I also haven't been successful with trying to combine the two arrays together. – Thad Jun 11 '18 at 23:20
  • I updated the CONST line to this const queries = eod_data.map(d=>t.none(`UPDATE stocks.all_stocks SET sma10='$4' WHERE ticker='$1' AND date='$2'`, eod_data, sma10_results)), but now I'm getting UnhandledPromiseRejectionWarning: – Thad Jun 11 '18 at 23:44

0 Answers0