StackOverflow Pages I Frequent

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

https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync

SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

 

PostgreSQL rounding

(Seriously, why is this so hard for me?)

https://www.postgresql.org/message-id/opsmwld9knawcxfg%40adi

Screen Shot 2018-08-25 at 11.35.50 PM.png

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;

Updating Triggers

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

from: stackoverflow

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s