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ür den Aufstieg nö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ö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ö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ür diesen Einsatz bereits reserviert';
		END IF;
		RAISE EXCEPTION 'Diese Mannschaft fährt bereits mit einem Fahrzeug zu dem gewä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();
}