3. July 2013
von Blackbam

This article on PostgreSQL is dedicated to my university course about database system, which I had to absolve this year. This post maybe useful as a quick syntactic introduction to PostgreSQL. It contains some useful examples and structures. This is not a complete tutorial. For really learning
PostgreSQL you should consider reading the official documentation.

 

1. Data definition and manipulation

This part of the tutorial will show you the syntax of creating, changing and deleting tables in PostgreSQL. It will also show you how
to insert, change and delete data in tables.

1.1 Creating Sequences

A sequence is an incrementing or decrementing row of numbers, which may be used to create specially structed primary keys
but also just any other number sequence you may want to use when inserting data. To use a sequence you have created
you usually use the the following function inside of an insert statement: nextval('sequence_name').

 
 -- Creates a sequence like this: 10,20,30, ...
CREATE SEQUENCE seq_mannschaft INCREMENT BY 10 MINVALUE 10 NO MAXVALUE START WITH 10 NO CYCLE;
-- Increment by: Size of the steps
-- Minvalue: Starting value of the sequence
-- Maxvalue: Maximum value of the sequence
-- Cycle: Sequence will start with MINVALUE again when MAXVALUE is reached

-- Creates a simple sequence: 1,2,3 ...
CREATE SEQUENCE seq_employee;

1.2 Creating Tables (DDL)

 
-- Most important types
CREATE TABLE Car (
 id SERIAL PRIMARY KEY, -- good way to create a primary key is serial
 petrol_consumption INTEGER, -- a normal integer
 model VARCHAR(128), -- varying characters (maximum length)
 cost NUMERIC(7,2), -- a numeric type
 has_golden_doors BOOLEAN, -- boolean type
 bought_at DATE, -- saves date Year, Month, Day: See date functions
 buytime TIMESTAMP -- saves time in microseconds: See date functions
);

-- Create a subtype
CREATE TABLE Elite_Car (
   id INTEGER PRIMARY KEY REFERENCES Car(id), -- creates a subtype
   num_of_speicals INTEGER NOT NULL
);
 
-- Most important constraints
CREATE TABLE Employee (
   id INTEGER PRIMARY KEY DEFAULT NEXTVAL('seq_employee'), -- use a defined sequence
   earns NUMERIC(7,2) CHECK (gehalt >= 0), -- income must be positive
   room_number INTEGER CHECK (stockwerk BETWEEN 1 AND 500), -- check range for numbers
   name NOT NULL, -- every employee must have a name
   department INTEGER -- foreign key deferred
);

CREATE TABLE Department (
 id SERIAL PRIMARY KEY,
 name VARCHAR(128),
 boss INTEGER
);

-- Adding constraints afterwards, especially: Cyclic Constraints
ALTER TABLE Employee ADD CONSTRAINT fk_department
 FOREIGN KEY (department) REFERENCES Department(id) DEFERRABLE INITIALLY DEFERRED;
 
ALTER TABLE Department ADD CONSTRAINT fk_boss
 FOREIGN KEY (boss) REFERENCES Employee(id) DEFERRABLE INITIALLY DEFERRED;
 
CREATE TABLE In_Conflict(
 employee1 INTEGER REFERENCES Employee(id),
 employee2 INTEGER REFERENCES Employee(id),
 PRIMARY KEY (employee1,employee2) -- create primary key from multiple foreign keys
);

1.3 Inserting Data (DML)

-- To guarantee that a whole transaction will be done or completly canceled: Begin, Commit
BEGIN;

INSERT INTO Car(petrol_consumption,model,cost,has_golden_doors,bought_at,buytime) VALUES (6,'Audi',20000.00,false,'2012-02-19','2012-02-19 14:48');
INSERT INTO Car(petrol_consumption,model,cost,has_golden_doors,bought_at,buytime) VALUES (7,'Opel',10000.00,true,'2011-02-19','2012-02-19 14:48');

COMMIT;

1.4 Updating data (DML)

 
UPDATE Car SET petrol_consumption = 12 WHERE model = 'Audi'; -- Update Data

1.5 Deleting data (DML)

 
DELETE FROM Car; -- deletes everything in car
DELETE FROM Car WHERE model='Audi' AND has_golden_doors=true; -- will not delete anything

