PostgreSQL

# List of Practicals:

  1. Installation and configuration of Postgresql and Pgadmin on machine and preparing to accept connections from all Ip addresses.
  2. Write the queries for Data Definition (CREATE, DROP, ALTER and RENAME) and Data Manipulation Language (SELECT, INSERT, UPDATE and DELETE).
  3.  Write SQL queries using Logical operators (<,>,=etc).
  4. Write SQL queries using SQL operators (Between, AND, OR, IN, Like, NULL).
  5. Write SQL query using character, number, date and group functions.
  6. Write SQL queries for Relational Algebra (UNION, INTERSECT, and MINUS, etc.).
  7. Write SQL queries for extracting data from more than one table (Equi-Join, Non-Equi-Join , Outer Join)
  8.  Write SQL queries for sub queries, nested queries.
  9. Creation, altering and droping of tables and inserting rows into a table (use constraints while creating tables) examples using SELECT command.
  10. Queries (along with sub Queries) using ANY, ALL, IN, EXISTS, NOTEXISTS, UNION, INTERSET, Constraints. Example – Select the roll number and name of the student who secured fourth rank in the class.
  11. Queries using Aggregate functions (COUNT, SUM, AVG, MAX and MIN), GROUP BY, HAVING and Creation and dropping of Views.
  12. Queries using Conversion functions (to_char, to_number and to_date), string functions (Concatenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr and instr), date functions (Sysdate, next_day, add_months, last_day, months_between, least, greatest, trunc, round, to_char, to_date)
  13. Write SQL queries to create views and also apply different operations on views.
  14.  Minor Project: By using standard database design rules, database has to be designed for a specific assigned problem to a group of two to three students. ER diagram related to project with an open source database tool like MYSQL workbench must also be prepared. The group of students must submit a project report of 8 to 10 pages (approximately) and the team will have to demonstrate as well as have to give a presentation of the same.

PRACTICAL-9


Constraints are the rules enforced on data columns on table.

These are used to prevent invalid data from being entered into the database.

This ensures the accuracy and reliability of the data in the database.

Constraints could be column level or table level.

Column level constraints are applied only to one column where as table level constraints are applied to the whole table.

Types of constraints used :

NOT NULL Constraint :

Ensures that a column cannot have NULL value.

A NOT NULL constraint is always written as a column constraint.

A NULL is not the same as no data, rather, it represents unknown data.

Example:
  CREATE TABLE COMPANY(
     ID INT PRIMARY KEY     NOT NULL,
     NAME           TEXT    NOT NULL,
     AGE            INT     NOT NULL,
     ADDRESS        CHAR(50),
     SALARY         REAL
   );

UNIQUE Constraint :

Ensures that all values in a column are different.

Example:
  CREATE TABLE COMPANY3(
     ID INT PRIMARY KEY     NOT NULL,
     NAME           TEXT    NOT NULL,
     AGE            INT     NOT NULL UNIQUE,
     ADDRESS        CHAR(50),
     SALARY         REAL    DEFAULT 50000.00
  );

Here, AGE column is set to UNIQUE , so that you can not have two records with same age:

PRIMARY KEY Constraint :

The PRIMARY KEY constraint uniquely identifies each record in a database table.

There can be more UNIQUE columns, but only one primary key in a table. Primary keys are important when designing the database tables. Primary keys are unique ids.

A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.

If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

Example:
  CREATE TABLE COMPANY4(
     ID INT PRIMARY KEY     NOT NULL,
     NAME           TEXT    NOT NULL,
     AGE            INT     NOT NULL,
     ADDRESS        CHAR(50),
     SALARY         REAL
  );

FOREIGN KEY Constraint :

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table.

They are called foreign keys because the constraints are foreign; that is, outside the table.

Foreign keys are sometimes called a referencing key.

Example:

Creates a table called COMPANY.

  CREATE TABLE COMPANY(
     ID INT PRIMARY KEY     NOT NULL,
     NAME           TEXT    NOT NULL,
     AGE            INT     NOT NULL,
     ADDRESS        CHAR(50),
     SALARY         REAL
  );

Creates a new table called DEPARTMENT.

  CREATE TABLE DEPARTMENT(
     ID INT PRIMARY KEY      NOT NULL,
     DEPT           CHAR(50) NOT NULL,
     EMP_ID         INT      references COMPANY(ID)
  );

The column EMP_ID is the foreign key and references the ID field of the table COMPANY.

CHECK Constraint :

The CHECK constraint ensures that all values in a column satisfy certain conditions.

If the condition evaluates to false, the record violates the constraint and isn’t entered into the table.

Example:
   CREATE TABLE COMPANY(
     ID INT PRIMARY KEY     NOT NULL,
     NAME           TEXT    NOT NULL,
     AGE            INT     NOT NULL,
     ADDRESS        CHAR(50),
     SALARY         REAL    CHECK(SALARY > 0)
   );

Here, we add a CHECK with SALARY column, so that you can not have any SALARY Zero:

EXCLUSION Constraint:

The EXCLUDE constraint ensures that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s), not all of these comparisons will return TRUE.

At least one of these operator comparisons will return false or null.

Example:
  CREATE TABLE COMPANY(
     ID INT PRIMARY KEY     NOT NULL,
     NAME           TEXT ,
     AGE            INT   ,
     ADDRESS        CHAR(50),
     SALARY         REAL,
     EXCLUDE USING gist
     (NAME WITH =,
     AGE WITH <>)
  );

Here, USING gist is the type of index to build and use for enforcement.

Adding an exclusion constraint will automatically create an index of the type specified in the constraint declaration.

Dropping Constraints:

To remove a constraint you need to know its name.

If the name is known, it’s easy to drop.

Else you need to find out the system generated name.

Syntax:
    ALTER TABLE table_name DROP CONSTRAINT some_name;
Example:
    ALTER TABLE order_details
    DROP CONSTRAINT order_unique;

In this example, we’re dropping a unique constraint on the order_details table called order_unique.

