Send the link below via email or IMCopy
Present to your audienceStart remote presentation
- Invited audience members will follow you as you navigate and present
- People invited to a presentation do not need a Prezi account
- This link expires 10 minutes after you close the presentation
- A maximum of 30 users can follow your presentation
- Learn more about this feature in our knowledge base article
5 Cool Things About PostgreSQL
Transcript of 5 Cool Things About PostgreSQL
of the PostgreSQL Project Operators and
Regular Expressions Views With Recursive PostgreSQL v9.1 a. The project has been around since 1986.
That's 25 years.
By comparison, Apache has been around since
1996. GCC, since 1988. GNU Emacs has been active
since April of 1985.
b. PostgreSQL is licensed under a BSD or MIT style license.
Here it is:
PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)
Portions Copyright (c) 1996-2010, The PostgreSQL Global Development Group
Portions Copyright (c) 1994, The Regents of the University of California
Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. c. The PostgreSQL project falls under a not-for-profit foundation called the "PostgreSQL Global Development Group".
It's made up of a federation of companies who advocate for PostgreSQL as free software. Some of the core companies
include PsotgreSQL Experts Inc. in San Francisco, F-Secure in Helsinki, Redpoll Linpro in Stockholm, Red Hat in Pittsburgh,
and Enterprise DB in Philadelphia. These companies have unmatched reputations with PostgreSQL of course.
I call this a "community open source business model".
Past contributors include Julian Assange of Wikileaks. He also has a reputation.
I think the extended PostgreSQL development team look a lot like a BarCamp crew. d. PostgreSQL is big enough for some pretty impressive applications. a. IS
expression IS NULL
expression IS NOT NULL
expression IS TRUE
expression IS NOT TRUE
expression IS FALSE
expression IS NOT FALSE
expression IS UNKNOWN
expression IS NOT UNKNOWN
expression IS DISTINCT FROM expression
expression IS NOT DISTINCT FROM expression b. Cool math operators
|/ square root
|// cube root
@ absolute value
c. Cool string operators
string || string : concatenate
lower(), upper() : change case
encode(), decode() : base64 and hex
md5() : hash
translate() : like tr/
d. Pattern matching and regular expressions
LIKE and ILIKE (a.k.a. ~~ and ~~*)
NOT LIKE and NOT ILIKE (!~~ and !~~*)
_ and % match single and multiple wildcard
SIMILAR TO uses regular expressions
to match strings
~ and ~* Match regular expression (*case insensitive)
!~ and !~* Don't match regular expression
regexp_match() and regexp_replace(), greedy and non-greedy
regexp_split_to_table() and regexp_split_to_array()
[ ... ] and [^ ... ] for sets of letters create table parties (
); create table addresses (
); create table phones (
); create view contact as
p.name as name,
a.street || ', ' || a.city || ', ' || a.state || ' ' || a.zip as address,
t.phone_number as phone
from parties p, addresses a, phones t
where p.id = a.party_id and p.id=t.party_id;
copy (select * from contact) to STDOUT with CSV;
Rob Martin,"706 S 22nd St, Milwaukee, Wisconsin 53204",414-678-1822
Barbara Handley,"706 S 22nd St, Milwaukee, Wisconsin 53204",414-678-1329
Amanda F Palmer,"1 Bitter Lane, Coldness, Minnesota ",unlisted
Neil Gaiman,"1 Bitter Lane, Coldness, Minnesota ",unlisted
Please excuse the copy (select ...). Prezi doesn't handle tabular data well. } create table ia (
); insert into ia values (...);
insert into ia values (...);
insert into ia values (...); copy (select * from ia) to stdout with csv;
8,7,About Our Founder
9,8,About Our Founder's Ego
Time: 0.361 ms create view crumbs as
with recursive page(id, long) as (
select ia.id, ia.page::text as long from ia where id = 1
select ia.id, page.long || ' / '::text || ia.page::text from ia, page where ia.parent_id = page.id
select page.id, page.long from page; copy (select * from crumbs) to stdout with csv;
2,Home / Products
3,Home / Services
4,Home / About
6,Home / Site Map
7,Home / About / About us
8,Home / About / About us / About Our Founder
9,Home / About / About us / About Our Founder / About Our Founder's Ego
Time: 0.755 ms
> > } a. Synchronous replication
You can require, on a per transaction basis (i.e. when you damn well feel like it)
that PostgreSQL consider a commit complete only when it's been written to the
master database plus one slave. b. Unlogged tables
When the data is ephemeral but too big for RAM, you can put it in a new "unlogged table".
create table myloggedtable (a int);
create unlogged table myunloggedtable (a int);
create index x_logged on myloggedtable(a);
create index x_unlogged on myunloggedtable(a);
insert into myloggedtable select generate_series(1,10000000);
Time: 17601.201 ms
insert into myunloggedtable select generate_series(1,10000000);
Time: 3439.982 ms
Full integration with SE-Linux for military-grade security.
d. Writeable Common Table Expressions
'WITH' gets more powerful:
with deleted as (
delete from mytable where words like '%yellow%' returning words
) insert into myarchivetable (select * from deleted); e. Stuff gets faster, some of it anyway
create table test1 (a int);
create table test2 (a int);
insert into test1 select generate_series(1,100000);
insert into test2 select generate_series(100,1000);
select * from test1 full outer join test2 using (a) limit 10
PostgreSQL 9.0: 330 ms
PostgreSQL 9.1: 3 ms f. Some of the more esoteric stuff
Per column collations: You can choose a linguistically-correct sort collation on a
per-column basis rather than per-database.
Serializable Snapshot Isolation: Keeps concurrent transactions consistent without blocking.
K-Nearest-Neighbor Indexing: Blazingly fast pattern and distance matching using the KNN algorithm.
This has some spillover benefit to LIKE and ILIKE also.
Foreign Data Wrappers: Attach and query other databases from PostgreSQL.