1.6 Removing Tables, Sequences, Constraints (DDL)

 
DROP TABLE In_Conflict;
ALTER TABLE Department DROP CONSTRAINT fk_boss;
DROP TABLE Employee;
DROP TABLE Department;
DROP TABLE Elite_Car;
DROP TABLE Car CASCADE; -- Cascade (makes no sense here) will delete everything else which must be deleted, before this table should be deleted (so use with care)

2. Querying data and complex queries

This part of the tutorial will be an introduction to complex queries in PostgreSQL. It will show the syntax and some examples.

2.1 Built in Functions and Query Structure

Built in functions like these inside of queries can be used to perform simple mathematical operations, convert strings to dates, and more...

 
|| -- string concetination
round(),sqrt() -- mathematical
to_char(),to_number() -- conversion function
to_date(), CURRENT_DATE -- date functions, CURRENT_DATE is a "constant" which will show the current date
to_char(date,'MONTH DD,YYYY'); -- date to char
EXTRACT(YEAR FROM My_Time) AS My_Year --- the EXTRACT keyword is used for getting parts of a timestamp for example

2.2 Recursive Queries

Recursive queries, quite a new feature of PostgreSQL, make it possible for a query to refer to its own output tables. The following
examples will demonstrate how it works.

This example was found at http://jakub.fedyczak.net/post/42/ , and I think it is a good demonstration of how recursive queries works.

 
-- create a table
BEGIN;
CREATE TABLE empl (
    name text PRIMARY KEY,
    boss text DEFAULT NULL
);

-- insert data
INSERT INTO empl VALUES ('Paul',NULL);
INSERT INTO empl VALUES ('Luke','Paul');
INSERT INTO empl VALUES ('Kate','Paul');
INSERT INTO empl VALUES ('Marge','Kate');
INSERT INTO empl VALUES ('Edith','Kate');
INSERT INTO empl VALUES ('Pam','Kate');
INSERT INTO empl VALUES ('Carol','Luke');
INSERT INTO empl VALUES ('John','Luke');
INSERT INTO empl VALUES ('Jack','Carol');
INSERT INTO empl VALUES ('Alex','Carol');
COMMIT;

-- do a recursive query, to determine the "boss path" for each employee
WITH RECURSIVE t(level,path,boss,name) AS (
        SELECT 0,name,boss,name FROM empl WHERE boss IS NULL
    union ALL
        SELECT
            level + 1,
            path || ' > ' || empl.name,
            empl.boss,
            empl.name
        FROM
            empl JOIN t
                ON empl.boss = t.name
) SELECT * FROM t ORDER BY path;

-- result
/*
 level |            path            | boss  | name
-------+----------------------------+-------+-------
     0 | Paul                       |       | Paul
     1 | Paul > Kate                | Paul  | Kate
     2 | Paul > Kate > Edith        | Kate  | Edith
     2 | Paul > Kate > Marge        | Kate  | Marge
     2 | Paul > Kate > Pam          | Kate  | Pam
     1 | Paul > Luke                | Paul  | Luke
     2 | Paul > Luke > Carol        | Luke  | Carol
     3 | Paul > Luke > Carol > Alex | Carol | Alex
     3 | Paul > Luke > Carol > Jack | Carol | Jack
     2 | Paul > Luke > John         | Luke  | John
(10 lines)
*/

This is another example on recursive queries, which is intended to show how it works.

/* Table:

 follower | follows
----------+---------
 a        | b
 a        | c
 a        | e
 b        | a
 b        | c
 b        | d
 b        | e
 c        | e
 d        | b
 d        | e
(10 LINES)

*/

-- recursive query
WITH RECURSIVE temp(a,b) AS (
 SELECT * FROM follows
UNION ALL
 SELECT f1.follower, f2.follows
 FROM follows f1, follows f2
 WHERE f1.follows = f2.follower
)
SELECT * FROM temp;

-- result
/*
a | b
--+---
a | b
a | c
a | e
b | a
b | c
b | d
b | e
c | e
d | b
d | e
b | b
b | c
b | e
a | a
a | c
a | d
a | e
d | a
d | c
d | d
d | e
a | e
b | e
b | b
b | e

*/


-- recursive query with group / count
SELECT a, count(*) FROM temp
GROUP BY a ORDER BY a;

´/*
 a | count
---+-------
 a |     8
 b |    10
 c |     1
 d |     6
(4 Zeilen)
*/

