PostgreSQL

1
PostgreSQL

• History
• Homework objectives
• Using PostgreSQL _at_ TCU

2

• From Ingres to PostgreSQL

(fine breed)
Commercial
3
"Genius is 1 inspiration and 99 perspiration."
Thomas Alva Edison (1847-1931)
Source http//www.thomasedison.com/
Homework Objectives
Persistence From
Latin __________ per- "thoroughly and
sistere maintain existence Be there try
continuously -) Be courageous! Whatever
setbacks America has encountered, it has always
emerged as a stronger and more prosperous
nation.... Thomas Alva Edison
Patience

• Patented 1,100 inventions in 60 years
• Tested Over 3000 Filaments before he came Up with
  His version Of a Practical Light Bulb

4
Using PosgreSQL at TCU

• Login in to Riogrande and psql
• sanchez ssh riogrande.cs.tcu.edu
• sanchez_at_riogrande.cs.tcu.edu's password
• Last login Mon Oct 10 195457 2005 from
  tt3322.mat.tcu.edu
• sanchez_at_riogrande psql
• Welcome to psql 8.0.3, the PostgreSQL interactive
  terminal.
• Use a template1 the first time
• sanchez_at_riogrande psql

5
Using PosgreSQL at TCU

• Help command in terminal psql
• \h for SQL commands
• CREATE database index table trigger
  schema rule ,
• COMMENT,
• COMMIT, ROLLBACK
• INSERT,
• SELECT, SELECT INTO
• FETCH
• \? For system commands (i.e. Non SQL)
• \q quit psql
• \connect DBNAME- USER
• connect to new database
  (currently "sanchez")
• Create a database
• sanchez- CREATE DATABASE
• Fittings database
• Employee database

6
Using PosgreSQL

• Insert data
• 10 rows per table
• Do some basic SQL
• SELECT FROM

7
Creating Tables

• CREATE GLOBAL LOCAL TEMPORARY TEMP
  TABLE table_name ( column_name data_type
  DEFAULT default_expr column_constraint ...
  table_constraint LIKE parent_table
  INCLUDING EXCLUDING DEFAULTS , ...
  ) INHERITS ( parent_table , ... ) WITH
  OIDS WITHOUT OIDS ON COMMIT PRESERVE ROWS
  DELETE ROWS DROP
• where column_constraint is
• CONSTRAINT constraint_name NOT NULL
  NULL UNIQUE PRIMARY KEY CHECK (expression)
  REFERENCES reftable ( refcolumn ) MATCH
  FULL MATCH PARTIAL MATCH SIMPLE ON
  DELETE action ON UPDATE action
  DEFERRABLE NOT DEFERRABLE INITIALLY
  DEFERRED INITIALLY IMMEDIATE

Sources http//www.cs.nott.ac.uk/TSG/manuals/dat
abases/postgres/tutorial/ http//www.postgresql.or
g/docs/7.4/interactive/sql-createtable.html http/
/archives.postgresql.org/pgsql-docs/2001-04/msg000
69.php
8
Using SELECT

• SELECT ALLDISTINCT
• expr_1 AS c_alias_1 , ...
  , expr_k AS c_alias_k
• FROM table_name_1 t_alias_1 , ...
  , table_name_n t_alias_n
• WHERE condition
• GROUP BY name_of_attr_i ,...
  , name_of_attr_j
• HAVING condition
• UNION ALL INTERSECT EXCEPT SELECT
  ... ORDER BY name_of_attr_i ASCDESC

Sources http//www.cs.nott.ac.uk/TSG/manuals/dat
abases/postgres/tutorial/ http//www.postgresql.or
g/docs/7.4/interactive/sql-createtable.html http/
/archives.postgresql.org/pgsql-docs/2001-04/msg000
69.php