http://www.postgresqltutorial.com/

There are several ways to verify the installation of PostgreSQL. You can try to connect to the PostgreSQL database server from any client application e.g., psql and pgAdmin.

pg_restore -U postgres -d dvdrental C:\temp\dvdrental.tar

Querying Data

Select

1. Select distinct rows by using DISTINCT operator.
2. Filter rows by using WHERE clause.
3. Sort rows by using ORDER BY clause.
4. Select rows based on various operators such as BETWEEN, IN and LIKE.
5. Group rows into groups using GROUP BY clause
6. Apply conditions for groups using HAVING clause.
7. Join a table to other tables using INNER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN clauses.

Order By

SELECT first_name, last_name FROM customer ORDER BY first_name ASC, last_name DESC;

Select Distinct

SELECT DISTINCT bcolor, fcolor FROM t1 ORDER BY bcolor, fcolor;
SELECT DISTINCT ON (bcolor) bcolor, fcolor FROM t1 ORDER BY bcolor, fcolor;

Filtering Data

Where

The conditions in the where clause are used to filter the rows returned from the select statement. There are various standard operators to construct the conditions such as comparison operators, logical operators, pattern matching operators and special operators.

SELECT customer_id, amount, payment_date FROM payment WHERE amount <= 1 OR amount >= 8;

Limit

Limit is an optional clause that gets a subset of rows returned by a query. It doesn’t conform to the SQL standard. Fetch is similar to Limit and SQL-standard but Limit is often used.
SELECT * FROM table ORDER BY column LIMIT n OFFSET m;

In/Not In

Value IN (value1, value2, …)
Value IN (SELECT value FROM tbl_name)
SELECT customer_id, rental_id, return_date FROM rental WHERE customer_id IN (1, 2) ORDER BY return_date DESC;
SELECT first_name, last_name FROM customer WHERE customer_id IN ( SELECT customer_id FROM rental WHERE CAST (return_date AS DATE) = ‘2005-05-27’ );
##Between/Not Between
Value BETWEEN low AND high
Same as
Value >= low and value <= high
Value NOT BETWEEN low AND high
Same as
Value < low OR value > high

LIKE/NOT LIKE/ILIKE/NOT ILIKE

string LIKE pattern
string NOT LIKE pattern

• Percent ( %) for matching any sequence of characters.
• Underscore ( _) for matching any single character.
  SELECT first_name, last_name FROM customer WHERE first_name LIKE ‘_her%’;

IS NULL/IS NOT NULL

Joining Multiple Tables

Joins

PostgreSQL join is used to combine columns from one (self-join) or more tables based on the values of the common columns between the tables. The common columns are typically the primary key columns of the first table and foreign key columns of the second table.
PostgreSQL support inner join, left join, right join, full outer join, cross join, natural join, and a special kind of join called self-join.

Inner Join

SELECT customer.customer_id, customer.first_name customer_first_name, customer.last_name customer_last_name, customer.email, staff.first_name staff_first_name, staff.last_name staff_last_name, amount, payment_date FROM customer INNER JOIN payment ON payment.customer_id = customer.customer_id INNER JOIN staff ON payment.staff_id = staff.staff_id;

Left Join

SELECT film.film_id, film.title, inventory_id FROM film LEFT JOIN inventory ON inventory.film_id = film.film_id;

Self-Join

CREATE TABLE employee ( employee_id INT PRIMARY KEY, first_name VARCHAR (255) NOT NULL, last_name VARCHAR (255) NOT NULL, manager_id INT, FOREIGN KEY (manager_id) REFERENCES employee (employee_id) ON DELETE CASCADE);
INSERT INTO employee ( employee_id, first_name, last_name, manager_id) VALUES (1, ‘Windy’, ‘Hays’, NULL), (2, ‘Ava’, ‘Christensen’, 1), (3, ‘Hassan’, ‘Conner’, 1), (4, ‘Anna’, ‘Reeves’, 2), (5, ‘Sau’, ‘Norman’, 2), (6, ‘Kelsie’, ‘Hays’, 3), (7, ‘Tory’, ‘Goff’, 3), (8, ‘Salley’, ‘Lester’, 3);
SELECT e.first_name || ’ ’ || e.last_name employee, m .first_name || ’ ’ || m .last_name manager FROM employee e INNER JOIN employee m ON m .employee_id = e.manager_id ORDER BY manager;
SELECT e.first_name || ’ ’ || e.last_name employee, m .first_name || ’ ’ || m .last_name manager FROM employee e LEFT JOIN employee m ON m .employee_id = e.manager_id ORDER BY manager;

Full Outer Join

SELECT employee_name, department_name FROM employees e FULL OUTER JOIN departments d ON d.department_id = e.department_id;

Cross Join