2.3 Views

A view appears as a table, but in fact it is just the result of a query on one or many other tables.

 
-- Create a simple table and insert data
CREATE TABLE DATA(a int4, b int4);

INSERT INTO DATA VALUES(12, 23);
INSERT INTO DATA VALUES(3, 7);
 
 -- create a view on this table
CREATE VIEW view_data
 AS SELECT a*2 AS c, b*2 AS d
 FROM DATA;
 
/*

Result: SELECT * FROM view_data;

 c | d
----+----
 24 | 46
 6  | 14
(2 rows)

*/

2.4 Some little things to remember

 
-- normal WITH: for creating intermediate results (tables/relations) which may be used in a complex query
WITH audis AS (
 SELECT id,model FROM cars WHERE brand='Audi'
) SELECT DISTINCT model FROM audi;

-- connecting results with union:
UNION --> duplicates are eliminated
UNION ALL --> duplicates are not eliminated

-- Comparing multiple results with a single value. Example:
WHERE Semester >= ALL ( SELECT Semester FROM Studenten );
-- same as
WHERE Semester = ( SELECT max( Semester ) FROM Studenten );


-- GROUP BY ... HAVING Example with COUNT
-- How many books are stored in the database for each publisher?
SELECT COUNT(e.isbn) AS "number of books",
       p.name AS publisher
       FROM editions AS e
    INNER JOIN publishers AS p
            ON (e.publisher_id = p.id)
   GROUP BY p.name;

-- GROUP BY ... HAVING Example with SUM  
-- How many pages does each publisher have in all his books together?
SELECT SUM(e.num_of_pages) AS "number of pages",
       p.name AS publisher
       FROM editions AS e
    INNER JOIN publishers AS p
            ON (e.publisher_id = p.id)
   GROUP BY p.name;

2.5 Existential quantifier / Universal quantifier

There is an existential quantifier in PostgreSQL:

-- Existential quantifier (check if resulting table is empty)
NOT EXISTS

There is no universal quantifier in SQL, so this must be simulated. Just see the following example:

"Which students attended all lectures which last 4 hours?" is the same as
"Search all Students for which is not true: There is a 4 hour lecture, which this student did not hear".

 
SELECT s.*
FROM Students s
WHERE NOT EXISTS
 (SELECT *
 FROM Lectures l
 WHERE l.SWS = 4 AND
 s.MatrNr NOT IN ( SELECT a.MatrNr
  FROM attend a
  WHERE a.VorlNr = l.VorlNr ));

2.2 Some complex query examples (german)

/* Geben Sie ID, Rufname sowie die ID und den Nachnamen des Leiters jener Mannschaften aus, die bereits mit allen Bergefahrzeugen bei Einsätzen waren. */

SELECT ID, Rufname, Leiter, (SELECT Nachname FROM Person WHERE ID = Leiter) LeiterName
    FROM Mannschaft WHERE NOT EXISTS
        (SELECT * FROM Bergefahrzeug WHERE NOT EXISTS
            (SELECT * FROM Einsatz WHERE Einsatz.mannschaft = Mannschaft.ID AND Einsatz.fahrzeug = Bergefahrzeug.ID));



/*
Wählen Sie per Hand einen Dienstgrad aus, der anderen Dienstgraden untergeordnet ist. Schreiben Sie eine Anfrage, die diesen Dienstgrad ausgibt, sowie rekursiv alle übergeordneten Dienstgrade. Geben Sie für jeden Dienstgrad die ID, Beschreibung sowie das zugehörige Gehalt und den Gehaltsunterschied zum nächstniedrigen Dienstgrad aus. Sollte es keinen niedrigeren Dienstgrad geben, soll der Inhalt der Spalte 0 sein. Passen Sie die Tupel in Ihrer Datenbank so an, dass es zu der von Ihnen ausgewählten Kategorie mindestens zwei Ebenen übergeordneter Kategorien gibt. Achten Sie darauf, dass Ihre Daten keine Schleife enthalten, da ansonsten die Abfrage fehlschlägt.
*/