PRACTICAL-8

Write SQL queries for sub queries, nested queries.

A subquery or Inner query or Nested query is a query within another PostgreSQL query and embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE and DELETE statements along with the operators like =, <, >, >=, <=, IN, etc.

Rules of subqueries are:

Subqueries must be enclosed within parentheses.

A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.

An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.

Subqueries that return more than one row can only be used with multiple value operators, such as the IN, EXISTS, NOT IN, ANY/SOME, ALL operator.

The BETWEEN operator cannot be used with a subquery; however, the BETWEEN can be used within the subquery.

Subqueries with the SELECT Statement:

Syntax:
    SELECT column_name [, column_name ]
    FROM   table1 [, table2 ]
    WHERE  column_name OPERATOR
           (SELECT column_name [, column_name ]
           FROM table1 [, table2 ]
           [WHERE])
Example:

Company Table:

id name age address salary
1 paul 32 california 20000
2 allen 25 texas 15000
3 teddy 23 norway 20000
4 mark 25 rich-mond 65000
5 david 27 texas 85000
6 kim 22 south-hall 45000
7 james 24 houston 10000

test database:

 SELECT *
 FROM COMPANY
 WHERE ID IN (SELECT ID
              FROM COMPANY
              WHERE SALARY > 45000) ;

Result:

id name age address salary
4 mark 25 rich-mond 65000
5 david 27 texas 85000

Subqueries with the INSERT Statement:

The INSERT statement uses the data returned from the subquery to insert into another table.

The selected data in the subquery can be modified with any of the character, date, or number functions.

Syntax:
INSERT INTO table_name [ (column1 [, column2 ]) ]
       SELECT [ *|column1 [, column2 ]
       FROM table1 [, table2 ]
       [ WHERE VALUE OPERATOR ]

Subqueries with the UPDATE Statement:

The subquery can be used in conjunction with the UPDATE statement.

Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement.

Syntax:
   UPDATE table
   SET column_name = new_value
   [ WHERE OPERATOR [ VALUE ]
      (SELECT COLUMN_NAME
      FROM TABLE_NAME)
      [ WHERE) ]
Example:
    UPDATE COMPANY
    SET SALARY = SALARY * 0.50
    WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
                  WHERE AGE >= 27 );

Result:

id name age address salary
2 allen 25 texas 15000
3 teddy 23 norway 20000
4 mark 25 rich-armond 65000
6 kim 22 south-hall 45000
7 james 24 housten 10000
1 paul 32 california 10000
5 david 27 texas 42500

This example updates SALARY by 0.50 times in COMPANY table for all the customers, whose AGE is greater than or equal to 27.

Subqueries with the DELETE Statement:

The subquery can be used in conjunction with the DELETE statement like with any other statements.

Syntax:
   DELETE FROM TABLE_NAME
   [ WHERE OPERATOR [ VALUE ]
     (SELECT COLUMN_NAME
     FROM TABLE_NAME)
     [ WHERE) ]
Example:
    DELETE FROM COMPANY
    WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
                   WHERE AGE > 24 );

Result

id name age address salary
3 teddy 23 norway 20000
6 kim 22 south-hall 45000
7 james 24 housten 10000

This example deletes records from COMPANY table for all the customers, whose AGE is greater than or equal to 24.

PRACTICAL-7

Write SQL queries for extracting data from more than one table.

JOINS are used to retrieve data from multiple tables. A JOIN is a means for combining fields from two tables by using values common to each.

There are different types of PostgreSQL joins:

1. INNER JOIN:

INNER JOINS return all rows from multiple tables where the join condition is met.

Syntax:

  SELECT columns
  FROM table1 
  INNER JOIN table2
  ON table1.column = table2.column;

Visual Illustration:

In this visual diagram, the PostgreSQL INNER JOIN returns the shaded area:

Alt text

INNER JOIN would return the records where table1 and table2 intersect.

Example:

Suppliers Table:

supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA

Orders_table:

order_id supplier_id order_date
500125 10000 2013/05/12
500126 10001 2013/05/13
500127 10004 2013/05/14

