Transcript playserengeti.googlecode.com

What is PostgreSQL?





Object-relational database management system
(ORDBMS)
Based on POSTGRES at Berkeley Computer
Science Department.
Sponsored by the Defense Advanced Research
Projects Agency (DARPA), the Army Research
Office (ARO), the National Science Foundation
(NSF), and ESL, Inc.
Implementation of POSTGRES began in 1986.
It used a query language based on QUEL,
called POST-QUEL
PostgreSQL



Aimed to be a fullyfeatured database
Understands a good
subset of SQL92/99
dialects
Rules, triggers,
server-side functions
can be written in C,
PgSQL, Python, Perl
and TCL
MySQL



Aimed first to be a
fast database
Uses SQL92 as its
foundation
Has simple
mechanism for
server-side libraries
with C function and
rudimentary support
for triggers
PostgreSQL


Supports subqueries,
stored procedures,
subqueries, cursors
or views
Supports referential
integrity, has
transactions and
rollbacks, foreign
keys ON DELETE
CASCADE and ON
UPDATE CASCADE
MySQL


Does not support
subqueries, stored
procedures,
subqueries, cursors
or views
Has basic provisions
for referential integrity
and
transactions/rollbacks
PostgreSQL


Doesn't have binary
distribution for all the
support platforms
Slower on low-end
but has some options
for improving
MySQL


Works better on
Windows
Fast on both simple
and complex
SELECTs
Roles and Databases
Roles and Databases
#!/bin/bash
for student in $@
do
echo "CREATE ROLE $student WITH LOGIN;" | psql
echo "CREATE DATABASE $student with OWNER $student;" | psql
done
Creation Alternatives

createdb

psql --command //can't mix of psql and sql

psql --file

psql < filename
Other Command Line Options

--hostname

--username

--password //force password prompt

psql database username //default
Environment Variables

PGDATABASE

PGHOST

PGPORT

PGUSER
Useful Commands


\connect
\d [ pattern ] //list table, view, index, or
sequence

\dn [ pattern ] //list schemas

\du [ pattern ] //list all roles

\i filename //excute from file

\l //list databases
Useful Commands




\o [ {filename | |command} ] //Saves future
query results to the file filename, standard
output or pipes result to the Unix command
\s [ filename ] //Saves the history to file or to
standard output
\set [ name [ value [ ... ] ] ] //Defines a variable
\! [ command ] //Executes command on a Unix
shell

\? //Help

the “+”
Sources

http://www.postgresql.org/docs/8.1

psql man page

Google "Postgres versus MySQL"