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

select distinct on (id) id, value2 from t_pk order by id, value2 asc nulls first;

-- filter
explain analyze select region, sum(case when year = 2009 then production else 0 end) as p2009, sum(case when year = 2010 then production else 0 end) as p2010 from t_oil group by region;

explain analyze select region, sum(production) filter (where  year = 2009) as p2009, sum(production) filter (where  year = 2010) as p2010 from t_oil group by region;

-- array_agg
select string_agg(a::text, ':')::text from (select a from t_test limit 100) s;
select array_agg(a order by a desc)::text from (select a from t_test limit 10) s;

kmoppel

create table t_log (created_on timestamptz not null, event text);

create table t_log_archive (created_on timestamptz not null, event text);

insert into t_log select d, 'event' from generate_series(current_date-300, now(), '5m'::interval) d;

with q_sub as (delete from t_log where created_on < current_date - 30 returning *) insert into t_log_archive select * from q_sub;

kmoppel

-- subqueries
explain select * from t_counter c, lateral (select * from t_joiny j where j.a = c.id) s;
explain select * from t_counter c join lateral (select * from t_joiny j where j.a = c.id) s on true;
-- functions
explain select * from t_counter, lateral (select * from generate_series(1, id)) s;
explain select * from t_counter,  generate_series(1, id) s;

kmoppel

-- exporting data with COPY to Excel
\copy (select * from t_test limit 2) to '/tmp/test.dump' with (format csv, header)
-- on Windows
\copy (select * from t_test limit 2) to 'c:\\User\\X\\test.dump' with (format csv, header)

kmoppel

-- normal view
create or replace view v_test as select a, b from t_test;
explain select * from v_test where a < 100;

-- materialized view
create materialized view m_test as select a, b from t_test;
refresh materialized view m_test ; -- locking version
refresh materialized view CONCURRENTLY m_test ; -- needs unique key!

-- recursion

kmoppel

-- 1. selectivity basics

CREATE TABLE t_person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);

INSERT INTO t_person(name) SELECT unnest(array['alice', 'bob']) FROM generate_series(1, 1000000);

CREATE INDEX ON t_person (name);

ANALYZE t_person;

kmoppel

SET maintenance_work_mem TO '1GB';

CREATE TABLE t_test (a int NOT NULL, b int, c text);
INSERT INTO t_test SELECT i AS a, random()*100 as b, random() as c FROM generate_series(1, 2*1e6::int) AS i;
CREATE INDEX ON t_test (a);
ANALYZE t_test;

CREATE TABLE t_joiny (a INT NOT NULL, b int);
INSERT INTO t_joiny SELECT i AS a, (random()*100)::int as b FROM generate_series(1, 1e6::int) AS i ;
CREATE UNIQUE INDEX ON t_joiny (a);

kmoppel

PATH=/usr/local/pgsql/bin:$PATH

MASTER_PORT=5432
REPLICA_IP=172.31.28.169

function append_param_to_config {
OPTS=$(cat <<HERE
${1}
HERE
)

kmoppel

ALTER TABLE pgbench_accounts
	ADD created_on timestamptz,
	DROP CONSTRAINT pgbench_accounts_pkey;

CREATE UNIQUE INDEX ON pgbench_accounts(aid, created_on DESC);