This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--set session_replication_role to replica; | |
INSERT INTO regions VALUES | |
( 1 | |
, 'Europe' | |
); | |
INSERT INTO regions VALUES | |
( 2 | |
, 'Americas' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE regions | |
( region_id NUMERIC | |
CONSTRAINT region_id_nn NOT NULL | |
, region_name VARCHAR(25) | |
); | |
ALTER TABLE regions | |
ADD CONSTRAINT reg_id_pk | |
PRIMARY KEY (region_id) | |
; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE alpha ( | |
s text | |
); | |
insert into alpha select 'Volk'; | |
insert into alpha select '(Weiter) A'; | |
insert into alpha select '(Weiter) B'; | |
insert into alpha select 'WISO'; | |
insert into alpha select 'Zahn'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select | |
test, | |
query::char(48), | |
instance, | |
--scale, | |
--clients, | |
round(mean_time::numeric, 4) mean_time, | |
round(stddev_time::numeric, 4) stddev_time, | |
round(((mean_time - mean_time_lag)::numeric / mean_time::numeric )*100, 1) as mean_time_diff, | |
round(((stddev_time - stddev_time_lag)::numeric / stddev_time::numeric )*100, 1) as stddev_time_diff |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create unlogged table int4_aa (id int, id2 int, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz); | |
create unlogged table int4_bb (id int, id2 int, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz); | |
insert into int4_aa | |
select i, i, i, i, i, i, now(), now() | |
from generate_series(1, 5*1e6) i; | |
insert into int4_bb | |
select * from int4_aa; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- execute in pgwatch2 database | |
delete from pgwatch2.metric where m_name = 'stat_statements'; | |
insert into pgwatch2.metric(m_name, m_pg_version_from,m_sql) | |
values ( | |
'stat_statements', | |
9.2, | |
$sql$ | |
with q_data as ( |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- multiple LIKE conditions | |
select * from t_test where c LIKE any(array['%566%', '%7788%']) limit 5; | |
-- selecting json | |
select * from t_json where data @> '{"a":1}'; | |
-- array access | |
select ('{1:2,3}'::text[])[2]; | |
-- fixing sequences |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- SQL diff versioning | |
https://github.com/depesz/Versioning | |
-- Git version control | |
https://en.wikipedia.org/wiki/Git | |
-- Syntax formatter | |
http://sqlformat.darold.net/ | |
-- XML processing |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table t_order(id int, created_on timestamptz, value int, check (false) no inherit); | |
create index ON t_order(created_on); | |
create index ON t_order(id); | |
create table t_order_201701 (LIKE t_order INCLUDING indexes) INHERITS (t_order); | |
create table t_order_201612 (LIKE t_order INCLUDING indexes) INHERITS (t_order); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create or replace function all_queries(out q_pid int, out q_usename text, out q_query text) returns setof record as | |
$$ | |
select pid, usename::text, query from pg_stat_activity | |
$$ language sql security definer; |