← back to Sanderson Onboard

scripts/load_trade.sql

17 lines

\set ON_ERROR_STOP on
BEGIN;
CREATE TABLE IF NOT EXISTS sanderson_trade_catalog (
  id serial PRIMARY KEY, mfr_code varchar UNIQUE, pattern_name varchar, product_type varchar,
  status varchar, trade_usd numeric, ssp_usd numeric, unit varchar, dw_retail_usd numeric,
  publishable boolean, source varchar DEFAULT 'trade.sandersondesigngroup.com', harvested_at timestamp DEFAULT now());
CREATE TEMP TABLE tload(mfr_code varchar,pattern_name varchar,product_type varchar,status varchar,trade_usd numeric,ssp_usd numeric,unit varchar);
\copy tload FROM 'trade_rows.csv' WITH (FORMAT csv, HEADER true)
INSERT INTO sanderson_trade_catalog(mfr_code,pattern_name,product_type,status,trade_usd,ssp_usd,unit,dw_retail_usd,publishable,harvested_at)
SELECT mfr_code,pattern_name,product_type,status,trade_usd,ssp_usd,unit,
       round((trade_usd/0.65/0.85)::numeric,0), (status='Live'), now()
FROM tload WHERE mfr_code IS NOT NULL AND mfr_code<>''
ON CONFLICT (mfr_code) DO UPDATE SET pattern_name=EXCLUDED.pattern_name,status=EXCLUDED.status,
  trade_usd=EXCLUDED.trade_usd,ssp_usd=EXCLUDED.ssp_usd,unit=EXCLUDED.unit,
  dw_retail_usd=EXCLUDED.dw_retail_usd,publishable=EXCLUDED.publishable,harvested_at=now();
COMMIT;