A CROSS JOIN clause allows you to produce the Cartesian Product of rows in two or more tables.
SELECT * FROM T1 CROSS JOIN T2;
SELECT * FROM T1, T2;
SELECT * FROM T1 INNER JOIN T2 ON TRUE;

Grouping Data

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 which may be count, sum, avg, max or min.
SELECT customer_id, SUM (amount) FROM payment GROUP BY customer_id ORDER BY SUM (amount) DESC;

Having

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.
SELECT customer_id, SUM (amount) FROM payment GROUP BY customer_id HAVING SUM (amount) > 200;

Performing Set Operations

Union/Union All

UNION operator removes duplicates while UNION ALL keeps them.
SELECT * FROM sales2007q1 UNION ALL SELECT * FROM sales2007q2 ORDER BY name ASC, amount DESC;

Intersect

SELECT employee_id FROM keys INTERSECT SELECT employee_id FROM hipos ORDER BY employee_id;

EXCEPT

SELECT film_id, title FROM film EXCEPT SELECT DISTINCT inventory.film_id, title FROM inventory INNER JOIN film ON film.film_id = inventory.film_id ORDER BY title;

Grouping Sets

Subquery

Subquery

SELECT film_id, title, rental_rate FROM film WHERE rental_rate > ( SELECT AVG (rental_rate) FROM film );
SELECT film_id, title FROM film WHERE film_id IN ( SELECT inventory.film_id FROM rental INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id WHERE return_date BETWEEN ‘2005-05-29’ AND ‘2005-05-30’ );
SELECT first_name, last_name FROM customer WHERE EXISTS ( SELECT 1 FROM payment WHERE payment.customer_id = customer.customer_id );

Any

The = ANY is equivalent to IN operator; The <> ANY operator is different from NOT IN.
SELECT title FROM film WHERE length >= ANY( SELECT MAX( length ) FROM film INNER JOIN film_category USING(film_id) GROUP BY category_id );

All

column_name > ALL (subquery) the expression evaluates to true if a value is greater than the biggest value returned by the subquery.
column_name != ALL (subquery) the expression evaluates to true if a value is not equal to any value returned by the subquery.

Exists

#Modifying Data

Insert

INSERT INTO table (column1, column2, …) VALUES (value1, value2, …), (value1, value2, …) , …;

Update

UPDATE link SET last_update = DEFAULT WHERE last_update IS NULL;
UPDATE link SET rel = ‘nofollow’;
UPDATE link SET description = name;
UPDATE link_tmp SET rel = link.rel, description = link.description, last_update = link.last_update FROM link WHERE link_tmp.id = link.id;
UPDATE link SET description = ‘Learn PostgreSQL fast and easy’, rel = ‘follow’ WHERE ID = 1 RETURNING id, description, rel;

Update Join

UPDATE A SET A.c1 = expression FROM B WHERE A.c2 = B.c2;
To join to another table in the UPDATE statement, you specify the joined table in the FROM clause and provide the join condition in the WHERE clause. The FROM clause must appear immediately after the SET clause.
UPDATE product SET net_price = price - price * discount FROM product_segment WHERE product.segment_id = product_segment.id;

DELETE

DELETE FROM table WHERE condition;
DELETE FROM table USING another_table WHERE table.id = another_table.id AND .
DELETE FROM table WHERE table.id = (SELECT id FROM another_table);
DELETE FROM link_tmp RETURNING *;

UPSERT

PostgreSQL added the ON CONFLICT target action clause to the INSERT statement to support the upsert feature:
INSERT INTO table_name(column_list) VALUES(value_list) ON CONFLICT target action;

Import & Export

Import CSV File Into PostgreSQL Table

COPY persons(first_name,last_name,dob,email) FROM ‘C:\tmp\persons.csv’ DELIMITER ‘,’ CSV HEADER;

Export To CSV File

COPY persons TO ‘C:\tmp\persons_db.csv’ DELIMITER ‘,’ CSV HEADER;
COPY persons(first_name,last_name,email) TO ‘C:\tmp\persons_partial_db.csv’ DELIMITER ‘,’ CSV HEADER;
COPY persons(email) TO ‘C:\tmp\persons_email_db.csv’ DELIMITER ‘,’ CSV;
\copy (SELECT * FROM persons) to ‘C:\tmp\persons_client.csv’ with csv

Managing Tables

Data Types

Boolean
Character types such as char, varchar, and text.
Numeric types such as integer and floating-point number.
Temporal types such as date, time, timestamp, and interval
UUID for storing Universally Unique Identifiers
Array for storing array strings, numbers, etc.
JSON stores JSON data
hstore stores key-value pair
Special types such as network address and geometric data

Create Table

The following are commonly used column constraints in PostgreSQL:

• NOT NULL
• UNIQUE
• PRIMARY KEY — a combination of NOT NULL and UNIQUE
• CHECK — to check a condition before data is inserted or updated
• REFERENCES — to define the foreign key constraint
  The following are the table constraints which are applied to the entire table:
• UNIQUE (column_list)
• PRIMARY KEY (column_list)
• CHECK (condition)
• REFERENCES

Select Into & Create Table As

The SELECT INTO statement allows you to create a new table and inserts data returned by a query.
The CREATE TABLE AS statement creates a new table and fills it with the data returned by a query.
##Serial
Similar to INT NOT NULL AUTO_INCREMENT in MariaDB.

Alter Table

It is used to change an existing table’s structure. The syntax is “ALTER TABLE table_name action;” The allowed actions include:

• Add a column, drop a column, rename a column, or change a column’s data type.
• Set a default value for the column.
• Add a CHECK constraint to a column.
• Rename a table.
  ##Drop Table
  DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];
  ##Truncate Table
  The TRUNCATE TABLE statement removes all rows from a table without scanning it. This is the reason why it is faster than the DELETE statement.
  #Conditional Expressions & Operators
• CASE
• COALESCE
• NULLIF
• CAST

Common Table Expressions

CTE

A common table expression is a temporary result set which you can reference within another SQL statement.
WITH cte_film AS ( SELECT film_id, title, (CASE WHEN length < 30 THEN ‘Short’ WHEN length >= 30 AND length < 90 THEN ‘Medium’ WHEN length > 90 THEN ‘Long’ END) length FROM film)
SELECT film_id, title, length FROM cte_film WHERE length = ‘Long’ ORDER BY title;

Recursive Query Using CTEs

WITH RECURSIVE cte_name(
  CTE_query_definition – non-recursive term
  UNION [ALL]
  CTE_query definion – recursive term
)
SELECT * FROM cte_name;

Transactions

A database transaction is a single unit of work which may consist of one or more operations. A transaction in PostgreSQL is atomic, consistent, isolated, and durable, often referred to as ACID.
BEGIN, COMMIT, ROLLBACK

psql Commands

• psql -d dvdrental -U postgres –W
• psql -U user -h host “dbname=db sslmode=require”
• \c dbname username
• \l #List available databases
• \dt #List available tables
• \d table_name #Describe a table
• \dn #List all schema of the currently connected database
• \df #List available functions in the current database
• \dv #List available views in the current database
• \du #List all users
• \g #Execute the previous command
• \s #Display command history
• \i filename #Execute psql commands from a file
• ? #Know all available psql commands
• \h ALTER TABLE #Know detailed information on ALTER TABLE statement.
• \timing #Turn on or off query execution time.
• \e #Edit command in your own editor.
• \ef [function name] #Edit a function in the editor.
• \a #switches from aligned to non-aligned column output.
• \H #format the output to HTML format.
• \q
  #Window Function
  Similar to an aggregate function, a window function operates on a set of rows, but it does not reduce the number of rows returned by the query. The term window describes the set of rows on which the window function operates. A window function returns a value from the rows in a window.
  A window function performs the calculation on the result set after the JOIN, WHERE, GROUP BY and HAVING clause complete, but before the final ORDER BY clause.
  window_function (arg1, arg2,…) OVER (PARTITION BY expression ORDER BY expression)
  PostgreSQL Window Functions include row_number, rank, lag, lead, first_value, last_value and so on.
  SELECT product_name, group_name, price, RANK () OVER ( PARTITION BY group_name ORDER BY price
  ) FROM products INNER JOIN product_groups USING (group_id);
  #PostgreSQL Administration
  ##Create a new database
  CREATE DATABASE db_name OWNER = role_name TEMPLATE = template ENCODING = encoding LC_COLLATE = collate LC_CTYPE = ctype TABLESPACE = tablespace_name CONNECTION LIMIT = max_concurrent_connection
  The createdb program uses CREATE DATABASE statement behind the scenes.

Copy Database

pg_dump -U postgres -O sourcedb sourcedb.sql
CREATE DATABASE targetdb;
psql -U postgres -d targetdb -f sourcedb.sql

Backup And Restore Databases

pg_dump -U postgres -W -F t dvdrental > c:\pgbackup\dvdrental.tar
psql -U username -f backupfile.sql
pg_restore --dbname=dvdrental_tpl --section=pre-data c:\pgbackup\dvdrental.tar
##Reset Password
PostgreSQL uses the pg_hba.conf configuration file that is stored in the database data directory to control the client authentication. HBA means host-based authentication.

1. Backup the pg_dba.conf file
2. Edit the pg_dba.conf by adding the following line as the first line after the comment lines. The comment line starts with the # sign. local all all trust. If your PostgreSQL installation does not support local, use host all postgres 127.0.0.1/32 trust. This step ensures that you can log into PostgreSQL database server without using the password.
3. Restart PostgreSQL server.
4. Connect to the database server and change the password of the postgres user. ALTER USER postgres with password ‘very_secure_password’;
5. Restore the pg_db.conf file and restart the server.