If we run the PostgreSQL SELECT statement (that contains an INNER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers

INNER JOIN orders

ON suppliers.supplier_id = orders.supplier_id;

Result:

supplier_id name order_date
10000 IBM 2013/05/12
10001 Hewlett Packard 2013/05/13

The rows for Microsoft and NVIDIA from the supplier table would be omitted, since the supplier_id’s 10002 and 10003 do not exist in both tables. The row for 500127 (order_id) from the orders table would be omitted, since the supplier_id 10004 does not exist in the suppliers table.

2. LEFT OUTER JOIN:

This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax:
  SELECT columns
  FROM table1
  OUTER JOIN table2
  ON table1.column = table2.column;

Visual Illustration:

In this visual diagram, the PostgreSQL LEFT OUTER JOIN returns the shaded area:

Alt text

The PostgreSQL LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.

Example:

suppliers table:

supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA

Orders table:

order_id supplier_id order_date
500125 10000 2013/05/12
500126 10001 2013/05/13

If we run the SELECT statement (that contains a LEFT OUTER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers

LEFT OUTER JOIN orders

ON suppliers.supplier_id = orders.supplier_id;

Result:

supplier_id supplier_name order_date
10000 IBM 2013/05/12
10001 Hewlett Packard 2013/05/13
10002 Microsoft NULL
10003 NVIDIA NULL

The rows for Microsoft and NVIDIA would be included because a LEFT OUTER JOIN was used. However, you will notice that the order_date field for those records contains a NULL value.

3. RIGHT OUTER JOIN:

This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax:
SELECT columns
FROM table1
RIGHT OUTER JOIN table2
ON table1.column = table2.column;

Visual Illustration:

In this visual diagram, the PostgreSQL RIGHT OUTER JOIN returns the shaded area:
Alt text

The PostgreSQL RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2.

Examlple:

Suppliers table:

supplier_id supplier_name
10000 Apple
10001 Google

Orders table:

order_id supplier_id order_date
500125 10000 2013/08/12
500126 10001 2013/08/13
500127 10002 2013/08/14

If we run the SELECT statement (that contains a RIGHT OUTER JOIN) below:

 SELECT orders.order_id, orders.order_date, suppliers.supplier_name
 FROM suppliers

 RIGHT OUTER JOIN orders

 ON suppliers.supplier_id = orders.supplier_id;
Result:
order_id order_date supplier_name
500125 2013/08/12 Apple
500126 2013/08/13 Google
500127 2013/08/14 NULL

The row for 500127 (order_id) would be included because a RIGHT OUTER JOIN was used. However, you will notice that the supplier_name field for that record contains a NULL value.

4. FULL OUTER JOIN:

This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.

Syntax:
 SELECT columns
 FROM table1
 FULL OUTER JOIN table2
 ON table1.column = table2.column;

Visual Illustration:

In this visual diagram, the PostgreSQL FULL OUTER JOIN returns the shaded area:
Alt text

The PostgreSQL FULL OUTER JOIN would return the all records from both table1 and table2.

Example:

Supplier table:

supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA

Order table:

order_id supplier_id order_date
500125 10000 2013/08/12
500126 10001 2013/08/13
500127 10002 2013/08/14

If we run the SQL statement (that contains a FULL OUTER JOIN) below:

  SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
  FROM suppliers

  FULL OUTER JOIN orders

  ON suppliers.supplier_id = orders.supplier_id;

Result:

supplier_id supplier_name order_date
10000 IBM 2013/08/12
10001 Hewlett Packard 2013/08/13
10002 Microsoft NULL
10003 NVIDIA NULL
NULL NULL 2013/08/14

The rows for Microsoft and NVIDIA would be included because a FULL OUTER JOIN was used. However, you will notice that the order_date field for those records contains a NULL value.

The row for supplier_id 10004 would be also included because a FULL OUTER JOIN was used. However, you will notice that the supplier_id and supplier_name field for those records contain a NULL value.

5. CROSS JOIN:

A CROSS JOIN matches every row of the first table with every row of the second table.

Example:

Tablbe1(T1):

label
A
B

Table2(T2):

score
1
2
3

uses the CROSS JOIN operator to join the T1 table with the T2 table.

   SELECT
   *
   FROM
   T1
   CROSS JOIN T2;
Result:
label score
A 1
B 1
A 2
B 2
A 3
B 3

ILLUSTRATE WITH PICTURE:

Alt text

EMAIL:

PRACTICLE-6

UNION:

The UNION operator used to combine the results of two or more SELECT statements without returning any duplicate rows.

SYNTAX:

 SELECT
    column_1,
    column_2
 FROM
    tbl_name_1

 UNION

 SELECT
    column_1,
    column_2
 FROM
    tbl_name_2;

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.

EXAMPLE:

sales1data:

name amount
mike 1500
jon 1400
mary 1000

sales2data:

name amount
mike 1600
jon 1300
mary 1000
SELECT *
FROM
sales1data

UNION

SELECT *
FROM
sales2data;
result:
name amount
jon 1300
jon 1400
mary 1000
mike 1500
mike 1600

UNION ALL:

To get all rows that include duplicate, you use the UNION ALL operator.

EXAMPLE:
   SELECT *
   FROM
   sales1data

   UNION ALL

   SELECT *
   FROM
   sales2data;
result:
name amount
jon 1300
jon 1400
mary 1000
mary 1000
mike 1500
mike 1600
UNION with ORDER BY:
example:
  SELECT *
  FROM
  sales1data

  UNION ALL

  SELECT *
  FROM
  sales2data

  ORDER BY 

  name ASC,
  amount DESC;
RESULT:
name amount
jon 1400
jon 1300
mary 1000
mary 1000
mike 1600
mike 1500

INTERSECT:

The PostgreSQL INTERSECT operator combines the result sets of two or more SELECT statements into a single result set. The INTERSECT operator returns all rows in the both result sets.

The final result set is represented by the yellow area where the circle A intersects with the circle B.

Alt text

Syntax;
  SELECT
    column_list
  FROM
  A

  INTERSECT

  SELECT
   column_list
  FROM
  B;

  • The number of columns and their order in the SELECT clauses must the be the same.
  • The data types of the columns must be compatible.

MINUS:

MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. TheMINUS operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.

MINUS Query:

Alt text

Explanation: The MINUS ** query will return the records in the blue shaded area. These are the records that exist in Dataset1 and not in Dataset2.

Each SELECT statement within the MINUS query must have the same number of fields in the result sets with similar data types.

The MINUS operator is not supported in all SQL databases. It can used in databases such as Oracle.

For databases such as SQL Server, PostgreSQL, and SQLite, use the EXCEPT operator to perform this type of query.

Syntax:
       SELECT expression1, expression2, ... expression_n
       FROM tables
       [WHERE conditions]

       MINUS

       SELECT expression1, expression2, ... expression_n
       FROM tables
       [WHERE conditions];

WHERE conditions:
Optional. These are conditions that must be met for the records to be selected.

NOTE:
-There must be same number of expressions in both SELECT statements.
-The corresponding expressions must have the same data type in the SELECT statements. For example: expression1 must be the same data type in both the first and second SELECTstatement

PRACTICAL-5

Write SQL query using character, number, date and group functions.

character:

A character set support in PostgreSQL allows you to store text in a variety of character sets including single byte character sets and multiple byte character Sets.

Character Types:

The table below lists general-purpose character types available in PostgreSQL.

       Name                                   Description

 character varying(n), varchar(n)       variable-length with limit

 character(n), char(n)                  fixed-length, blank padded

 text                                   variable unlimited length

Numeric funtions :

ABS(X):

The ABS() function returns the absolute value of X. Consider the following example:

   testdb=# SELECT ABS(2);
   +---------------------------------------------------------+
   | ABS(2)                                                  |
   +---------------------------------------------------------+
   | 2                                                       |
   +---------------------------------------------------------+
   1 row in set (0.00 sec)


   testdb=# SELECT ABS(-2);
   +---------------------------------------------------------+
   | ABS(2)                                                  |
   +---------------------------------------------------------+
   | 2                                                       |
   +---------------------------------------------------------+
   1 row in set (0.00 sec)
ACOS(X):

This function returns the arccosine of X. The value of X must range between -1 and 1 or NULL will be returned. Consider the following example:

   testdb=# SELECT ACOS(1);
   +---------------------------------------------------------+
   | ACOS(1)                                                 |
   +---------------------------------------------------------+
   | 0.000000                                                |
   +---------------------------------------------------------+
   1 row in set (0.00 sec)
ASIN(X):

The ASIN() function returns the arcsine of X. The value of X must be in the range of -1 to 1 or NULL is returned.

 testdb=# SELECT ASIN(1);
 +---------------------------------------------------------+
 | ASIN(1)                                                 |
 +---------------------------------------------------------+
 | 1.5707963267949                                         |
 +---------------------------------------------------------+
 1 row in set (0.00 sec)
ATAN(X):

This function returns the arctangent of X.

 testdb=# SELECT ATAN(1);
 +---------------------------------------------------------+
 | ATAN(1)                                                 |
 +---------------------------------------------------------+
 | 0.78539816339745                                        |
 +---------------------------------------------------------+
 1 row in set (0.00 sec)
ATAN2(Y,X):

This function returns the arctangent of the two arguments: X and Y. It is similar to the arctangent of Y/X, except that the signs of both are used to find the quadrant of the result.

 testdb=# SELECT ATAN2(3,6);
 +---------------------------------------------------------+
 | ATAN2(3,6)                                              |
 +---------------------------------------------------------+
 | 0.46364760900081                                        |
 +---------------------------------------------------------+
 1 row in set (0.00 sec)
CEIL(X):
CEILING(X):

These function return the smallest integer value that is not smaller than X. Consider the following example:

 testdb=# SELECT CEILING(3.46);
 +---------------------------------------------------------+
 | CEILING(3.46)                                           |
 +---------------------------------------------------------+
 | 4                                                       |
 +---------------------------------------------------------+
 1 row in set (0.00 sec)

 testdb=# SELECT CEIL(-6.43);
 +---------------------------------------------------------+
 | CEIL(-6.43)                                             |
 +---------------------------------------------------------+
 | -6                                                      |
 +---------------------------------------------------------+
 1 row in set (0.00 sec)
COS(X):

This function returns the cosine of X. The value of X is given in radians.

 testdb=#SELECT COS(90);
 +---------------------------------------------------------+
 | COS(90)                                                 |
 +---------------------------------------------------------+
 | -0.44807361612917                                       |
 +---------------------------------------------------------+
 1 row in set (0.00 sec)
COT(X):

This function returns the cotangent of X. Consider the following example:

 testdb=#SELECT COT(1);
 +---------------------------------------------------------+
 | COT(1)                                                  |
 +---------------------------------------------------------+
 | 0.64209261593433                                        |
 +---------------------------------------------------------+
 1 row in set (0.00 sec)
DEGREES(X):

This function returns the value of X converted from radians to degrees.

 testdb=#SELECT DEGREES(PI());
 +---------------------------------------------------------+
 | DEGREES(PI())                                           |
 +---------------------------------------------------------+
 | 180.000000                                              |
 +---------------------------------------------------------+
 1 row in set (0.00 sec)
EXP(X):

This function returns the value of e (the base of the natural logarithm) raised to the power of X.

 testdb=#SELECT EXP(3);
 +---------------------------------------------------------+
 | EXP(3)                                                  |
 +---------------------------------------------------------+
 | 20.085537                                               |
 +---------------------------------------------------------+
 1 row in set (0.00 sec)
FLOOR(X):

This function returns the largest integer value that is not greater than X.

 testdb=#SELECT FLOOR(7.55);
 +---------------------------------------------------------+
 | FLOOR(7.55)                                             |
 +---------------------------------------------------------+
 | 7                                                       |
 +---------------------------------------------------------+
 1 row in set (0.00 sec)
GREATEST(n1,n2,n3,……….):

The GREATEST() function returns the greatest value in the set of input parameters (n1, n2, n3, and so on). The following example uses the GREATEST() function to return the largest number from a set of numeric values:

 testdb=#SELECT GREATEST(3,5,1,8,33,99,34,55,67,43);
 +---------------------------------------------------------+
 | GREATEST(3,5,1,8,33,99,34,55,67,43)                     |
 +---------------------------------------------------------+
 | 99                                                      |
 +---------------------------------------------------------+
 1 row in set (0.00 sec)
LEAST(N1,N2,N3,N4,……):

The LEAST() function is the opposite of the GREATEST() function. Its purpose is to return the least-valued item from the value list (N1, N2, N3, and so on). The following example shows the proper usage and output for the LEAST() function:

 testdb=#SELECT LEAST(3,5,1,8,33,99,34,55,67,43);
 +---------------------------------------------------------+
 | LEAST(3,5,1,8,33,99,34,55,67,43)                        |
 +---------------------------------------------------------+
 | 1                                                       |
 +---------------------------------------------------------+
 1 row in set (0.00 sec)
LOG(X):
LOG(B,X):

The single argument version of the function will return the natural logarithm of X. If it is called with two arguments, it returns the logarithm of X for an arbitrary base B. Consider the following example:

 testdb=#SELECT LOG(45);
 +---------------------------------------------------------+
 | LOG(45)                                                 |
 +---------------------------------------------------------+
 | 1.65321251377534                                               |
 +---------------------------------------------------------+
 1 row in set (0.00 sec)


 testdb=#SELECT LOG(2,65536);
 +---------------------------------------------------------+
 | LOG(2,65536)                                            |
 +---------------------------------------------------------+
 | 16.000000                                               |
 +---------------------------------------------------------+
 1 row in set (0.00 sec)
MOD(N,M):

This function returns the remainder of N divided by M. Consider the following example:

  testdb=#SELECT MOD(29,3);
  +---------------------------------------------------------+
  | MOD(29,3)                                               |
  +---------------------------------------------------------+
  | 2                                                       |
  +---------------------------------------------------------+
  1 row in set (0.00 sec)
PI():

This function simply returns the value of pi. SQL internally stores the full double-precision value of pi.

  testdb=#SELECT PI();
  +---------------------------------------------------------+
  | PI()                                                    |
  +---------------------------------------------------------+
  | 3.141593                                                |
  +---------------------------------------------------------+
  1 row in set (0.00 sec)
POW(X,Y):
POWER(X,Y):

These two functions return the value of X raised to the power of Y.

  testdb=# SELECT POWER(3,3);
  +---------------------------------------------------------+
  | POWER(3,3)                                              |
  +---------------------------------------------------------+
  | 27                                                      |
  +---------------------------------------------------------+
  1 row in set (0.00 sec)
RADIANS(X):

This function returns the value of X, converted from degrees to radians.

  testdb=#SELECT RADIANS(90);
  +---------------------------------------------------------+
  | RADIANS(90)                                             |
  +---------------------------------------------------------+
  |1.570796                                                 |
  +---------------------------------------------------------+
  1 row in set (0.00 sec)
ROUND(X):
ROUND(X,D):

This function returns X rounded to the nearest integer. If a second argument, D, is supplied, then the function returns X rounded to D decimal places. D must be positive or all digits to the right of the decimal point will be removed. Consider the following example:

  testdb=#SELECT ROUND(5.693893);
  +---------------------------------------------------------+
  | ROUND(5.693893)                                         |
  +---------------------------------------------------------+
  | 6                                                       |
  +---------------------------------------------------------+
  1 row in set (0.00 sec)


  testdb=#SELECT ROUND(5.693893,2);
  +---------------------------------------------------------+
  | ROUND(5.693893,2)                                       |
  +---------------------------------------------------------+
  | 5.69                                                    |
  +---------------------------------------------------------+
  1 row in set (0.00 sec)
SIGN(X):

This function returns the sign of X (negative, zero, or positive) as -1, 0, or 1.

  testdb=#SELECT SIGN(-4.65);
  +---------------------------------------------------------+
  | SIGN(-4.65)                                             |
  +---------------------------------------------------------+
  | -1                                                      |
  +---------------------------------------------------------+
  1 row in set (0.00 sec)


  testdb=#SELECT SIGN(0);
  +---------------------------------------------------------+
  | SIGN(0)                                                 |
  +---------------------------------------------------------+
  | 0                                                       |
  +---------------------------------------------------------+
  1 row in set (0.00 sec)


  testdb=#SELECT SIGN(4.65);
  +---------------------------------------------------------+
  | SIGN(4.65)                                              |
  +---------------------------------------------------------+
  | 1                                                       |
  +---------------------------------------------------------+
  1 row in set (0.00 sec)
SIN(X):

This function returns the sine of X. Consider the following example:

  testdb=#SELECT SIN(90);
  +---------------------------------------------------------+
  | SIN(90)                                                 |
  +---------------------------------------------------------+
  | 0.893997                                                |
  +---------------------------------------------------------+
  1 row in set (0.00 sec)
SQRT(X):

This function returns the non-negative square root of X. Consider the following example:

  testdb=#SELECT SQRT(49);
  +---------------------------------------------------------+
  | SQRT(49)                                                |
  +---------------------------------------------------------+
  | 7                                                       |
  +---------------------------------------------------------+
  1 row in set (0.00 sec)
TAN(X):

This function returns the tangent of the argument X, which is expressed in radians.

  testdb=#SELECT TAN(45);
  +---------------------------------------------------------+
  | TAN(45)                                                 |
  +---------------------------------------------------------+
  | 1.619775                                                |
  +---------------------------------------------------------+
  1 row in set (0.00 sec)

date/time functions:

age Function:

age function returns the number of years, months, and days between two dates.

Syntax:
     age( [date1,] date2 )

date1 and date2:
The two dates to calculate the difference between.

Calculation:
The age function performs two different calculations depending on what parameters are provided.

If date1 is provided, the age function performs the following calculation:
date1 – date2

If date1 is NOT provided, the age function performs the following calculation:
current date – date2

The age function will return the result as one of the following formats:

xxx year(s) xxx mon(s) xxx day(s)
xxx mon(s) xxx day(s)
xxx day(s)

EXAMPLE:
  postgres=# SELECT age(timestamp '2014-01-01');  (current date is '2014-04-25')
  age
  ----------------
  3 mons 24 days
 (1 row)


 postgres=# SELECT age(timestamp '2014-04-25', timestamp '2014-01-01');
  age
 ----------------
  3 mons 24 days
 (1 row)


 postgres=# SELECT age(current_date, timestamp '2012-09-16');
     age
 ----------------------
 1 year 7 mons 9 days
 (1 row)
current_date Function:

current_date function returns the current date.

Syntax:
  current_date

The current_date function will return the current date as a ‘YYYY-MM-DD’ format.
Do not put parentheses () after the current_date function.

Example:
  postgres=# SELECT current_date;
  date
  ------------
  2014-04-24
 (1 row)


  postgres=# SELECT current_date + 1;
 ?column?
  ------------
 2014-04-25
 (1 row)
current_time Function:

current_time function returns the current time with the time zone.

Syntax:
    current_time( [ precision ] )

precision:Optional. The number of digits to round the fractional seconds to.

The current_time function will return the current time of day as a ‘HH:MM:SS.US+TZ’ format.

Do not put parentheses () after the current_time function when the precision parameter is not specified.

Example:
   postgres=# SELECT current_time;
   timetz
   --------------------
   20:18:08.586881+00
   (1 row)
current_timestamp Function:

current_timestamp function returns the current date and time with the time zone.

Syntax:
current_timestamp( [ precision ] )

precision:Optional. The number of digits to round the fractional seconds to.

The current_timestamp function will return the current date as a ‘YYYY-MM-DD HH:MM:SS.US+TZ’ format.

Do not put parentheses () after the current_timestamp function when the precision parameter is not specified.

Example:
   postgres=# SELECT current_timestamp;
          now
   -------------------------------
   2014-04-24 20:55:09.808294+00
  (1 row)
date_part Function:

date_part function extracts parts from a date.

Syntax:
   date_part( 'unit', date )

date: The date, timestamp, time, or interval value from which the date part is to be extracted.

unit:

Unit Explanation
century Uses the Gregorian calendar where the first century starts at ‘0001-01-01 00:00:00 AD’
day Day of the month (1 to 31)
decade Year divided by 10
dow Day of the week (0=Sunday, 1=Monday, 2=Tuesday, … 6=Saturday)
doy Day of the year (1=first day of year, 365/366=last day of the year, depending if it is a leap year)
epoch Number of seconds since ‘1970-01-01 00:00:00 UTC’, if date value. Number of seconds in an interval, if interval value
hour Hour (0 to 23)
isadow Day of the week (1=Monday, 2=Tuesday, 3=Wednesday, … 7=Sunday)
isoyear ISO 8601 year value (where the year begins on the Monday of the week that contains January 4th)
microseconds Seconds (and fractional seconds) multiplied by 1,000,000
millennium Millennium value
milliseconds Seconds (and fractional seconds) multiplied by 1,000
minute Minute (0 to 59)
month Number for the month (1 to 12), if date value. Number of months (0 to 11), if interval value
quarter Quarter (1 to 4)
second Seconds (and fractional seconds)
timezone Time zone offset from UTC, expressed in seconds
timezone_hour Hour portion of the time zone offset from UTC
timezone_minute Minute portion of the time zone offset from UTC
week Number of the week of the year based on ISO 8601 (where the year begins on the Monday of the week that contains January 4th)
year Year as 4-digits
example:
  postgres=# SELECT date_part('day', date '2014-04-25');
  date_part
  -----------
    25
  (1 row)


  postgres=# SELECT date_part('month', date '2014-04-25');
  date_part
  -----------
     4
 (1 row)

 postgres=# SELECT date_part('year', date '2014-04-25');
 date_part
 -----------
  2014
 (1 row)

timestamp values:

   postgres=# SELECT date_part('day', timestamp '2014-04-25 08:44:21');
   date_part
  -----------
    25
  (1 row)

  postgres=# SELECT date_part('month', timestamp '2014-04-25 08:44:21');
  date_part
  -----------
     4
 (1 row)

  postgres=# SELECT date_part('minute', timestamp '2014-04-25 08:44:21');
  date_part
 -----------
    44
 (1 row)

 postgres=# SELECT date_part('hour', timestamp '2014-04-25 08:44:21');
 date_part
 -----------
     8
(1 row)

++time values:++

 postgres=# SELECT date_part('minute', time '08:44:21');
 date_part
 -----------
    44
 (1 row)

 postgres=# SELECT date_part('milliseconds', time '08:44:21.7');
 date_part
 -----------
 21700
(1 row)

interval values:

   postgres=# SELECT date_part('day', interval '5 days 3 hours');
   date_part
   -----------
     5
   (1 row)

  postgres=# SELECT date_part('hour', interval '5 days 3 hours');
  date_part
  -----------
     3
 (1 row)
extract Function:

extract function extracts parts from a date.

Syntax:
  extract( unit from date )

date: T date, timestamp, time, or interval value from which the date part is to be extracted.
unit: The unit type of the interval such as day, month, minute, hour, and so on.

Example:
      postgres=# SELECT extract(day from date '2014-04-25');
      date_part
      -----------
      25
     (1 row)

      postgres=# SELECT extract(month from date '2014-04-25');
      date_part
      -----------
      4
     (1 row)

timestamp values:

      postgres=# SELECT extract(day from timestamp '2014-04-25 08:44:21');
      date_part
      -----------
      25
      (1 row)

      postgres=# SELECT extract(month from timestamp '2014-04-25 08:44:21');
     date_part
     -----------
     4
    (1 row)

time values:

   postgres=# SELECT extract(minute from time '08:44:21');
   date_part
    -----------
    44
   (1 row)

    postgres=# SELECT extract(milliseconds from time '08:44:21.7');
    date_part
    -----------
    21700
   (1 row)

interval values:

    postgres=# SELECT extract(day from interval '5 days 3 hours');
    date_part
     -----------
     5
    (1 row)

    postgres=# SELECT extract(hour from interval '5 days 3 hours');
    date_part
     -----------
     3
    (1 row)
localtime Function:

localtime function returns the current time.

Syntax:
   localtime( [ precision ] )

precision: Optional. The number of digits to round the fractional seconds to.

The localtime function will return the current time of day as a ‘HH:MM:SS.US’ format.

Do not put parentheses () after the localtime function when the precision parameter is not specified.

Example:
  postgres=# SELECT localtime;
  time
  -----------------
  20:18:08.586881
  (1 row)
localtimestamp Function:

localtimestamp function returns the current date and time.

Syntax:
     localtimestamp( [ precision ] )

precision: Optional. The number of digits to round the fractional seconds to.

The localtimestamp function will return the current date as a ‘YYYY-MM-DD HH:MM:SS.US’ format.

Do not put parentheses () after the localtimestamp function when the precision parameter is not specified.

Example:
     postgres=# SELECT localtimestamp;
     timestamp
     ----------------------------
     2014-04-24 20:55:09.808294
    (1 row)
now Function:

now function returns the current date and time with the time zone.

Syntax:
     now( )

precision: Optional. The number of digits to round the fractional seconds to.

The now function will return the current date as a ‘YYYY-MM-DD HH:MM:SS.US+TZ’ format.

Example:
     postgres=# SELECT now();
                  now
     -------------------------------
     2014-04-25 17:01:33.753769+00
     (1 row)

GROUP FUNTION:

The PostgreSQL GROUP BY clause is used in collaboration with the SELECT statement to group together those row in a table that have identical data.This is done to eliminate redundancy in the output and or compute aggregates that apply to these groups.

The GROUP BY clause follow the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

Syntax:
  SELECT column_list
  FROM table_name
  WHERE [conditions]
  GROUP BY column1,column2,.....columnN;

You can use more than one column in the GROUP BY clause.

 

Practical 4 – SQL Operator Between, AND, OR, IN, Like, Null

Queries

  • To get the payment whose payment date is between2007-02-07 and 2007-02-15.
  • write the query to find the records with NULL values.

PostgreSQL BETWEEN operator examples

Let’s take a look at the paymenttable in the sample database. The following query selects any payment whose amount is between 8 and 9:

PostgreSQL IN operator examples

Suppose you want to know the rental information of customer id 1 and 2, you can use the INoperator in the WHERE clause as follows:

 oR Operators :

 

PostgreSQL NOT IN operator

You can combine the IN operator with the NOT operator to select rows whose values do not match the values in the list. The following statement selects rentals of customers whose customer id is not 1 or 2.

Suppose the store manager asks you find a customer that he does not remember the name exactly. He just remembers that customer’s first name begins with something like Jen. How do you find the exact customer that the store manager is asking? You may find the customer in the customer table by looking at the first name column to see if there is any value that begins withJen. It is kind of tedious because there many rows in the customertable.

Fortunately, you can use the PostgreSQL LIKE operator to as the following query:

PostgreSQL <code>LIKE</code> Jen%

Notice that the WHERE clause contains a special expression: the first_name, the LIKE operator and a string that contains a percent (%) character, which is referred as a pattern.

The query returns rows whose values in the first name column begin with Jenand 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 operator to find the matches. PostgreSQL provides two wildcard characters:

  • Percent ( %)  for matching any sequence of characters.
  • Underscore ( _)  for matching any single character.

The syntax of PostgreSQL LIKE operator is as follows:

The expression returns true if the string matches the pattern, otherwise it returns false.

You can combine the LIKE operator with the NOT operator as follows:

The expression returns true if LIKE returns true and vice versa.

If the pattern does not contain any wildcard character, the LIKE operator acts like the equal ( =) operator.

PostgreSQL pattern matching examples

PostgreSQL LIKE examples

See the following example:

How it works.

  • The first expression returns true because the foopattern does not contain any wildcard character so the LIKE operator acts like the equal( =) operator.
  • The second expression returns true because it matches any string that begins with the letter  f and followed by any number of characters.
  • The third expression returns true because the pattern ( _o_) matches any string that begins with any single character, followed by the letter oand ends with any single character.
  • The fourth expression returns false because the pattern  b_ matches any string that begins with the letter  b and followed by any single character.

You can use the wildcard character at the beginning and/or end of the pattern. For example, the following query returns customers whose first name contains  er string e.g., Jenifer,Kimberly, etc.

You can combine the percent ( %) with underscore ( _) to construct a pattern as the following example:

 

The expression matches customer whose first name begins with any single character, is followed by the literal string her, and ends with any number of characters.

PostgreSQL NOT LIKE examples

The following query returns customer whose first name does not begin with Jen:

Notice that we used the NOT LIKE operator in the WHERE clause.

PostgreSQL’s extensions of LIKE operator

PostgreSQL provides the ILIKE operator that acts like the LIKE operator. In addition, theILIKE operator matches value case-insensitively. See the following example:

PostgreSQL ILIKE BAR%

The  BAR% pattern matches any string that begins with BAR, Bar, BaR, etc. If you use the LIKEoperator instead, the query will not return any row.

Practical 3 – What are  Operators in PostgreSQL?

An operator is a reserved word or a character used primarily in a PostgreSQL statement’s WHERE clause to perform operation(s), such as comparisons and arithmetic operations.

Operators are used to specify conditions in a PostgreSQL statement and to serve as conjunctions for multiple conditions in a statement.

  • Arithmetic operators
  • Comparison operators
  • Logical operators
  • Bitwise operators

PostgreSQL Arithmetic Operators:

Assume variable a holds 2 and variable b holds 3, then:

Show Examples

Operator Description Example
+ Addition – Adds values on either side of the operator a + b will give 5
Subtraction – Subtracts right hand operand from left hand operand a – b will give -1
* Multiplication – Multiplies values on either side of the operator a * b will give 6
/ Division – Divides left hand operand by right hand operand b / a will give 1
% Modulus – Divides left hand operand by right hand operand and returns remainder b % a will give 1
^ Exponentiation – This gives the exponent value of the right hand operand a ^ b will give 8
|/ square root |/ 25.0 will give 5
||/ Cube root ||/ 27.0 will give 3
!/ factorial 5 ! will give 120
!! factorial (prefix operator) !! 5 will give 120

PostgreSQL Comparison Operators:

Assume variable a holds 10 and variable b holds 20, then:

Show Examples

Operator Description Example
= Checks if the values of two operands are equal or not, if yes then condition becomes true. (a = b) is not true.
!= Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. (a != b) is true.
<> Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. (a <> b) is true.
> Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true. (a > b) is not true.
< Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true. (a < b) is true.
>= Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true. (a >= b) is not true.
<= Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true. (a <= b) is true.

PostgreSQL Logical Operators:

Here is a list of all the logical operators available in PostgresSQL.

Show Examples

Operator Description
AND The AND operator allows the existence of multiple conditions in a PostgresSQL statement’s WHERE clause.
NOT The NOT operator reverses the meaning of the logical operator with which it is used. Eg. NOT EXISTS, NOT BETWEEN, NOT IN etc. This is negate operator.
OR The OR operator is used to combine multiple conditions in a PostgresSQL statement’s WHERE clause.

PostgreSQL Bit String Operators:

Bitwise operator works on bits and perform bit by bit operation. The truth table for & and | is as follows:

p q p & q p | q
0 0 0 0
0 1 0 1
1 1 1 1
1 0 0 1

Assume if A = 60; and B = 13; now in binary format they will be as follows:

A = 0011 1100

B = 0000 1101


A&B = 0000 1100

A|B = 0011 1101

~A  = 1100 0011

The Bitwise operators supported by PostgreSQL are listed in the following table. Assume variable A holds 60 and variable B holds 13 then:

Show Examples

Operator Description Example
& Binary AND Operator copies a bit to the result if it exists in both operands. (A & B) will give 12 which is 0000 1100
| Binary OR Operator copies a bit if it exists in either operand. (A | B) will give 61 which is 0011 1101
~ Binary Ones Complement Operator is unary and has the effect of ‘flipping’ bits. (~A ) will give -61 which is 1100 0011 in 2’s complement form due to a signed binary number.
<< Binary Left Shift Operator. The left operands value is moved left by the number of bits specified by the right operand. A << 2 will give 240 which is 1111 0000
>> Binary Right Shift Operator. The left operands value is moved right by the number of bits specified by the right operand. A >> 2 will give 15 which is 0000 1111
# bitwise XOR. A # B will give 49 which is 0100 1001

31-8-2016

Practical 2:

Queries For Part 1 of Practical 2

  • Create a table student with constraints
  • Alter one of the field in the table for its datatype
  • Drop contraint from the table field
  • Rename a table

 

Queries For Part 2 of Practical 2

  • Show all the fields in actor table
  • show top ten results from actor
  • show last ten results from actor
  • show results where category_id is in between 10 o 15

Queries for Part 2 of Practical 2

  • Insert rows to table created by you
  • delete rows using where clause
  • Update the fields of table using where clause

 

Part 2 of Practical 2

The PostgreSQL UPDATE Query is used to modify the existing records in a table. You can use WHERE clause with UPDATE query to update selected rows otherwise all the rows would be updated.

Syntax:

The basic syntax of UPDATE query with WHERE clause is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

You can combine N number of conditions using AND or OR operators.

The PostgreSQL DELETE Query is used to delete the existing records from a table. You can use WHERE clause with DELETE query to delete selected rows, otherwise all the records would be deleted.

Syntax:

The basic syntax of DELETE query with WHERE clause is as follows:

DELETE FROM table_name
WHERE [condition];

You can combine N number of conditions using AND or OR operators.

Example

DELETE FROM category WHERE category_id = 2;

Part 1 of Practical 2:

Write the queries for Data Definition (CREATE, DROP, ALTER and RENAME)

To Rename Table

ALTER TABLE xxx RENAME TO yyy;
ALTER TABLE yyy RENAME COLUMN  col1 TO colA;
ALTER TABLE yyy RENAME COLUMN  col2 TO colB;

To Alter Table

The basic syntax of ALTER TABLE to add a new column in an existing table is as follows:

ALTER TABLE table_name ADD column_name datatype;

The basic syntax of ALTER TABLE to DROP COLUMN in an existing table is as follows:

ALTER TABLE table_name DROP COLUMN column_name;

The basic syntax of ALTER TABLE to change the DATA TYPE of a column in a table is as follows:

ALTER TABLE test ALTER COLUMN id  TYPE integer USING (id::integer);

The basic syntax of ALTER TABLE to add a NOT NULL constraint to a column in a table is as follows:

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows:

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

The basic syntax of ALTER TABLE to ADD CHECK CONSTRAINT to a table is as follows:

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

The basic syntax of ALTER TABLE to ADD PRIMARY KEY constraint to a table is as follows:

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

The basic syntax of ALTER TABLE to DROP CONSTRAINT from a table is as follows:

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

To Drop Table

droop table schema.tablename;

Insert Values

Basic syntax of INSERT INTO statement is as follows.

INSERT INTO TABLE_NAME (column1, column2, column3,…columnN)]

VALUES (value1, value2, value3,…valueN);

or

INSERT INTO Schemaname.TABLE_NAME (column1, column2, column3,…columnN)]

