PostgreSQL 's Werelds meest geävanceerde opensource database... ...maar waarom eigenlijk? Een wilde rit op de rug van een olifant langs alle interessante plekjes in de wereld van databases en webdevelopment (waar die rottige dolfijn niet kan komen)
Wat gaan we doen: Verschikkelijk kort intro PostgreSQL's basis schopkontigheden Standaard uitbreidingen op de standaard Uitbreidingen op de uitbreidingen Performance en availability Indexes, loadbalancing en replicatie Beheer functies Wat maakt PgSQL makkelijk te beheren
Wat gaan we niet doen? MySQL bashen. select 2='Too sexy for my Shirt'; ++ | 2='Too sexy for my Shirt' | ++ | 0 | ++ 1 row in set, 1 warning (0.01 sec) mysql> select 2='2 sexy for my shirt'; ++ | 2='2 sexy for my shirt' | ++ | 1 | ++ 1 row in set, 1 warning (0.00 sec) select 2='2 sexy for my shirt'; ERROR: invalid input syntax for integer: "2 sexy for my shirt" LINE 1: select 2='2 sexy for my shirt'; ^
Verschrikkelijk kort intro, deel 1 Begonnen in 1977 als Ingres, een oefening in database bouwen. 1986 Broncode verkocht aan Computer Associates Nieuw project: PostGres, oefening in nieuwe database concepten.
1995 Querytaal vervangen door een meer SQL-standaard versie Naamsverandering naar PostGres95
1996 Naamsverandering naar PostgreSQL PostgreSQL wordt opensource.
1996 → heden Bijzonder ontiegelijk veel goed ontwikkelwerk.
Verschrikkelijk kort intro, deel 2 Volgt zoveel mogelijk de SQL standaard. Wijkt alleen af als uit intiegelijk lange discussies met diepgravende argumenten blijkt dat wat de standaard voorschrijft in de praktijk niet goed werkt. Afwijkingen zijn goed gedocumenteerd in de handleiding.
Volledig MVCC, ook DDL. ALTER TABLE kan in een transactie worden geROLLBACKt.
De nadruk ligt op betrouwbaarheid en functionaliteit. Extreem uitbreidbaar door eindgebruikers. Draait op unix/linux/windows en OSX Geen licentiekosten voor commercieel gebruik. Grote actieve community Mailinglists waar ook de core-programmeurs aan deelnemen.
Verschrikkelijk kort intro, deel 3 Max database afmeting:
geen limiet.
Max rijen per tabel:
geen limiet.
Max tabel afmeting:
32 TB
Max rij lengte:
1.6 TB
Max veld afmeting:
1 GB
Max kolommen per tabel:
250-1600
Max indexes per tabel:
geen limiet.
Basis schopkontigheden
SEQUENCE CREATE SEQUENCE sekki START 101; SELECT NEXTVAL('sekki'); “101” SELECT NEXTVAL('sekki'); “102”
Chuck Norris' iPod came with a real charger
instead of just a USB cord
SEQUENCE CREATE SEQUENCE n INCREMENT 3 MINVALUE 10 MAXVALUE 17 CYCLE; SELECT NEXTVAL('n'); “10” SELECT NEXTVAL('n'); “13” SELECT NEXTVAL('n'); “16” SELECT NEXTVAL('n'); “10”
SEQUENCE CREATE SEQUENCE n INCREMENT 3 MINVALUE 10 MAXVALUE 27 NO CYCLE; SELECT NEXTVAL('n'); “10” SELECT NEXTVAL('n'); “13” SELECT NEXTVAL('n'); “16” SELECT NEXTVAL('n'); ERROR: nextval: reached maximum value of sequence "n" (17) SQL state: 55000
RETURNING CREATE TEMPORARY SEQUENCE sekki START 101; CREATE TEMPORARY TABLE sektest ( id INTEGER DEFAULT nextval('sekki') , name TEXT); INSERT INTO sektest (name) VALUES ('von Krackenfahrt') RETURNING id; id 101 (1 row)
RETURNING CREATE TABLE foo (id int, name varchar(128)); INSERT INTO foo values (1,'Krackenfahrt'); INSERT INTO foo values (2,'Gavrok'); UPDATE foo SET name = 'von Krackenfahrt' WHERE name = 'Krackenfahrt' RETURNING *; id | name + 1 | Krackenfahrt (1 row)
RETURNING CREATE TABLE foo (id int, name varchar(128)); INSERT INTO foo values (1,'Krackenfahrt'); INSERT INTO foo values (2,'Gavrok'); DELETE FROM foo WHERE id<100 RETURNING *; id | name + 1 | Krackenfahrt 2 | Gavrok
RETURNING CREATE TABLE producten (product_no SERIAL , name VARCHAR(100) , price NUMERIC(10,4) , voorraad INTEGER); INSERT INTO producten (name, price, voorraad) VALUES ('floppy', 1.44, 8) RETURNING (product_no); “324” UPDATE producten SET voorraad = voorraad 2 WHERE product_no=324 RETURNING voorraad; “6”
GENERATE_SERIES() SELECT GENERATE_SERIES(1,10) AS x; 1 2 3 4 5 6 7 8 9 10
GENERATE_SERIES() SELECT (CURRENT_DATE + (x * INTERVAL '1 DAY'))::DATE FROM GENERATE_SERIES(1,10) AS x; 2012-07-01 2012-07-02 2012-07-03 2012-07-04 2012-07-05 2012-07-06 2012-07-07 2012-07-08 2012-07-09 2012-07-10
GENERATE_SERIES() Ontbrekende datums aanvullen. CREATE TABLE agenda (datum timestamp, event varchar(255)); INSERT INTO agenda VALUES ('20120406','Party hardy'); INSERT INTO agenda VALUES ('20120409','Schoenen kopen'); SELECT d.fakedate, event FROM (SELECT '20120401'::date + INTERVAL '1 DAY' * GENERATE_SERIES(0,30) AS fakedate) AS d LEFT JOIN agenda ON d.fakedate=datum ORDER BY d.fakedate ASC; dd | event + 20120401 00:00:00 | 20120402 00:00:00 | 20120403 00:00:00 | 20120404 00:00:00 | 20120405 00:00:00 | 20120406 00:00:00 | Party hardy 20120407 00:00:00 | 20120408 00:00:00 | 20120409 00:00:00 | Schoenen kopen 20120410 00:00:00 | 20120411 00:00:00 | 20120412 00:00:00 |
GENERATE_SERIES() Heel snel heel veel data genereren. CREATE TABLE foo (id INT, title varchar); INSERT INTO foo (id, title) SELECT x, 'title' || x::varchar FROM generate_series(1,100) AS x; id | title + 1 | title1 2 | title2 3 | title3 4 | title4 5 | title5 … 99 | title99 100 | title100 (100 rows)
INTERSECT SELECT * FROM ( SELECT GENERATE_SERIES(1,10) INTERSECT SELECT GENERATE_SERIES(5,12) ) AS foo; generate_series 6 5 8 9 7 10 (6 rows)
EXCEPT SELECT * FROM ( SELECT GENERATE_SERIES(1,10) EXCEPT SELECT GENERATE_SERIES(5,12) ) AS foo; generate_series 4 1 2 3 (4 rows)
Common Table Expressions (CTE's) Herbruikbare tijdelijk in-query view. Alias voor een subquery blok.
Chuck Norris can smell what the rock is cookin.
CTE's CREATE TABLE foo (id int, name varchar(128)); INSERT INTO foo values (1,'Krackenfahrt'); INSERT INTO foo values (2,'Gavrok'); WITH currentuser AS (SELECT * FROM foo WHERE id=2) SELECT * FROM currentuser; id | name + 2 | Gavrok (1 row)
Updatable CTE's CREATE TABLE foo (id int, name varchar(128)); INSERT INTO foo values (1,'Krackenfahrt'); INSERT INTO foo values (2,'Gavrok'); CREATE TABLE foo_backup (id int, name varchar(128)); WITH deleted_rows AS (DELETE FROM foo WHERE id=2 RETURNING *) INSERT INTO foo_backup SELECT * FROM deleted_rows; SELECT * FROM foo_backup; id | name + 2 | Gavrok (1 row)
Recursive CTE's Doet een recursieve self-join. Levert een lijst met het hoofdrecord, zijn kinderen, de kinderen van de kinderen, de kinderen van de kinderen van de kinderen, etc. Is niet genest! Goed voor het ophalen van paden.
Paden CREATE TABLE categories (cat_id INTEGER, parent_cat_id INTEGER, title VARCHAR); INSERT INTO categories VALUES (1, null, 'root'); INSERT INTO categories VALUES (2, 1, 'muziek'); INSERT INTO categories VALUES (3, 1, 'cabaret'); INSERT INTO categories VALUES (4, 3, 'B.Visser');
Paden WITH RECURSIVE paden AS ( SELECT cat_id, parent_cat_id, title, title as pad FROM categories WHERE parent_cat_id IS NULL UNION SELECT category_children.cat_id , category_children.parent_cat_id, category_children.title , pad || '/' || category_children.title AS pad FROM categories AS category_children INNER JOIN paden ON paden.cat_id = category_children.parent_cat_id ) SELECT * FROM paden;
Paden cat_id | parent_cat_id | title | pad +++ 1 | | root | root 2 | 1 | muziek | root/muziek 3 | 1 | cabaret | root/cabaret 4 | 3 | B.Visser | root/cabaret/B.Visser (4 rows)
Windowing Groeperen zonder te groeperen. Running totals per groep. Min/max, eerste/laatste in de groep. Ranking Vorige/volgende waarde
Chuck Norris once jumped out of a basement window
Windowing CREATE TABLE uitslagen ( score integer, has_cheated integer, spelernummer integer ); INSERT INTO uitslagen VALUES (88, 1, 1); INSERT INTO uitslagen VALUES (100, 1, 2); INSERT INTO uitslagen VALUES (16, 1, 3); INSERT INTO uitslagen VALUES (17, 1, 4); INSERT INTO uitslagen VALUES (33, 1, 5); INSERT INTO uitslagen VALUES (6, 1, 6); INSERT INTO uitslagen VALUES (25, 0, 7); INSERT INTO uitslagen VALUES (19, 0, 8); INSERT INTO uitslagen VALUES (30, 0, 9); INSERT INTO uitslagen VALUES (98, 1, 10);
Windowing SELECT spelernummer , score , RANK() OVER (w) AS positie , score LEAD(score) OVER (w) AS diff_next , score LAG(score) OVER (w) AS diff_prev , SUM(score) OVER (w RANGE UNBOUNDED PRECEDING ) , AVG(score) OVER (w RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) , has_cheated FROM uitslagen WINDOW w AS (PARTITION BY has_cheated ORDER BY score DESC) ORDER BY has_cheated, positie ASC;
Windowing spelernr | score | pos | d_next | prev | rtot | tot | tot2 | cheated ++++++++ 9 | 30 | 1 | 5 | | 30 | 74 | 74 | 0 7 | 25 | 2 | 6 | 30 | 55 | 74 | 74 | 0 8 | 19 | 3 | | 25 | 74 | 74 | 74 | 0 2 | 100 | 1 | 2 | | 100 | 358 | 286 | 1 10 | 98 | 2 | 10 | 100 | 198 | 358 | 319 | 1 1 | 88 | 3 | 55 | 98 | 286 | 358 | 336 | 1 5 | 33 | 4 | 16 | 88 | 319 | 358 | 252 | 1 4 | 17 | 5 | 1 | 33 | 336 | 358 | 160 | 1 3 | 16 | 6 | 10 | 17 | 352 | 358 | 72 | 1 6 | 6 | 7 | | 16 | 358 | 358 | 39 | 1 (10 rows)
Windowing WITH data AS (SELECT spelernummer , score , has_cheated , RANK() OVER (w) AS positie FROM uitslagen WINDOW w AS (PARTITION BY has_cheated ORDER BY score DESC) ) SELECT * FROM data WHERE positie <=2; spelernummer | score | has_cheated | positie +++ 9 | 30 | 0 | 1 7 | 25 | 0 | 2 2 | 100 | 1 | 1 10 | 98 | 1 | 2 (4 rows)
Handige types.
INET Beschrijft een IP adres of range. SELECT INET '192.168.1.5' <<= INET '192.168.1.0/24'; 't' SELECT '256.256.256.256'::inet; ERROR: invalid input syntax for type inet: "256.256.256.256"
Chuck Norris can ping 256.256.256.256 and get a reply.
INET CREATE TABLE allowed_addr (title TEXT, ipaddr INET); INSERT INTO allowed_addr VALUES ('localhost','127.0.0.1'); INSERT INTO allowed_addr VALUES ('dev','192.168.1.0/24'); INSERT INTO allowed_addr VALUES ('nerdrange','abcd:1234:1234:abcd:1234:abcd:abcd:1234'); SELECT title FROM allowed_addr WHERE '192.168.1.5' <<= ipaddr; title dev range (1 row)
INTERVAL SELECT '42 week 3.14 day'::INTERVAL; interval 297 days 03:21:36 (1 row)
Chuck Norris once ordered a Big Mac at Burger King,
...and got one.
INTERVAL CREATE TABLE abbo ( id INT, startdate DATE, duration INTERVAL); INSERT INTO abbo VALUES (1, '20120114', '3 WEEK'); SELECT id, startdate, (startdate + duration)::date AS enddate FROM abbo; id | startdate | enddate ++ 1 | 20120114 | 20120204 (1 row)
TIMESTAMP Bereik van 4713 BC tot 5.874.897 AD Nauwkeurigheid tot in microseconden SELECT NOW() AT TIME ZONE 'CET'; 2012-06-20 08:16:32.240806 Timestamp kolommen veranderen nooit vanzelf van waarde. Meerdere acties binnen dezelfde seconde krijgen aparte timestamps.
Chuck Norris doesn't wear a watch. He decides what time it is.
Timestamp en transacties NOW() is de starttijd van de transactie. clock_timestamp() is de huidige systeemtijd, ook binnen transaties. BEGIN; SELECT NOW(); 20120910 22:45:48.626417+02 SELECT NOW(); 20120910 22:45:48.626417+02 SELECT clock_timestamp(); 20120910 22:45:59.057902+02 SELECT clock_timestamp(); 20120910 22:46:00.249995+02 ROLLBACK; SELECT clock_timestamp(), clock_timestamp(); clock_timestamp | clock_timestamp + 20120910 22:48:21.189502+02 | 20120910 22:48:21.189504+02
TIMESTAMP met tijdzone SELECT '2012-01-04 12:00:00 Europe/Amsterdam'::TIMESTAMPTZ; 2012-01-04 12:00:00 SELECT '2012-01-04 12:00:00 Europe/Amsterdam'::TIMESTAMPTZ AT TIME ZONE 'Europe/London'; 2012-01-04 11:00:00
TIMESTAMP met tijdzone SELECT '2012-01-04 12:00:00 Europe/Amsterdam'::TIMESTAMPTZ; 2012-01-04 12:00:00 SELECT '2012-01-04 12:00:00 Europe/Amsterdam'::TIMESTAMPTZ AT TIME ZONE 'Europe/London'; 2012-01-04 11:00:00
Zomer- en wintertijd SELECT '2012-01-04 12:00:00 UTC' AT TIME ZONE 'Europe/Amsterdam'; 2012-01-04 13:00:00 SELECT '2012-06-04 12:00:00 UTC' AT TIME ZONE 'Europe/Amsterdam'; 2012-06-04 14:00:00
To INFINIY.... and -INFINITY! Geldige waarden voor DATE/TIME velden Gedraagt zich als een timestamp die altijd hoger/lager is dan alle datums die je kunt verzinnen. SELECT 'infinity'::TIMESTAMP INTERVAL '1 day'; infinity
Chuck Norris has counted to infinity. Twice.
Back to the future. id
prijs
Startdate
enddate
2
14.33
-infinity
2012-05-04
2
14.37
2012-05-04
2012-05-08
2
14.40
2012-05-08
infinity
SELECT * FROM prijzentabel WHERE NOW() BETWEEN startdate AND enddate;
Range types (9.2) CREATE TABLE reservation (room int, during tsrange); INSERT INTO reservation VALUES (1108, '[20100101 14:30, 20100101 15:30)');
Vlotte Functies
String meuk SELECT regexp_split_to_array('hello world', E'\\s+'); regexp_split_to_array {hello,world} (1 row) SELECT UNNEST(regexp_split_to_array('hello world', E'\\s+')); unnest hello world (2 rows)
OVERLAPS SELECT (DATE '20010101', DATE '20011231') OVERLAPS (DATE '20010601', DATE '20021231'); overlaps t SELECT (DATE '20010101', INTERVAL '1 YEAR') OVERLAPS (DATE '20010601', DATE '20021231'); overlaps t
OVERLAPS CREATE TABLE kalender (title text , event_startdate timestamp , event_enddate timestamp); INSERT INTO kalender VALUES ('Castlefest', '20130801', '20130801'); INSERT INTO kalender VALUES ('Elf Fantasy Fair', '20130420', '20130421'); SELECT title FROM kalender WHERE (DATE '20130725', DATE '20130802') OVERLAPS (event_startdate, event_enddate)' Title Castlefest Superman owns a pair of Chuck Norris pajamas. (1 rows)
Geometrie Overlappen twee rechthoeken: SELECT box '((0,0),(1,1))' && box '((0,0),(2,2))'; Valt punt binnen cirkel: SELECT circle '((0,0),2)' @> point '(1,1)'; Doorsnijdt een lijn een rechthoek: SELECT lseg '((-1,0),(1,0))' ?# box '((-2,-2),(2,2))' Wat is de lengte van een pad SELECT LENGTH(path '((0,0),(1,0),(1,1),(0,1))') SELECT point(polygon '((0,0),(1,1),(2,0))') "(1,0.333333333333333)"
Krachtige Consistency
Stricte datatypen SELECT 2='2 sexy for my Shirt'; ERROR: invalid input syntax for integer: "2 sexy for my Shirt" SELECT 1/0; ERROR: division by zero SELECT CAST('20120229' AS DATE); "20120229" SELECT CAST('20130229' AS DATE); ERROR: date/time field value out of range: "20130229"
Stricte datatypen CREATE TEMPORARY TABLE foo (id INT); INSERT INTO foo(id) VALUES (2147483647); INSERT INTO foo(id) VALUES (2147483648); ERROR: integer out of range Geen geneuzel met signed en unsigned.
Stricte datatypen CREATE TABLE foo (name VARCHAR(6)); INSERT INTO foo (name) VALUES ('Nelis'); INSERT INTO foo (name) VALUES ('Nelisje'); ERROR: value too long for type character varying(6)
NOT NULL is echt NOT NULL CREATE TABLE foo (id int); INSERT INTO foo VALUES (null); CREATE TABLE bar (id int not null); INSERT INTO bar SELECT * FROM foo; ERROR: null value in column "id" violates notnull constraint
Chuck Norris can insert null values
into a non-nullable primary key column and it'll still be unique.
Bashmomentje create table foo (id int); insert into foo values (null); create table bar (id int not null); insert into bar select * from foo; Query OK, 1 row affected, 1 warning (0.04 sec) Records: 1 Duplicates: 0 Warnings: 1 select * from bar; ++ | id | ++ | 0 | ++ 1 row in set (0.00 sec)
ENUM CREATE TYPE gendertype AS enum('m','f','u'); CREATE TABLE foo (id int, gender gendertype); INSERT INTO foo VALUES (1, 't'); ERROR: invalid input value for enum gendertype: "t" LINE 1: insert into foo VALUES (1, 't');
Bashmomentje CREATE TABLE foo (gender enum('m','f','f','f','f')); Query OK, 0 rows affected, 3 warnings (0.15 sec) INSERT INTO foo VALUES ('f'); Query OK, 1 row affected (0.06 sec) ALTER TABLE foo CHANGE COLUMN gender gender enum ('m'); Query OK, 1 row affected, 1 warning (0.44 sec) Records: 1 Duplicates: 0 Warnings: 1 select * from foo; ++ | gender | ++ | | ++ 1 row in set (0.00 sec)
CHECK constraint Controle op de inhoud van een veld. Queries falen als ze op welke manier dan ook de CHECK overtreden. CREATE TABLE products ( product_no integer, name text, price NUMERIC CHECK (price > 0) ); INSERT INTO products (product_no, name, price) VALUES (1, 'schepje', 0); ERROR: new row for relation "products" violates check constraint "products_price_check"
CHECK constraint CREATE TABLE products ( product_no integer, name text, price NUMERIC CHECK (price > 0), price_discount NUMERIC CONSTRAINT discount_check CHECK (price_discount >= 0 AND price_discount <= price) ); insert into products values (1,'foo', 1.44, 3.14; ERROR: new row for relation "products" violates check constraint "discount_check"
Advisory Locks Locks op app niveau, maar beheerd door de database. Geen race conditions. Locks worden vrijgegeven als de verbinding sluit. De app moet zelf op locks controleren.
Advisory Locks Verbinding A select pg_advisory_lock(1972); → success ...strutt... ...your... ...funky... ...stuff... select pg_advisory_unlock(1972); → success
Verbinding B select pg_advisory_lock(1972); → waiting → waiting → waiting → waiting → waiting → waiting → waiting → success
Advisory Locks Verbinding A select pg_advisory_lock(1972); → success ...strutt... ...your... ...funky... ...stuff... select pg_advisory_unlock(1972); → success
Verbinding B select pg_try_advisory_lock(1972); → false
Pret met Indexes
Partieële indexes Indexeert alleen records die aan een voorwaarde voldoen. Minder records in de index Sneller zoeken Sneller insert/update/delete
Chuck Norris made the white boy play that funky music.
Partieële indexes CREATE INDEX producten_beschikbaar_idx ON producten (productid, prijs) WHERE voorraad > 0;
Partieële indexes CREATE INDEX apache_log_2012_idx ON apache_log (hit_timestamp) WHERE EXRACT(YEAR FROM hit_timestamp) = 2012; CREATE INDEX apache_log_2012_01_idx ON apache_log (hit_timestamp) WHERE hit_timestamp BETWEEN '20120101' AND '20120131';
Partieële indexes CREATE TABLE indextest (id integer, val INTEGER, foo TEXT, bar TEXT); INSERT INTO indextest SELECT x , (1222*RANDOM())::INT , RANDOM()::TEXT , RANDOM()::TEXT FROM generate_series(1,1000000) AS x;
Partieële indexes EXPLAIN ANALYSE SELECT * FROM indextest WHERE val=3; "Seq Scan on indextest (cost=0.00..17860.00 rows=3572 width=72) (actual time=0.136..174.686 rows=776 loops=1)" " Filter: (val = 3)" "Total runtime: 174.880 ms" CREATE INDEX idx_val ON indextest (id, val); EXPLAIN ANALYSE SELECT * FROM indextest WHERE val=3; "Index Scan using idx_val on indextest (cost=0.00..19820.96 rows=791 width=42) (actual time=0.091..44.905 rows=776 loops=1)" " Index Cond: (val = 3)" "Total runtime: 45.143 ms" CREATE INDEX idx_part_val ON indextest (id,val) WHERE val=3; EXPLAIN ANALYSE SELECT * FROM indextest WHERE val=3; "Index Scan using idx_part_val on indextest (cost=0.00..1364.91 rows=790 width=44) (actual time=0.074..2.013 rows=776 loops=1)" " Index Cond: (val = 3)" "Total runtime: 2.434 ms"
Functionele indexes Indexeert bewerkte kolomdata Maak het mogelijk om te zoeken op complexe bewerkingen.
Functionele indexes CREATE INDEX adressen_postcode_idx ON adressen (SUBSTRING(postcode, 0, 4)); SELECT * FROM adressen WHERE SUBSTRING(postcode, 0, 4) = '1065';
Functionele indexes CREATE TEMPORARY TABLE xmldata (x_id serial,x XML); INSERT INTO xmldata (x) SELECT ('
foo
'::xml) FROM GENERATE_SERIES(1,10000); CREATE INDEX xmldata_idx ON xmldata USING BTREE(((XPATH('/data/row/text()',x))[1]::TEXT)); EXPLAIN SELECT * FROM xmldata WHERE (XPATH('/data/row/text()',x))[1]::TEXT = 'bar' limit 1; Limit (cost=0.00..3.15 rows=1 width=36) > Index Scan using xmldata_idx on xmldata (cost=0.00..157.13 rows=50 width=36) Index Cond: (((xpath('/data/row/text()'::text, x, '{}'::text[])) [1])::text = 'bar'::text)
Partieel functioneel Unique constaint op een deel van een string: CREATE TABLE zipper (zipcode char(6)); INSERT INTO zipper (zipcode) VALUES ('1234AA'); CREATE UNIQUE INDEX aa_only ON zipper (SUBSTRING(zipcode,5,2)); INSERT INTO zipper (zipcode) VALUES ('8765AA'); ERROR: duplicate key value violates unique constraint "aa_only" DETAIL: Key ("substring"(zipcode::text, 4, 2))=(AA) already exists.
Document based meuk
HStore Simpele key/value store Operators voor mutatie en bevraging. Indexeerbaar via GiST en GiN!
SELECT 'price=>3.14, cat=>42, fabric=>Leather'::hstore; hstore "cat"=>"42", "price"=>"3.14", "fabric"=>"Leather" (1 row)
Chuck Norris once made a Happy Meal sad.
HStore CREATE TEMPORARY TABLE products (id INT, metadata HSTORE); INSERT INTO products (id, metadata) VALUES (22, 'price=>3.14, cat=>42, fabric=>Leather'::hstore); SELECT * FROM products WHERE metadata>'fabric' = 'Leather'; id | metadata + 22 | "cat"=>"42", "price"=>"3.14", "fabric"=>"Leather" (1 row)
Hstore operators Bevat de hstore een bepaalde key? SELECT (('naam'=>'waarde')::hstore) ? 'naam'; Bevat de hstore alle inhoud van een andere hstore? SELECT (('foo=>bar,mysql=>sucks,pgsql=>rules')::hstore) @> ('mysql=>sucks,foo=>bar')::hstore; Key verwijderen: SELECT 'foo=>bar,mysql=>sucks,pgsql=>rules'::hstore – 'mysql'::text; Hstore naar records SELECT * FROM each(('foo=>bar,tea=>cup'::text)::hstore) ; key | value foo | bar tea | cup
XML Genereren SELECT XMLELEMENT(NAME adres , XMLATTRIBUTES(53 AS adresid) , XMLELEMENT(NAME straat, 'Snorkelweg') , XMLELEMENT(NAME postcode, '1495AD') );
<straat>Snorkelweg <postcode>1495AD "
XML Genereren CREATE TABLE products (id int, title varchar(100), price NUMERIC(10,2) ); INSERT INTO products values (16, 'Funky Cold Medina', 19.89); INSERT INTO products values (17, 'Shefafa', 19.45);
XML Genereren SELECT XMLELEMENT(NAME products , XMLATTRIBUTES(COUNT(*) AS numproducts) , XMLAGG( XMLELEMENT(NAME product, XMLFOREST(title, price) ) ) ) FROM products; <products numproducts="2"> <product> Funky Cold Medina <price>19.89 <product> Shefafa <price>19.45
XML Parsen WITH data AS (SELECT '<products numproducts="2"> <product> Funky Cold Medina <price>19.89 <product> Shefafa <price>19.45 '::xml as x) SELECT xpath('/products/product', x) FROM data; {"<product> Funky Cold Medina <price>19.89 ","<product> Shefafa <price>19.45 "}
XML Parsen WITH data AS (SELECT '<products numproducts="2"> <product> Funky Cold Medina <price>19.89 <product> Shefafa <price>19.45 '::xml as x) ,products AS (SELECT UNNEST(xpath('/products/product', x)) AS product FROM data) SELECT (xpath('title/text()', product))[1] AS title , (xpath('price/text()', product))[1] AS price FROM products;
XML Parsen xpath | xpath + Funky Cold Medina | 19.89 Shefafa | 19.45 (2 rows)
XML Transformeren WITH data AS (SELECT '<products numproducts="2"> …. '::text as x) SELECT XSLT_PROCESS( x, ' <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.w3.org/1999/xhtml"> <xsl:output method="html"/> <xsl:template match="/products/product"> <xsl:valueof select="title/text()"/>
' ) FROM data;
XML Transformeren Funky Cold Medina
Shefafa
JSON SELECT * FROM demo ; username | posts | emailaddress ++ john | 121 | [email protected] mickael | 215 | [email protected] (2 rows) SELECT row_to_json(demo) FROM demo; row_to_json {"username":"john","posts":121,"emailaddress":"john@nowher e.com"} {"username":"mickael","posts":215,"emailaddress":"mickael@ nowhere.com"} (2 rows)
Uitbreidsels
Foreign data wrappers Bevraag externe bronnen alsof het tabellen zijn. CSV MySQL Twitter Amazon S3 Webservices CouchDb
File_FDW CREATE FOREIGN TABLE passwd ( username text, pass text, uid int4, gid int4, gecos text, home text, shell text ) SERVER file_server OPTIONS (format 'text', filename '/etc/passwd', delimiter ':', null ''); select * from passwd order by uid asc limit 3; username | pass | uid | gid | gecos | home | shell ++++++ root | x | 0 | 0 | root | /root | /bin/bash daemon | x | 1 | 1 | daemon | /usr/sbin | /bin/sh bin | x | 2 | 2 | bin | /bin | /bin/sh (3 rows)
earth_distance SELECT earth_distance( ll_to_earth('4.892222', '52.373056'), ll_to_earth('5.119444', '52.093333') ); Amsterdam-Utrecht: 40025.5422070672 Meter.
Chuck Norris doesn’t believe in Germany.
Geo Meuk SELECT earth_distance( ll_to_earth('4.892222', '52.373056'), ll_to_earth('5.119444', '52.093333') ); Amsterdam-Utrecht: 40025.5422070672 Meter.
Chuck Norris doesn’t believe in Germany.
Custom aggregates CREATE OR REPLACE FUNCTION "public"."_group_concat_delimiter" (text, text, text) RETURNS text AS $body$ SELECT CASE WHEN $2 IS NULL THEN $1 WHEN $1 IS NULL THEN $2 ELSE $1 operator(pg_catalog.||) $3 operator(pg_catalog.||) $2 END $body$ LANGUAGE 'sql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER; CREATE AGGREGATE "public"."group_concat_delimiter" (text, text) ( SFUNC = "public"."_group_concat_delimiter", STYPE = "text"); SELECT group_concat_delimiter(kolomnaam, 'scheidingsstring') FROM tabel GROUP BY veldnaam;
Stored functions PL/SQL PL/PgSQL PL/Perl PL/Java PL/Python PL/sh PL/Ruby PL/R Heftige statistieken met grafische representatie
C Shared objects, Schnell!
PL/pgSQL CREATE OR REPLACE FUNCTION foo() RETURNS integer AS $BODY$ BEGIN RETURN 5; END $BODY$ LANGUAGE 'plpgsql';
PL/pgSQL CREATE TABLE foo (title VARCHAR, price NUMERIC(6,2)); CREATE OR REPLACE FUNCTION loadxml(xmldata XML) RETURNS integer AS $BODY$ DECLARE numrows INTEGER; BEGIN WITH products AS (SELECT UNNEST(xpath('/products/product', xmldata)) AS product) INSERT INTO foo (title, price) SELECT (xpath('title/text()', product))[1]::TEXT AS , (xpath('price/text()', product))[1]::TEXT::NUMERIC FROM products; GET DIAGNOSTICS numrows = ROW_COUNT; RETURN numrows; END $BODY$ LANGUAGE 'plpgsql';
PL/pgSQL SELECT loadxml('<products numproducts="2"> <product> Funky Cold Medina <price>19.89 <product> Shefafa <price>19.45 '::xml); loadxml 2 SELECT * FROM foo; title | price + Funky Cold Medina | 19.89 Shefafa | 19.45
PL/pgSQL Exceptions CREATE OR REPLACE FUNCTION foo() RETURNS INTEGER AS $BODY$ BEGIN SELECT 1/0; EXCEPTION WHEN division_by_zero THEN RAISE NOTICE 'Alleen Chuck Norris kan delen door nul.'; RETURN 0; END $BODY$ LANGUAGE 'plpgsql' ; SELECT foo();
Chuck Norris doesn’t use Try-Catch,
exceptions are too afraid to raise.
Variadic functions Variabel aantal parameters. CREATE OR REPLACE FUNCTION elt(int, VARIADIC text[]) RETURNS text AS $$ SELECT $2[$1]; $$ LANGUAGE sql; SELECT elt(1, 'hallo','allemaal'); hallo (1 row) SELECT elt(2, 'hallo','allemaal','hier','tezaam'); allemaal (1 row)
Chuck Norris can touch MC Hammer.
Overige uitbreidingen PostGIS Nauwkeurige “spatial” en geografische functies.
pg_trgm Trigram indexes kunnen worden gebruikt door LIKE '%foo%'.
pg_stat_statements Logt queries met stats over runtime, cachegebruik etc.
auto_explain Logt de explain van queries.
Beheer en performance
Tablespaces Een tablespace is een locatie in het filesysteem waar tabellen en indexes in kunnen worden. Per database kunnen meerdere tablespaces worden gemaakt Netto effect: de hoeveelheid diskactiviteit kan worden uitgebreid voor specifieke tabellen en/of indexes. Dure kleine snelle SSD voor indexes BBU array voor veelgebruikte data Grote goedkope RAID voor weiniggebruikte data
High availability Streaming replication, synchroon en asynchroon PgPool Connection pooling Replication Load balancing Parallel query (1 query verdelen over n- servers)
PlProxy Parallel query vanuit stored procedures.
pg_stat_activity SELECT datname , usename , client_addr , waiting , query_start FROM pg_stat_activity; datname | usename | client_addr | waiting | query_start ++++ postgres | www | 127.0.0.1 | f | 20120912 19:34:04.6111 speeltuin | www | 127.0.0.1 | f | 20120912 19:34:04.5315 speeltuin | www | 127.0.0.1 | f | 20120912 20:57:58.0889 speeltuin | vinny | | f | 20120912 20:59:05.2395
Explain EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000; QUERY PLAN Bitmap Heap Scan on tenk1 (cost=11.27..49.11 rows=11 width=244) Recheck Cond: ((unique1 < 100) AND (unique2 > 9000)) > BitmapAnd (cost=11.27..11.27 rows=11 width=0) > Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0) Index Cond: (unique1 < 100) > Bitmap Index Scan on tenk1_unique2 (cost=0.00..8.65 rows=1042 width=0) Index Cond: (unique2 > 9000)
Rechten Aanpassingen werken onmiddelijk geen flush privileges nodig
Een ROLE kan ook runtime instellingen bepalen ALTER ROLE myname SET enable_indexscan TO off;
Kolomrechten CREATE USER mrnaughty WITH ENCRYPTED PASSWORD 'n'; CREATE TABLE safetable (id integer, secret integer); REVOKE ALL ON safetable FROM mrnaughty; GRANT SELECT (id) ON TABLE safetable TO mrnaughty;
Kolomrechten SELECT * FROM safetable; ERROR: permission denied for relation safetable select id FROM safetable; id (0 rows)
COMMIT;
All your base are belong to Chuck Norris.
