PostgreSQL

Extensions

  • Horizontal Scaling and Sharding: Citus DB
  • Columnar Data Storage: cstore_fdw
  • Files as Tables: file_fdw
  • Streaming Computations: PipelineDB
  • Timeseries: TimescaleDB

Generate Series

create table product (
  id serial primary key,
  created_at DATE,
  price NUMERIC(4,2)
);

insert into product (created_at, price) VALUES (
  generate_series('2015-01-12', '2015-02-22', interval '1 day'),
  generate_series(2.33, 45.12, 1.05)
);

Update Case-When

update product set kind = (
  case kind
  when 'book' then 'book_kind'
  when 'cd'   then 'cd_kind'
  end
) where kind in ('book', 'cd');

Insert Multiple

insert into widget (name) values ('Widget 1'), ('Widget 2'), ('Widget 3');

UUID

create extension "uuid-ossp";

... = uuid_generate_v4();

Truncate

Truncate doesn't scan the entire table while DELETE FROM without WHERE does.

Group vs Non-Group

select create_date, name from widget where create_date = (select min(create_date) from widget);

This won't work:

select min(create_date), name from widget;
Copyright © 2019 All rights reserved.
Powered by Kulfon.