Skip to content

Instantly share code, notes, and snippets.

@waltton
Last active November 7, 2022 13:44
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save waltton/17cc58f5a7202b05be7704049c2b659e to your computer and use it in GitHub Desktop.
Save waltton/17cc58f5a7202b05be7704049c2b659e to your computer and use it in GitHub Desktop.
BEGIN;
UPDATE product
SET price = price * 1.1
, date_updated = NOW();
INSERT INTO product_log (product_id, price)
SELECT id, price
FROM product;
END;
-- Checking our data
SELECT substring(id::text, 1, 5) || '...' AS id, name, to_char(price, '999D99') AS price, '... ' || substring(date_added::text, 12, 8) AS date_added, '... ' || substring(date_updated::text, 12, 8) AS date_updated FROM product;
-- id | name | price | date_added | date_updated
-- ----------+----------------------+---------+--------------+--------------
-- 3da19... | feather on the stick | 13.31 | ... 16:53:59 | ... 16:54:01
-- 156a6... | mouse on the stick | 15.97 | ... 16:53:59 | ... 16:54:01
-- 4ad03... | bed cavern | 185.01 | ... 16:53:59 | ... 16:54:01
-- 1612f... | scratch toy | 38.60 | ... 16:53:59 | ... 16:54:01
-- b9803... | loud bell ball | 10.65 | ... 16:53:59 | ... 16:54:01
-- (5 rows)
SELECT substring(product_id::text from 1 for 5) || '...' AS product_id, to_char(price, '999D99') AS price, '... ' || substring(date_updated::text, 12, 8) AS date_updated FROM product_log;
-- product_id | price | date_updated
-- ------------+---------+--------------
-- 3da19... | 13.31 | ... 16:54:01
-- 156a6... | 15.97 | ... 16:54:01
-- 4ad03... | 185.01 | ... 16:54:01
-- 1612f... | 38.60 | ... 16:54:01
-- b9803... | 10.65 | ... 16:54:01
-- (5 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment