kmoppel

-- COPY t_oil FROM PROGRAM 'curl https://www.cybertec-postgresql.com/secret/oil_ext.txt';
BEGIN;

CREATE TABLE t_oil (
    region text,
    country text,
    year integer,
    production integer,
    consumption integer
);

kmoppel

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;

kmoppel

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);

kmoppel

-- 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

kmoppel

-- 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

kmoppel

-- 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 (

kmoppel

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;

kmoppel

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

kmoppel

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';

kmoppel

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)
;