Ask me k'no'w querries, I will tell you no lies


December 06, 2013

Getting started with PostgreSQL PostgreSQL is a general purpose and object-relational database management system. PostgreSQL features highlights User-defined types Table inheritance Sophisticated locking mechanism Foreign key referential integrity Views, rules, sub-select Nested transactions (savepoints) Multi-version concurrency control (MVCC) Asynchronous replication Native Microsoft Windows Server version Table spaces Point-in-time recovery

and many are being added with every new release What make PostgreSQL stand out PostgreSQL is the first database management system that implements multi-version concurrency control (MVCC) feature. It allows you to add custom functions developed using different programming languages such as C/C++, Java, etc. PostgreSQL is designed to be extensible. Who are using PostgreSQL — List Install PostgreSQL There is an installer available for windows and Mac OS and steps are mentioned to install in other Operating systems. Here is the procedure to install in ubuntu sudo apt-get install postgresql-9.3(9.3 is latest version at the time of writing) pgAdmin III is a handy GUI for PostgreSQL, it is essential to beginners. To install it, type at the command line: sudo apt-get install pgadmin3 Basic Server Setup we need to change the PostgreSQL postgres user password. sudo -u postgres psql postgres Set password Control+D to exit the posgreSQL prompt Create first Db createdb onedb acces the db psql -d onedb -U postgres To import daatabse or execute a sql file … at psql DB prompt \i path/to/file/containing/sql/code Basic PostgreSQL tutorial PostgreSQL SELECT One of the most common tasks is to query data from tables by using the SELECT statement. The SELECT statement is one of the most complex statements in PostgreSQL. Clauses that appear in SELECT statement Select distinct rows by using DISTINCT operator. Filter rows by using WHERE clause. Sort rows by using the ORDER BY clause. Select rows based on various operator such as BETWEEN, IN and LIKE. Group rows into groups by using GROUP BY clause Apply condition for groups by using HAVING clause. Join to other table by using INNER JOIN, LEFT JOIN, RIGHT JOIN clauses. Normal SELECT statement SELECT column1, column2, … FROM table_name; Here semicolon is to denote the statement is over for postgresql … it is not sql requirement SQL language is case sensitive Example SELECT * FROM employee; This selects all rows of employee table. Using * is not advaised, it is good for performamce sake to specify column names you are retrieving. SELECT name, address, city FROM employee; PostgreSQL SELECT DISTINCT The DISTINCT clause is used in SELECT statement to remove duplicate rows in result set, this clause can be used on one or more columns. Example SELECT DISTINCT name, address, city FROM employee;

This query gives distinct result on combination of columns.

FROM employee;

This gives result with distinct values for name column
the returned result is unpredictable so ORDER BY clause should be used, the ORDER BY the DISTINCT column firts
FROM employee

PostgreSQL ORDER BY ORDER BY is used for sorting the result set genearted from SELECT statement. Default ORDER is ASC to GET descending you need to specify. SELECT DISTINCT ON(name), address, city FROM employee ORDER BY name DESC, address, city; PostgreSQL WHERE WHERE clause is used to filter the result set returned from SELECT statement SELECT column1, column2 … columnn FROM tablename WHERE conditions;

In WHERE conditions comparitive operatives are used PostgreSQL LIKE(NOT LIKE) LIKE and ILIKE are used for patter matching LIKE is not case sensitive(NOT LIKE) ILIKE is case sensitive(NOT ILIKE) SELECT firstname, lastname FROM customer WHERE firstname LIKE ‘Jen%’; WHERE clause contains a special expression: the firstname, the LIKE operator and a string that contains a percent (%) character, which is referred as a pattern. query returns rows whose values in the first name column begin with Jen and may be followed by any sequence of characters. This technique is called pattern matching. You construct a pattern by combining a string with wildcard characters and use the LIKE or NOT LIKE PostgreSQL provides two wild cards Percent ( %) for matching any sequence of characters. Underscore ( ) for matching any single character. SELECT * FROM employee WHERE firstname LIKE ‘T%’; SELECT * FROM employee WHERE firstname LIKE ’avi’; ~~ is equivalent to LIKE * is equivalent to ILIKE ! is equivalent to NOT LIKE !~~* is equivalent to NOT ILIKE

PostgreSQL IN IN operator in the WHERE clause is to check if a value matches any value in a list of values SELECT customerid, rentalid, returndate FROM rental WHERE customerid IN (1, 2) ORDER BY return_date DESC;

PostgreSQL NOT IN operator combining IN operator with the NOT operator to select rows whose values do not match the values in the list. SELECT customerid, rentalid, returndate FROM rental WHERE customerid NOT IN (1, 2); You can also rewrite the NOT IN operator by using the not equal (<>) and the AND operators SELECT customerid, rentalid, returndate FROM rental WHERE customerid <> 1 AND customerid <> 2; PostgreSQL IN with subquery SELECT firstname, lastname FROM customer WHERE customerid IN ( SELECT customerid FROM rental WHERE CAST (returndate AS DATE) = ‘2005-05-27’ );

PostgreSQL BETWEEN use the BETWEEN operator to match a value against a range of values value BETWEEN low AND high <---> value >= low and value <= high value NOT BETWEEN low AND high <---> value < low OR value > high

If you want to check a value against of date ranges, you should use the literal date in ISO 8601 format i.e., YYYY-MM-DD to get the payment whose payment date is between 2007-02-07 and 2007-02-15 SELECT customerid, paymentid, amount, paymentdate FROM payment WHERE paymentdate BETWEEN ‘2007-02-07’ AND ‘2007-02-15’;

PostgreSQL UNION The UNION operator combines result sets of two or more SELECT statements into a single result set. The following are rules applied to the queries: Both queries must return the same number of columns. The corresponding columns in the queries must have compatible data types. The UNION operator removes all duplicate rows unless the UNION ALL is used. PostgreSQL INNER JOIN INNER JOIN between A and B tables: The INNER JOIN clause returns rows in A table that have the corresponding rows in the B table. PostgreSQL LEFT JOIN If you want to select rows from the A table that have corresponding rows in the B table, you use the INNER JOIN clause. If you want to select rows from the A table which may or may not have corresponding rows in the B table, you use the LEFT JOIN clause. In case there is no matching row in the B table, the values of the columns in the B table are substituted by the NULL values.

PostgreSQL GROUP BY The GROUP BY clause divides the rows returned from the SELECT statement into groups. For each group, you can apply an aggregate function e.g., to calculate the sum of items or count the number of items in the groups. SELECT column1, aggregatefunction(column2) FROM tblname GROUP BY column_1 The GROUP BY clause must appear right after the FROM or WHERE clause. Followed by the GROUP BY clause is one column or a list of comma-separated columns. You can also put an expression in the GROUP BY clause. GROUP BY with SUM GROUP BY with COUNT To filter groups, you use the HAVING clause instead of WHERE clause

PostgreSQL HAVING use the HAVING clause in conjunction with the GROUP BY clause to filter group rows that do not satisfy a specified condition The HAVING clause sets the condition for group rows created by the GROUP BY clause after the GROUP BY clause applies, while the WHERE clause sets the condition for individual rows before GROUP BY clause applies. This is the main difference between the HAVING and WHERE clauses.


PostgreSQL Data Types

PostgreSQL supports the following data types:

Boolean Character Number Temporal i.e., date and time-related data types Special types Array


PostgreSQL Stored Procedures

PostgreSQL Administration