`scripts/load.sql`

29 lines
\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE sload (
  mfr_sku varchar, pattern_name varchar, color_name varchar, collection varchar,
  image_url text, product_url text, product_type varchar, price_gbp numeric,
  currency varchar, settlement_flag varchar, gallery_images text[]
);
\copy sload FROM 'rows.csv' WITH (FORMAT csv, HEADER true)

INSERT INTO sanderson_catalog
  (mfr_sku, pattern_name, color_name, collection, image_url, product_url,
   product_type, price_gbp, currency, settlement_flag, gallery_images, crawled_at, updated_at)
SELECT mfr_sku, pattern_name, color_name, collection, image_url, product_url,
       product_type, price_gbp, currency, settlement_flag, gallery_images, now(), now()
FROM sload
WHERE mfr_sku IS NOT NULL AND mfr_sku <> ''
ON CONFLICT (mfr_sku) DO UPDATE SET
  pattern_name = EXCLUDED.pattern_name,
  color_name   = EXCLUDED.color_name,
  collection   = EXCLUDED.collection,
  image_url    = EXCLUDED.image_url,
  product_url  = EXCLUDED.product_url,
  price_gbp    = EXCLUDED.price_gbp,
  currency     = EXCLUDED.currency,
  settlement_flag = EXCLUDED.settlement_flag,
  gallery_images  = EXCLUDED.gallery_images,
  updated_at   = now();
COMMIT;