Upgrade to Pro — share decks privately, control downloads, hide ads and more …

n Things You Didn't Know About PostgreSQL (Ruby...

n Things You Didn't Know About PostgreSQL (Rubyslava & PyVo 2014 Edition)

Markus Wein

June 25, 2014
Tweet

More Decks by Markus Wein

Other Decks in Programming

Transcript

  1. How? “It might help to explain that the pronunciation is

    "post-gres" or "post-gres-cue-ell", not "post-gray- something".” tom lane
  2. How? “Arguably, the 1996 decision to call it PostgreSQL instead

    of reverting to plain Postgres was the single worst mistake this project ever made. It seems far too late to change now, though.” tom lane
  3. “Though incorrect, PostgreSQL is more fun to use when you

    pronounce it as ‘post-Grayskull’.” karlinfox
  4. \e

  5. $ cat ~/.psqlrc \set PROMPT1 '%[%033[33;1m%]%x%[%033[0m%]%[%033[1m%]%/%[%033[0m%]%R%# ' \x auto \pset

    linestyle unicode \pset border 2 \pset null ¤ \pset format wrapped \timing
  6. Datatypes » Network address types » uuid (great for primary

    keys!) » Custom enumerated types » Binary data types » Geometric shapes » Lots more
  7. CREATE TYPE squid AS ( length float, tentacles integer, weight

    float ); CREATE TABLE atlantic_squid ( squid_key bigserial primary key, a_squid squid ); CREATE TABLE pacific_squid (LIKE atlantic_squid INCLUDING ALL);
  8. CREATE EXTENSION hstore; CREATE TABLE users ( id uuid PRIMARY

    KEY DEFAULT uuid_generate_v4(), email character varying(255), data hstore DEFAULT '' NOT NULL, created_at timestamp with time zone DEFAULT now(), updated_at timestamp with time zone DEFAULT now() );
  9. INSERT INTO users (email, data)VALUES ( '[email protected]', ' homepage =>

    "http://nuclearsquid.com", github => "cypher", twitter => "nuclearsquid", "a key" => "a value" ' );
  10. SELECT id, email FROM users WHERE data -> 'github' =

    'cypher'; !""""""""""""""""""""""""""""""""""""""#"""""""""""""""""""""""""$ % id % email % &""""""""""""""""""""""""""""""""""""""'"""""""""""""""""""""""""( % 75ba2347-1d7a-4ffa-b9f6-369df7b35665 % [email protected] % )""""""""""""""""""""""""""""""""""""""*"""""""""""""""""""""""""+ SELECT skeys(data) FROM users; !""""""""""$ % skeys % &""""""""""( % adn % % a key % % github % % twitter % % homepage % )""""""""""+
  11. Genres » No movie is 100% comedy or 100% tragedy

    » Each genre is an axis » Each movie gets a score between 0 and 10 for each genre » 0 is nonexistent, 10 strongest
  12. SELECT title, cube_distance(genre, '(0,7,0,0,0,0,0,0,0,7,0,0,0,0,10,0,0,0)') dist FROM movies WHERE cube_enlarge('(0,7,0,0,0,0,0,0,0,7,0,0,0,0,10,0,0,0)'::cube, 5,

    18) @> genre ORDER BY dist; !""""""""""""""""""""""""""""""""""""""""""""""""#""""""""""""""""""$ % title % dist % &""""""""""""""""""""""""""""""""""""""""""""""""'""""""""""""""""""( % Star Wars % 0 % % Star Wars: Episode V - The Empire Strikes Back % 2 % % Avatar % 5 % % Explorers % 5.74456264653803 % % Krull % 6.48074069840786 % % E.T. The Extra-Terrestrial % 7.61577310586391 % )""""""""""""""""""""""""""""""""""""""""""""""""*""""""""""""""""""+
  13. SELECT '{"id": 1, "email": "[email protected]"}'::json; !"""""""""""""""""""""""""""""""""""""""""""""""# $ json $ %"""""""""""""""""""""""""""""""""""""""""""""""&

    $ {"id": 1, "email": "[email protected]"} $ '"""""""""""""""""""""""""""""""""""""""""""""""( SELECT row_to_json(row) FROM (SELECT * FROM actors LIMIT 5 OFFSET 3000) row; !"""""""""""""""""""""""""""""""""""""""""""""""""# $ row_to_json $ %"""""""""""""""""""""""""""""""""""""""""""""""""& $ {"actor_id":3001,"name":"Lotte Lenya"} $ $ {"actor_id":3002,"name":"Lotte Verbeek"} $ $ {"actor_id":3003,"name":"Lou Costello"} $ $ {"actor_id":3004,"name":"Lou Diamond Phillips"} $ $ {"actor_id":3005,"name":"Lou Eppolito"} $ '"""""""""""""""""""""""""""""""""""""""""""""""""(
  14. SELECT * FROM json_each('{ "name":"depesz", "password":"super simple", "grades":[1,3,1,1,1,2], "skills":{ "a":"b",

    "c":[1,2,3] } }'); key | value ----------+------------------------ name | "depesz" password | "super simple" grades | [1,3,1,1,1,2] skills | {"a":"b", "c":[1,2,3]}
  15. CREATE TYPE rec AS (i integer, t text); CREATE FUNCTION

    set_of_records() RETURNS SETOF rec AS $$ // plv8.return_next() stores records in an internal tuplestore, // and return all of them at the end of function. plv8.return_next( { "i": 1, "t": "a" } ); plv8.return_next( { "i": 2, "t": "b" } ); // You can also return records with an array of JSON. return [ { "i": 3, "t": "c" }, { "i": 4, "t": "d" } ]; $$ LANGUAGE plv8; DO $$ plv8.elog(NOTICE, 'this', 'is', 'inline', 'code') $$ LANGUAGE plv8;
  16. CREATE TABLE posts ( id integer primary key, title text,

    body text, tags text[] ); SELECT title FROM posts WHERE tags @> ARRAY['ruby', 'rails'];
  17. SELECT daterange('("Jan 1 2013", "Jan 15 2013")') @> 'Jan 10

    2013'::date; !""""""""""# $ ?column? $ %""""""""""& $ t $ '""""""""""(
  18. CREATE TABLE reservation (room int, during tsrange); INSERT INTO reservation

    VALUES (1108, '[2010-01-01 14:30, 2010-01-01 15:30)'); SELECT * FROM reservation WHERE during @> '2010-01-01 15:00'::timestamp; !""""""#"""""""""""""""""""""""""""""""""""""""""""""""$ % room % during % &""""""'"""""""""""""""""""""""""""""""""""""""""""""""( % 1108 % ["2010-01-01 14:30:00","2010-01-01 15:30:00") % )""""""*"""""""""""""""""""""""""""""""""""""""""""""""+
  19. ALTER TABLE reservation ADD EXCLUDE USING gist (room with =,

    during WITH &&); INSERT INTO reservation VALUES (1108, '[2010-01-01 11:30, 2010-01-01 13:00)'); INSERT INTO reservation VALUES (1108, '[2010-01-01 14:45, 2010-01-01 15:45)'); ERROR: conflicting key value violates exclusion constraint "reservation_during_excl" DETAIL: Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts with existing key (during)=(["2010-01-01 14:30:00","2010-01-01 15:30:00")).
  20. CREATE INDEX users_active_index ON users(id) WHERE active IS TRUE; CREATE

    INDEX posts_titles ON posts(id, title) WHERE deleted = false;
  21. SELECT query, calls, total_time FROM pg_stat_statements ORDER BY total_time DESC;

    !""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""#"""""""#""""""""""""""""""$ % query % calls % total_time % &""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'"""""""'""""""""""""""""""( % SELECT title, cube_distance(genre, ?) dist FROM movies WHERE cub…% 1 % 36.833 % %…e_enlarge(?::cube, ?, ?) @> genre ORDER BY dist; % % % % SELECT title FROM movies WHERE title @@ ?; % 1 % 34.396 % % SELECT COUNT(*) FROM movies WHERE TITLE !~ ?; % 8 % 27.433 % % SELECT name, dmetaphone(name), dmetaphone_alt(name), metaphone(n…% 1 % 20.438 % %…ame, ?), soundex(name) FROM actors; % % % % SELECT * FROM actors WHERE name % ?; % 1 % 18.608 % )""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""*"""""""*""""""""""""""""""+
  22. SELECT title FROM movies WHERE title ILIKE 'stardust%'; !"""""""""""""""""""# $

    title $ %"""""""""""""""""""& $ Stardust Memories $ $ Stardust $ '"""""""""""""""""""(
  23. SELECT movie_id, title FROM movies WHERE levenshtein(lower(title), lower('a hard day

    nght')) <= 3; !""""""""""#""""""""""""""""""""$ % movie_id % title % &""""""""""'""""""""""""""""""""( % 245 % A Hard Day's Night % )""""""""""*""""""""""""""""""""+
  24. CREATE INDEX movies_title_trigram ON movies USING gist (title gist_trgm_ops); SELECT

    title FROM movies WHERE title % 'Avatre'; !""""""""# $ title $ %""""""""& $ Avatar $ '""""""""(
  25. SELECT title FROM movies WHERE title @@ 'night & day';

    !"""""""""""""""""""""""""""""""# $ title $ %"""""""""""""""""""""""""""""""& $ A Hard Day's Night $ $ Six Days Seven Nights $ $ Long Day's Journey Into Night $ '"""""""""""""""""""""""""""""""(
  26. SELECT title FROM movies WHERE to_tsvector(title) @@ to_tsquery('english ', 'night

    & day'); !"""""""""""""""""""""""""""""""# $ title $ %"""""""""""""""""""""""""""""""& $ A Hard Day's Night $ $ Six Days Seven Nights $ $ Long Day's Journey Into Night $ '"""""""""""""""""""""""""""""""(
  27. SELECT to_tsvector('A Hard Day''s Night'), to_tsquery('english', 'night & day'); !""""""""""""""""""""""""""""#"""""""""""""""""$

    % to_tsvector % to_tsquery % &""""""""""""""""""""""""""""'"""""""""""""""""( % 'day':3 'hard':2 'night':5 % 'night' & 'day' % )""""""""""""""""""""""""""""*"""""""""""""""""+
  28. SELECT * FROM actors WHERE name = 'Broos Wils'; !""""""""""#""""""$

    % actor_id % name % &""""""""""'""""""( )""""""""""*""""""+ SELECT * FROM actors WHERE name % 'Broos Wils'; !""""""""""#""""""$ % actor_id % name % &""""""""""'""""""( )""""""""""*""""""+
  29. SELECT title FROM movies NATURAL JOIN movies_actors NATURAL JOIN actors

    WHERE metaphone(name, 6) = metaphone('Broos Wils', 6); !"""""""""""""""""""""""""""""# $ title $ %"""""""""""""""""""""""""""""& $ The Fifth Element $ $ Twelve Monkeys $ $ Armageddon $ $ Die Hard $ $ Pulp Fiction $ $ The Sixth Sense $ $ Blind Date $ $ Die Hard with a Vengeance $ …
  30. SELECT name, dmetaphone(name), dmetaphone_alt(name), metaphone(name, 8), soundex(name) FROM actors; !"""""""""""""""""""""""""""""""""""""#""""""""""""#""""""""""""""""#"""""""""""#"""""""""$

    % name % dmetaphone % dmetaphone_alt % metaphone % soundex % &"""""""""""""""""""""""""""""""""""""'""""""""""""'""""""""""""""""'"""""""""""'"""""""""( % 50 Cent % SNT % SNT % SNT % C530 % % A Martinez % AMRT % AMRT % AMRTNS % A563 % % A. Michael Baldwin % AMKL % AMXL % AMXLBLTW % A524 % % Aaron Eckhart % ARNK % ARNK % ARNKHRT % A652 % % Aaron Paul % ARNP % ARNP % ARNPL % A651 % % Aaron Stanford % ARNS % ARNS % ARNSTNFR % A652 % % Abbie Cornish % APKR % APKR % ABKRNX % A126 % % Abby Dalton % APTL % APTL % ABTLTN % A134 % % Abhay Deol % APTL % APTL % ABHTL % A134 % % Abraham Sofaer % APRH % APRH % ABRHMSFR % A165 % % Adam Baldwin % ATMP % ATMP % ATMBLTWN % A351 % % Adam Beach % ATMP % ATMP % ATMBX % A351 % % Adam Hann-Byrd % ATMN % ATMN % ATMHNBRT % A355 % % Adam Lavorgna % ATML % ATML % ATMLFRKN % A354 % % Adam Roarke % ATMR % ATMR % ATMRRK % A356 % % Adam Sandler % ATMS % ATMS % ATMSNTLR % A352 % % Adam Storke % ATMS % ATMS % ATMSTRK % A352 % % Adam Trese % ATMT % ATMT % ATMTRS % A353 % % Adam West % ATMS % ATMS % ATMWST % A352 % % Addison Richards % ATSN % ATSN % ATSNRXRT % A325 % % Adele Mara % ATLM % ATLM % ATLMR % A345 % % Aden Young % ATNN % ATNN % ATNYNK % A355 % % Adewale Akinnuoye-Agbaje % ATLK % ATLK % ATWLKNYK % A342 % % Adolfo Celi % ATLF % ATLF % ATLFSL % A341 % % Adolphe Menjou % ATLF % ATLF % ATLFMNJ % A341 % (and so on)
  31. CREATE TABLE department ( id INTEGER PRIMARY KEY, -- department

    ID -- upper department ID parent_department INTEGER REFERENCES department, name TEXT -- department name ); INSERT INTO department (id, parent_department, "name") VALUES (0, NULL, 'ROOT'), (1, 0, 'A'), (2, 1, 'B'), (3, 2, 'C'), (4, 2, 'D'), (5, 0, 'E'), (6, 4, 'F'), (7, 5, 'G');
  32. WITH RECURSIVE subdepartment AS ( -- non-recursive term SELECT *

    FROM department WHERE name = 'A' UNION ALL -- recursive term SELECT d.* FROM department AS d JOIN subdepartment AS sd ON (d.parent_department = sd.id) ) SELECT * FROM subdepartment ORDER BY name;
  33. WITH moved_rows AS ( DELETE FROM products WHERE "date" >=

    '2010-10-01' AND "date" < '2010-11-01' RETURNING * ) INSERT INTO products_log SELECT * FROM moved_rows;
  34. SELECT name FROM actors WHERE name ~* 'll$'; !"""""""""""""""""""""""# $

    name $ %"""""""""""""""""""""""& $ Alan Marshall $ $ Albert Hall $ $ Amy Ingersoll $ $ Andie MacDowell $ $ André Morell $ $ Angeline Ball $ …
  35. A> LISTEN work_available; B> NOTIFY work_available, '44924'; A> Asynchronous notification

    "work_available" with payload "44924" received from server process with PID 77946. B> SELECT pg_notify('work_available', '1337'); A> Asynchronous notification "work_available" with payload "1337" received from server process with PID 77946.
  36. » PostgreSQL » CSV files » json (file) » S3

    » Oracle » MySQL » CouchDB » Redis » Neo4J
  37. 9.4 » REFRESH MATERIALIZED VIEW CONCURRENTLY » Writeable foreign data

    wrappers » Replication improvements: » Replication Slots » Logical Decoding » pg_prewarm » ALTER SYSTEM
  38. Recap » Many handy datatypes available » Lots of handy

    extensions » Hstore - NoSQL in your SQL » JSON/plv8 - Webscale in your SQL » Arrays are useful™
  39. Recap » Common Table Expressions make complicated queries easier »

    Ranges » Full Text Search built-in » Materialized views
  40. Sources » Adam Sanderson, “Postgres, the Best Tool You’re Already

    Using” » Craig Kierstens, “Postgres Demystified” » Peter van Hardenberg, “Postgres, The Bits You Haven’t Found”
  41. Sources » Selena Deckelmann, “Schema liberation with JSON and plv8

    (and Postgres)” (https://speakerdeck.com/ selenamarie/schema-liberation-with-json-and-plv8- and-postgres) » “Seven Databases in Seven Weeks”, by Eric Redmond & Jim R. Wilson (Pragmatic Programmers, 2012) » http://www.craigkerstiens.com » http://www.depesz.com/ » http://slid.es/xzilla/postgres-9-3