VALUES (value1, value2, value3,…valueN);

# Create a table

create table myschema.company(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULLADDRESS CHAR (25), SALARY DECIMAL (18,2), PRIMARY KEY (ID);

Create SCHEMA

 The basic syntax CREATE SCHEMA is as follows

CREATE SCHEMA name;

 name is the name of the schema.

Drop Schema

Drop schema  name

Data Types

Name

Storage Size

Description

Range

smallint

2 bytes

small-range integer

-32768 to +32767

integer

4 bytes

typical choice for integer

-2147483648 to +2147483647

bigint

8 bytes

large-range integer

-9223372036854775808 to

9223372036854775807

decimal

variable

user-specified precision,exact

up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

numeric

variable

user-specified precision,exact

up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

real

4 bytes

variable-precision,inexact

6 decimal digits precision

double precision

8 bytes

variable-precision,inexact

15 decimal digits precision

smallserial

2 bytes

small autoincrementing integer

1 to 32767

serial

4 bytes

autoincrementing integer

1 to 2147483647

bigserial

8 bytes

large autoincrementing integer

1 to 9223372036854775807

Character Types

The table below lists general-purpose character types available in PostgreSQL.

Name

Description

character varying(n), varchar(n)

variable-length with limit

character(n), char(n)

fixed-length, blank padded

text

variable unlimited length

 

Practical No: 1

Installation and configuration of Postgresql and Pgadmin on machine and preparing to accept connections from all Ip addresses.

 

The following links are just for help: 1. https://wiki.postgresql.org/wiki/Detailed_installation_guides

2. https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-14-04

3. http://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html

  1. Configuring PostgreSQL Database Servers -https://www.digitalocean.com/community/tutorials/how-to-back-up-restore-and-migrate-postgresql-databases-with-barman-on-centos-7