WITH RECURSIVE Hierarchie (ID, Bezeichnung, Vorgesetzter, Gehalt, Unterschied) AS (
 SELECT ID, Bezeichnung, Vorgesetzter, Gehalt, CAST(0.0 AS NUMERIC) FROM Dienstgrad WHERE ID = 8
 UNION ALL
 SELECT Dienstgrad.ID,
           Dienstgrad.Bezeichnung, Dienstgrad.Vorgesetzter,
           Dienstgrad.Gehalt, Dienstgrad.Gehalt - Hierarchie.Gehalt
     FROM Hierarchie JOIN Dienstgrad ON (Hierarchie.Vorgesetzter = Dienstgrad.ID)
)
SELECT ID, Bezeichnung, Gehalt, Unterschied FROM Hierarchie;



/*
Geben Sie die ID, Vor- und Nachname sowie Anzahl der Berichte der Personen aus, die an nicht mehr als drei Einsätzen teilgenommen haben und gleichzeitig die wenigsten Berichte verfasst haben. Vergessen Sie nicht darauf, dass im Falle, dass noch keine Berichte erstellt wurden auch die Personen ausgegeben werden, die keine Berichte verfasst haben.
*/


SELECT Person.ID, Vorname, Nachname, COUNT(bericht.id) FROM Person LEFT JOIN Bericht ON Bericht.Verfasser = Person.ID
    WHERE Person.ID IN (SELECT Person.ID FROM Person JOIN Einsatz ON Einsatz.Mannschaft = Person.Mannschaft
        GROUP BY Person.ID HAVING COUNT(*) <= 3) GROUP BY Person.ID, Vorname, Nachname
    HAVING COUNT(*) <= ALL
        (SELECT COUNT(*) FROM Person LEFT JOIN Bericht ON Bericht.Verfasser = Person.ID
            WHERE Person.ID IN (SELECT Person.ID FROM Person JOIN Einsatz ON Einsatz.Mannschaft = Person.Mannschaft
                GROUP BY Person.ID HAVING COUNT(*) <= 3) GROUP BY Person.ID, Vorname, Nachname);

3. Functions / Procedures and Triggers

This part of the article is about PL/pgSQL (Procedural Language/PostgreSQL) which is one possibility to extend some PostgreSQL database with advanced
functions and procedures, beyond the possibilities of standard SQL. PL/pgSQL is just one of many possibilities for this purpose.

3.1 Important constructs

 
-- creating query loops
FOR variable IN statement
LOOP
 -- each row can be accessed here
END LOOP;

-- an alternative to query loops
CURSOR, RECORD, REFCURSOR, Copying types;

3.2 Function Example

This is an example of a function in PL/pgSQL.

 
CREATE OR REPLACE FUNCTION p_erhoehe_dienstgrad(jahre INTEGER) RETURNS VOID AS $$
DECLARE
 sw_person Person%ROWTYPE;
 old_dienstgrad Dienstgrad%ROWTYPE;
 new_dienstgrad Dienstgrad%ROWTYPE;
BEGIN
 IF jahre < 1 THEN
  RAISE EXCEPTION 'Mindestens 1 Jahr f&uuml;r den Aufstieg n&ouml;tig.';
 END IF;
 
 FOR sw_person IN SELECT * FROM Person p
 LOOP
  SELECT * FROM Dienstgrad d INTO old_dienstgrad WHERE sw_person.dienstgrad = d.id;
 
  IF extract(year FROM age(sw_person.dienstgrad_seit)) > = jahre THEN

   IF old_dienstgrad.vorgesetzter IS NOT NULL THEN
    SELECT * FROM Dienstgrad INTO new_dienstgrad WHERE Dienstgrad.id = old_dienstgrad.vorgesetzter;
    UPDATE Person SET dienstgrad = new_dienstgrad.id, dienstgrad_seit = CURRENT_DATE WHERE Person.id = sw_person.id;
    RAISE NOTICE 'Die Person % % wurde bef&ouml;rdert vom % zum %.',sw_person.vorname, sw_person.nachname,old_dienstgrad.bezeichnung,new_dienstgrad.bezeichnung;
   ELSE
    RAISE NOTICE 'Die Person % % befindet sich bereits im h&ouml;chsten Dienstgrad % und ist daher nicht aufgestiegen.',sw_person.vorname,sw_person.nachname,old_dienstgrad.bezeichnung;
   END IF;
  ELSE
   RAISE NOTICE 'Die Person % % befindet sich nach wie vor im Dienstgrad %',sw_person.vorname,sw_person.nachname,old_dienstgrad.bezeichnung;
  END IF;
 END LOOP;
