28 Apr '16, 7pm

Postgres window functions and Common Table Expressions (CTEs)

CREATE TABLE posts ( id integer PRIMARY KEY, body varchar, created_at timestamp DEFAULT current_timestamp ); CREATE TABLE comments ( id INTEGER PRIMARY KEY, post_id integer NOT NULL, body varchar, created_at timestamp DEFAULT current_timestamp ); /* make two posts */ INSERT INTO posts VALUES (1, 'foo'); INSERT INTO posts VALUES (2, 'bar'); /* make 4 comments for the first post */ INSERT INTO comments VALUES (1, 1, 'foo old'); INSERT INTO comments VALUES (2, 1, 'foo new'); INSERT INTO comments VALUES (3, 1, 'foo newer'); INSERT INTO comments VALUES (4, 1, 'foo newest'); /* make 4 comments for the second post */ INSERT INTO comments VALUES (5, 2, 'bar old'); INSERT INTO comments VALUES (6, 2, 'bar new'); INSERT INTO comments VALUES (7, 2, 'bar newer'); INSERT INTO comments VALUES (8, 2, 'bar newest');

Full article: https://robots.thoughtbot.com/postgres-window-functions

Tweets

#TravelTuesday

velvetescape.com 29 Apr '16, 4pm

Congonhas is arguably one of the most notorious airports in the world. It reminds me of Hong Kong’s old Kai Tak airport wh...

Create a vertical garden on your window or wall...

inhabitat.com 27 Apr '16, 5pm

Imagine being able to create your own vertical garden right on your window or wall in just a few minutes. That’s exactly w...

How to create Postgres indexes concurrently in ...

robots.thoughtbot.com 25 Apr '16, 5pm

By default, Postgres locks writes (but not reads) to a table while creating an index on it. That can result in unacceptabl...

Postgres Index Types

robots.thoughtbot.com 21 Apr '16, 12am

As discussed in “A Grand Piano for Your Violin” , indexes are quite useful for speeding up database queries. When creating...