PostgreSQL - Study Notes 6

CTEs (Common Table Expressions):

 

 

Not materialized, because it is used only once.

 

Explicitly creating the CTE as not materialized looks having better performance than let PostgreSQL decide to do that.

 

Creating the CTE as materialized got bad performance.

 

 

CTEs with window functions:

 

     lag(), lead()

......

......

 

......

 

 

 

 

Recursive CTEs:

 

     random()

 

 

Hierarchical recursive CTEs:

 

 

Handling nulls:

 

 

 

     nullif()

 

 

 

Row value syntax:

          

 

 

     extract()

......

 

 

Views:

show search_path;
-----------------
 "$user", public

 

 

 

Materialized views:

A materialized view is like a cache. It is used for expensive queries. Its data is saved on disk and can be out of date, so you need to refresh it periodically. 

 

 

 

 

Removing duplicate rows:

 

 

Upsert:

create table kv (
    key   text primary key,
    value TEXT
);

insert into kv (key, value) values ('cache:foo', 123);
select * from kv;
-- cache:foo 123

insert into kv (key, value) values ('cache:foo', 456) on conflict (key) do nothing;
select * from kv;
-- cache:foo 123

insert into kv (key, value) values ('cache:foo', 456) on conflict (key) do update set value = excluded.value;
select * from kv;
-- cache:foo 456

insert into kv (key, value) values ('cache:foo', 789) on conflict (key) do update set value = kv.value;
select * from kv;
-- cache:foo 456

insert into kv (key, value) values ('cache:foo', 789) on conflict (key) do update set value = excluded.key;
select * from kv;
-- cache:foo cache:foo

insert into kv (key, value) values ('cache:foo', 789) on conflict (key) do update set value = kv.key;
select * from kv;
-- cache:foo cache:foo

 

drop table kv;

create table kv (
    key   text primary key,
    value int4
);

insert into kv (key, value) values ('hits:homepage', 1);
select * from kv;
-- hits:homepage 1

insert into kv (key, value) values ('hits:homepage', 10) on conflict (key) do update set value =  kv.value + excluded.value;
select * from kv;
-- hits:homepage 11

 

Returning keyword:

insert into kv (key, value) values ('hits:homepage', 10) on conflict (key) do update set value =  kv.value + excluded.value
returning *;
-- hits:homepage 21

delete from kv
returning *;
-- hits:homepage 21

insert into kv (key, value) values ('hits:homepage', 1)
returning *;
-- hits:homepage 1

update kv set value = 2
returning *;
-- hits:homepage 2

 

Coalesce generated columns: