Index

Multi column index

CREATE INDEX meetups_multi_index 
          ON meetups ("location", "language", "date");


SELECT * FROM meetups WHERE "location" = 'Prague' 
                        AND "language" = 'ruby' 
                        AND "date" = '2016-09-15';


SELECT * FROM meetups WHERE "location" = 'Prague' 
                        AND "language" = 'ruby';


SELECT * FROM meetups WHERE "location" = 'Prague';

Expression index

CREATE INDEX lower_email ON meetups (LOWER("email"));

SELECT * FROM users WHERE LOWER("email") = LOWER('David@Schovi.cz');

Index implicitly supports pattern matching LIKE 'david%'

CREATE INDEX reverse_email ON users (REVERSE("email"));

SELECT * FROM users WHERE REVERSE("email") LIKE REVERSE('%schovi.cz');

Partial index

CREATE INDEX reminders_not_sent_at 
          ON reminders(sent_at) WHERE NOT sent_at;

SELECT * FROM reminders WHERE send_at IS NULL;

Data types

Date and time

-- rails begining_of_day(Time.now)
SELECT date_trunc('day', start_at)::date, count(*)
FROM meetups
GROUP BY date_trunc('day', start_at);

-- Get date before 3 months
SELECT now() - '3 month'::interval;

-- Series
SELECT generate_series(
         date_trunc('day', now()),
         date_trunc('day', now()::date + '1 month'::interval),
         '1 day'::interval
       )

Enum

-- Create
CREATE TYPE languages_enum AS 
  ENUM ('ruby', 'javascript', 'python', 'crystal');

-- Update
ALTER TYPE languages_enum ADD VALUE 'java' AFTER 'ruby';

-- Add column with enum type
CREATE TABLE meetups (
    language languages_enum
);

-- Using
INSERT INTO meetups(language) VALUES ('ruby');

INSERT INTO meetups(language) VALUES ('c++');
-- ERROR:  invalid input value for enum languages_enum: "c++"

Array

-- Creating
SELECT ARRAY[1, 2, 3];

-- Updating existing
SELECT ARRAY[1,2] || 3;
SELECT 3 || ARRAY[1,2];
SELECT ARRAY[1,2] || ARRAY[3, 4];

-- Aggregate to array
SELECT array_agg(distinct language) from meetups where location = 'Prague';

-- Create column
CREATE TABLE example (
    my_text_array text[],
    matrix number[][]
);

-- Get item
SELECT (ARRAY['a', 'b', 'c'])[1]; -- 'a'

-- Query
SELECT * FROM meetups WHERE tags @> array['party', 'food'];

-- Index
CREATE INDEX ON meetups(tags);

Jsonb

-- Create column
ALTER TABLE meetups ADD COLUMN meta jsonb;

-- Creating
SELECT '{"key": "val", "nested": {"key2": "val2"}}'::jsonb;

-- Upadting
SELECT meta || '{"new_key": "new_value"}'::jsonb FROM meetups;

-- Get item
SELECT meta->>'key'; -- "val"
SELECT meta->'nested'->>'key2'; -- "val2"
SELECT jsonb_extract_path_text(meta, 'nested', 'key2'); -- "val2"

-- Query
SELECT * FROM meetups WHERE meta ? 'nested';
SELECT * FROM meetups WHERE meta->'nested' @> '{"key2": "val2"}'
SELECT * FROM meetups WHERE meta->'nested'->>'key2' = 'val2'

-- Index
CREATE INDEX ON meetups(meta);

Extensions

-- https://github.com/petere/pgemailaddr
SELECT user(email) FROM users WHERE host(email) = 'schovi.cz'

-- https://github.com/petere/pguri
SELECT * FROM meetups WHERE uri_host(web) = 'schovi.cz'
SELECT host('https://schovi.cz'::uri)

WITH

WITH

WITH
  habitable_cities AS (
    SELECT id FROM cities
    WHERE current_temp BETWEEN 15 AND 25
  ), 
  interesting_languages (
    SELECT name FROM languages
    WHERE popularity > 90
  )
