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