END
$$ LANGUAGE plpgsql;

3.3 Trigger Example

This is an example of a trigger in PostgreSQL. A trigger is a special function which can be "triggered" before / after inserting, updating or deleting data,
which can be used to control certain flows.

 
CREATE OR REPLACE FUNCTION check_einsatz_allowed() RETURNS TRIGGER AS $$
 DECLARE
 
 BEGIN
  IF NOT EXISTS (SELECT * FROM Einsatz e WHERE e.mannschaft = NEW.mannschaft AND e.ereignis = NEW.ereignis) THEN
   IF NOT EXISTS (SELECT * FROM Einsatz e WHERE e.ereignis = NEW.ereignis AND e.fahrzeug = NEW.fahrzeug) THEN
    RETURN NEW;
   END IF;
   RAISE EXCEPTION 'Dieses Fahrzeug ist f&uuml;r diesen Einsatz bereits reserviert';
  END IF;
  RAISE EXCEPTION 'Diese Mannschaft f&auml;hrt bereits mit einem Fahrzeug zu dem gew&auml;hlten Ereignis';
 END
$$ LANGUAGE plpgsql;

CREATE TRIGGER chk_einsatz
BEFORE INSERT ON Einsatz
FOR EACH ROW EXECUTE PROCEDURE check_einsatz_allowed();

4. JDBC

This part is about using PostgreSQL in combination with Java (JDBC driver). The following examples demonstrate how it basically works.

Zusammenfassung PostgreSQL

 

  1. Register driver
  2. Create connection (DriverManger.getConnection)
  3. Create a statement (f.e. Statement, PreparedStatement, CallableStatement)
  4. Give a SQL statement to a query or prepare with prepareStatement / prepareCall
  5. If using a preparedStatement / callableStatement: statement.setString(pos,value), Example: statement.setInt(1,1)
  6. ResultSet = Statement.executeQuery / executeUpdate
  7. Loop through results: (while (ResultSet.next()))
  8. Get a result inside this loop: ResultSet.getInt() / ResultSet.getString()
  9. Close all connections
 
try {
 Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
}

System.out.println("Found");

try {
 Connection c = DriverManager.getConnection("jdbc:postgresql://localhost/exp","dave","hallo123");
 
 
 //////////// Normal statements
 // advanced options: c.createStatement(ResultSet.CONCUR_UPDATABLE, ResultSet.TYPE_SCROLL_INSENSITIVE
 Statement stmt = c.createStatement();
 
 ResultSet rs = stmt.executeQuery("SELECT * FROM empl"); // could also be: executeUpdate()
 
 // Navigation: first(), last(), next(), previous(), beforeFirst(), beforeLast(), absolute(x), relative(x)
 while(rs.next()) {
  // other Methods: getString(), getFloat(), getDouble(), getDate(), getTimestamp()
  System.out.println("Name: "+rs.getString(1)+" Boss: "+rs.getString(2));
 
  // update: rs.updateFloat("Note",l_Note-1); rs.updateRow();
  // deletes: rs.deleteRow();
 }
 
 
 /////////// Prepared Statements
 PreparedStatement pstmt = c.prepareStatement("SELECT underbosses.name FROM empl as underbosses WHERE boss = ? ");
 
 pstmt.setString(1, "Paul");
 rs = pstmt.executeQuery();
 
 while(rs.next()) {
  System.out.println("Underboss: "+rs.getString(1));
 }
 
 
 /////////// Callable Statements
 CallableStatement cs = c.prepareCall("{call getBastards(?,?}");
 cs.setInt(1,4);
 cs.setString(2, "special");
 cs.registerOutParameter(2,Types.INTEGER);
 
 cs.executeQuery();
 int bastards = cs.getInt(2);
 
 rs.close();
 cs.close();
 stmt.close();
 pstmt.close();
 c.close();
 
} catch (SQLException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
}

 

Share

Dieser Eintrag wurde am 3. July 2013 um 0:00 in der Kategorie SQL veröffentlicht. You can book the comments for this article RSS 2.0. Feedback, discussion, commendation and critics are welcome: Write a comment or trackback.


Tags: , , , ,

No comments yet

Kommentare abonnieren (RSS) or URL Trackback

Leave a comment:

Current day month ye@r *