PostgreSQL

1. PostgreSQL • Introduction • Most Advanced Opensource ORDBMS • 1997 by University of California • Free source code and open std

2. PSQL Features • ORDBMS • Complex SQL Queries, Multi user support, MVCC, Query optimization , Inheritance and arrays • SQL Support – SQL 99 ( or) SQL 3 • Highly Extensible • User defined operations, functions, access methods and data types • Referential Integrity • Flexible API -Python, Perl , PHP , C, C++ • Procedural Languages • Client Server • Write Ahead Logging (WAL)

3. PostgreSQL Vs SQL Server

4. POSTGRESQL MYSQL ANSI SQL compliance Closer to ANSI SQL standard Follows some of the ANSI SQL standards Performance Slower Faster Sub-selects Yes No Transactions Yes Yes, however InnoDB table type must be used Database replication Yes Yes Foreign key support Yes No Views Yes No Stored procedures Yes No Triggers Yes No Unions Yes No Full joins Yes No Constraints Yes No Windows support Yes Yes Vacuum (cleanup) Yes No ODBC Yes Yes JDBC Yes Yes Different table types No Yes

5. PostgreSQL's Geometric Data Types Data Type Description box A geometric box, rectangle circle A circle defined by a point and a radius line A line defined by a starting point and an end point lseg A geometric line segment path A sequence of lines defined by points point A point defined by two coordinates polygon A polygon defined by a list of points

6. Data Type Internal Size Description cidr 12 bytes Stores valid Ipv4 network addresses inet 12 bytes Stores IP addresses or network addresses macaddr 6 bytes Stores MAC addresses • Working with Arrays • One-Dimensional Arrays • Multidimensional Arrays • Example: • CREATE TABLE parent (name_mother text, name_children text[2]); • INSERT INTO parent VALUES ('Andrea', '{"Paul", "Lisa"}');

7. Commands Purpose Example CREATE DATABASE Creates a new database in PostgreSQL CREATE DATABASE booktown; CREATE TABLE Creates a new table in an exiting database CREATE TABLE book; CREATE INDEX Creates new index on a table column CREATE UNIQUE INDEX employee_id_idx ON employees(id) CREATE SEQUENCE Creates a new sequence in an existing database CREATE SEQUENCE ship_id START 200 INCREMENT PostgreSQL Commands

8. Commands Purpose Example CREATE TRIGGER Creates a new trigger definition CREATE TRIGGER sync_authors_booksBEFORE UPDATEON authorsFOR EACH ROWEXECUTE PROCEDURE sync_authors_and_books(); CREATE VIEW Creates a new view on an existing table CREATE VIEW h_publishers AS SELECT * FROM publishers WHERE name LIKE 'H%‘; SELECT Retrieves records from a table SELECT * FROM books WHERE id > 5000; INSERT Adds one or more new records into a table INSERT INTO employees (id, last_name) VALUES (108, 'Williams'); UPDATE Modifies the data in existing table records UPDATE stock SET stock = stock + 1 WHERE isbn = '0385121679' ;

9. Commands Purpose Example DELETE Removes existing records from a table DELETE FROM shipments WHERE customer_id = 142 AND ship_date < '2001-08-07'; DROP DATABASE Destroys an existing database DROP DATABASE testdb; DROP INDEX Removes a column an index from an existing table DROP INDEX books_id_pkey, books_title_idx; DROP SEQUENCE Destroys an existing sequence generator DROP SEQUENCE shipments_ship_id_seq; DROP TABLE Destroys an existing table DROP TABLE employees; DROP TRIGGER Destroys an existing trigger definition DROP TRIGGER sync_authors_books ON authors;

10. Commands Purpose Example DROP VIEW Destroys an existing table view DROP VIEW h_publishers; CREATE USER Adds a new PostgreSQL user account to the system CREATE USER david WITH PASSWORD 'jw8s0F4' CREATEDB IN GROUP accounting VALID UNTIL 'Jan 1 2005' ; ALTER USER Modifies an existing PostgreSQL user account ALTER USER mark WITH PASSWORD 'ml0215em‘; DROP USER Removes an existing PostgrSQL user account DROP USER jonathan; GRANT Grant rights on a database object to a user GRANT ALL ON publishers TO manager; REVOKE Deny rights on a database object from a user. REVOKE INSERT ON guest FROM books;