SELECT * FROM meetups
WHERE city_id IN habitable_cities
  AND language IN interesting_languages;

WITH RECURSIVE

WITH RECURSIVE all_categories AS (
  -- Static
  SELECT c.id, c.name, c.category_id, ARRAY[c.id] AS path 
  FROM categories c
  WHERE category_id IS NULL

  UNION ALL
  
  -- Recursive
  SELECT c.id, c.name, c.category_id, ac.path || c.id
  FROM categories c
  JOIN all_categories ac ON c.category_id = ac.id
)
SELECT * FROM all_categories WHERE path @> ARRAY[1] ORDER BY path;

Window Functions

PARTITION BY

SELECT id, location, language, attendees,
       (
         100 -
         attendees / 
         AVG(attendees) OVER (PARTITION BY language)
         * 100
       ) AS attendees_to_avg
FROM meetups;

ORDER BY

SELECT name, date, attendees,
       SUM(attendees) OVER (ORDER BY date ASC) as total
FROM meetups
WHERE name = 'Prague Ruby Meetup';

Functions

SELECT name, date, attendees,
       rank() OVER (ORDER BY attendees DESC)
FROM meetups
WHERE name = 'Prague Ruby Meetup';

first_value(column)

last_value(column)

lag(column, [offset, [default]])

lead(column, [offset, [default]])

https://www.postgresql.org/docs/9.6/static/functions-window.html

ROWS

SELECT name, date, attendees,
       (
         attendees * 100.0 /
       	 lag(attendees, 1) OVER (ORDER BY date ASC ROWS 1 PRECEDING)
       ) as diff
FROM meetups
WHERE name = 'Prague Ruby Meetup';

ROWS UNBOUNDED PRECEDING AND CURRENT ROW

ROWS 10 PRECEDING AND CURRENT ROW

ROWS CURRENT ROW AND 20 FOLLOWING

ROWS CURRENT ROW AND UNBOUNDED FOLLOWING

https://www.postgresql.org/docs/9.1/static/sql-expressions.html​

Rails?

String

class Meetup < ActiveRecord::Base
  def self.sql(raw_sql, *values)
    sql = ActiveRecord::Base.send(:sanitize_sql_array, [raw_sql, *values])
    find_by_sql(sql)
  end
end

Meetup.sql(<<~SQL, 10, 'ruby')
  WITH popular_cities AS (
    SELECT name FROM cities 
      ORDER BY meetups_count DESC
      LIMIT ?
  )
  SELECT * FROM meetups
  WHERE city IN popular_cities
    AND language = ?;
SQL

String and Scopes

class Meetup < ActiveRecord::Base
  def self.find_in_popular_city(cities_count, language)
    popular_cities = City.popular.limit(cities_count)

    main_query = self.where("city IN popular_cities").
                      where(language: language)

    find_by_sql(<<~SQL)
      WITH popular_cities AS (#{popular_cities.to_sql})
      #{main_query.to_sql}
    SQL
  end
end

Arel

?

Gem postgres_ext

# Gemfile

gem "postgres_ext"

# App

Meetup.with(popular_cities: City.popular.limit(10)).
       where("city IN popular_cities").
       where(language: 'ruby')

Gem schema_plus

# Gemfile

gem "schema_plus"

# Migration

add_index :users, index: { expression: "REVERSE(email)" }

create_enum :color, 'red', 'green', 'blue'
alter_enum :color, 'purple', after: 'red'
drop_enum :color

Dont be lazy to write code

# migration
def up
  execute "CREATE TYPE roles AS ENUM ('worker', 'manager', 'boss');"
end 
        
# model
class Employee < ActiveRecord::Base
  include PostgresEnum
  
  postgres_enum :role
end

# usage 

# > Employee.new(role: 'ceo')
#   SQL (1.9ms)  SELECT unnest(enum_range(NULL::"roles"))::text AS enum_value;
# ArgumentError: 'ceo' is not a valid role (available values: worker, manager, boss)

https://gist.github.com/schovi/b92f4c9b22af1c492fb12864d6ce8a5c

Thanks!

Any questions?