A collection of notes to help keep the ball rolling throughout the Sisyphean task of writing code.
[Perpetually updated unless I get lazy]
Resetting serial sequence PostgreSQL
SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;
(Seriously, why is this so hard for me?)
FWIW I always read the parameters^ and expect this to work:
select round(price, 4) from price_data;
But it doesn’t because because price is double precision. Fix with this:
select round(price::numeric, 4) from price_data;
With Clause Syntax:
Alias goes before the query expression that is referenced (which is different than the syntax of a subquery).
with price_data_alias as ( select round(price::numeric, 4) from price_data) select distinct price_data_alias.price, other_table.column from price_data_alias inner join other_table on id;
You can’t “create or replace” or “update” or “alter” triggers, so this is the next best thing. Note: must also specify all of the tables
BEGIN; DROP TRIGGER IF EXISTS trig on tab2; CREATE TRIGGER trig AFTER INSERT OR UPDATE OR DELETE ON tab2 FOR EACH ROW EXECUTE PROCEDURE proc3(); COMMIT;