11. Commands Purpose Example CREATE FUNCTION Creates a new SQL function within a database CREATE FUNCTION title(integer) RETURNS text AS 'SELECT title from books where id = $1'LANGUAGE 'sql'; CREATE LANGUAGE Creates a new language definition within a database CREATE FUNCTION example_call_handler () RETURNS opaqueAS'/usr/local/pgsql/lib/libexample.so' LANGUAGE 'C'; CREATE OPERATOR Creates a new SQL operator within a database CREATE OPERATOR !# (PROCEDURE = is_zero, LEFTARG = integer, RIGHTARG = integer);

12. Commands Purpose Example CREATE TYPE Creates a new SQL data type within a database CREATE TYPE zero (internallength = 16, input = zero_in, output = zero_out); DELETE Deletes rows from a table DELETE FROM shipments WHERE customer_id = 142AND ship_date < '2001-08-07'; TRUNCATE Empties the contents of a table TRUNCATE TABLE temp_emp; UPDATE Modifies the values of column data within a table UPDATE stock SET stock = stock + 1 WHERE isbn = '0385121679';

13. Commands Purpose Example ABORT Rolls back changes made during a transaction block BEGIN WORK; DELETE FROM publishers WHERE id < 100;ABORT WORK; ALTER TABLE Modifies table and column attributes ALTER TABLE employees ADD COLUMN address text; COPY Copies data between files and tables COPY publishers FROM '/tmp/publisher_data'; CURRENT_DATE Returns the current date SELECT CURRENT_DATE AS today; CURRENT_TIME Returns the current time SELECT CURRENT_TIME AS the_time;

14. Commands Purpose Example CURRENT_USER  Returns the current database username SELECT CURRENT_USER AS myself; SELECT INTO Construct a new table from the results of a SELECT SELECT * INTO TEMP TABLE old_empFROM employeesWHERE id < 105; CURRENT_TIMESTAMP – not available in SQL Returns the current date and time SELECT CURRENT_TIMESTAMP AS date_and_time; . BEGIN /COMMIT BEGIN - Starts a chained-mode transaction block. COMMIT -- Ends the current transaction block and finalizes changes made within it BEGIN WORK; CREATE TABLE test (id integer, name text); COMMIT WORK;

15. Logical Operators • The usual logical operators are available: • AND • OR • NOT

16. Comparison Operators • Operator Description • < less than • > greater than • <= less than or equal to • >= greater than or equal to • = equal • <> or != not equal

17. Mathematical Functions and Operators

21. String Functions and Operators

22. Binary String Functions and Operators

23. Pattern Matching • LIKE • string LIKE pattern [ESCAPE escape-character] • string NOT LIKE pattern [ESCAPE escape-character]

24. SIMILAR TO Regular Expressions • string SIMILAR TO pattern [ESCAPE escape-character] • string NOT SIMILAR TO pattern [ESCAPE escape-character]

25. Data Type Formatting Functions

26. Date/Time Functions and Operators

30. Enum Support Functions

31. Conditional Expressions • CASE • The SQL CASE expression is a generic conditional expression, similar to if/else statements in other programming languages: • CASE WHEN condition THEN result [WHEN ...] [ELSE result] END

32. COALESCE • COALESCE(value [, ...]) • NULLIF • NULLIF(value1, value2) • GREATEST and LEAST • GREATEST(value [, ...]) • LEAST(value [, ...])

33. Trigger • CREATE OR REPLACE FUNCTION mytrigger() • RETURNS trigger AS • $BODY$ DECLARE • BEGIN • IF(TG_OP = 'INSERT')THEN • END IF; • IF(TG_OP = 'UPDATE') THEN • END IF; • IF TG_OP = 'DELETE' THEN • END IF; • END ; • $BODY$

34. Geometric Functions and Operators • Network Address Functions and Operators • Text Search Functions and Operators • XML Functions • Sequence Manipulation Functions • Array Functions and Operators • Aggregate Functions • Subquery Expressions

35. Important link • http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html