Category: postgresql

PostgreSQL – NULL Values ”; Previous Next The PostgreSQL NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank. A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different from a zero value or a field that contains spaces. Syntax The basic syntax of using NULL while creating a table is as follows − CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); Here, NOT NULL signifies that column should always accept an explicit value of the given data type. There are two columns where we did not use NOT NULL. Hence, this means these columns could be NULL.> A field with a NULL value is one that has been left blank during record creation. Example The NULL value can cause problems when selecting data, because when comparing an unknown value to any other value, the result is always unknown and not included in the final results. Consider the following table, COMPANY having the following records − ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Let us use the UPDATE statement to set few nullable values as NULL as follows − testdb=# UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7); Now, COMPANY table should have the following records − 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 | | 7 | James | 24 | | (7 rows) Next, let us see the usage of IS NOT NULL operator to list down all the records where SALARY is not NULL − testdb=# SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL; The above given PostgreSQL statement will produce the following result − 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 (5 rows) The following is the usage of IS NULL operator which will list down all the records where SALARY is NULL − testdb=# SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL; The above given PostgreSQL statement will produce the following result − id | name | age | address | salary —-+——-+—–+———+——– 6 | Kim | 22 | | 7 | James | 24 | | (2 rows) Print Page Previous Next Advertisements ”;

PostgreSQL – ALTER TABLE Command ”; Previous Next The PostgreSQL ALTER TABLE command is used to add, delete or modify columns in an existing table. You would also use ALTER TABLE command to add and drop various constraints on an existing table. Syntax 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 table_name ALTER COLUMN column_name TYPE datatype; 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; If you are using MySQL, the code is as follows − ALTER TABLE table_name DROP INDEX MyUniqueConstraint; The basic syntax of ALTER TABLE to DROP PRIMARY KEY constraint from a table is as follows − ALTER TABLE table_name DROP CONSTRAINT MyPrimaryKey; If you are using MySQL, the code is as follows − ALTER TABLE table_name DROP PRIMARY KEY; Example Consider our COMPANY table has the following records − 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 The following is the example to ADD a new column in an existing table − testdb=# ALTER TABLE COMPANY ADD GENDER char(1); Now, COMPANY table is changed and the following would be the output from SELECT statement − id | name | age | address | salary | gender —-+——-+—–+————-+——–+——– 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 | (7 rows) The following is the example to DROP gender column from existing table − testdb=# ALTER TABLE COMPANY DROP GENDER; Now, COMPANY table is changed and the following would be the output from SELECT statement − 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 Print Page Previous Next Advertisements ”;

PostgreSQL – LOCKS ”; Previous Next Locks or Exclusive Locks or Write Locks prevent users from modifying a row or an entire table. Rows modified by UPDATE and DELETE are then exclusively locked automatically for the duration of the transaction. This prevents other users from changing the row until the transaction is either committed or rolled back. The only time when users must wait for other users is when they are trying to modify the same row. If they modify different rows, no waiting is necessary. SELECT queries never have to wait. The database performs locking automatically. In certain cases, however, locking must be controlled manually. Manual locking can be done by using the LOCK command. It allows specification of a transaction”s lock type and scope. Syntax for LOCK command The basic syntax for LOCK command is as follows − LOCK [ TABLE ] name IN lock_mode name − The name (optionally schema-qualified) of an existing table to lock. If ONLY is specified before the table name, only that table is locked. If ONLY is not specified, the table and all its descendant tables (if any) are locked. lock_mode − The lock mode specifies which locks this lock conflicts with. If no lock mode is specified, then ACCESS EXCLUSIVE, the most restrictive mode, is used. Possible values are: ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE. Once obtained, the lock is held for the remainder of the current transaction. There is no UNLOCK TABLE command; locks are always released at the transaction end. DeadLocks Deadlocks can occur when two transactions are waiting for each other to finish their operations. While PostgreSQL can detect them and end them with a ROLLBACK, deadlocks can still be inconvenient. To prevent your applications from running into this problem, make sure to design them in such a way that they will lock objects in the same order. Advisory Locks PostgreSQL provides means for creating locks that have application-defined meanings. These are called advisory locks. As the system does not enforce their use, it is up to the application to use them correctly. Advisory locks can be useful for locking strategies that are an awkward fit for the MVCC model. For example, a common use of advisory locks is to emulate pessimistic locking strategies typical of the so-called “flat file” data management systems. While a flag stored in a table could be used for the same purpose, advisory locks are faster, avoid table bloat, and are automatically cleaned up by the server at the end of the session. Example Consider the table COMPANY having records as follows − testdb# select * from COMPANY; 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 (7 rows) The following example locks the COMPANY table within the testdb database in ACCESS EXCLUSIVE mode. The LOCK statement works only in a transaction mode − testdb=#BEGIN; LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE; The above given PostgreSQL statement will produce the following result − LOCK TABLE The above message indicates that the table is locked until the transaction ends and to finish the transaction you will have to either rollback or commit the transaction. Print Page Previous Next Advertisements ”;

PostgreSQL – TRANSACTIONS ”; Previous Next A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program. A transaction is the propagation of one or more changes to the database. For example, if you are creating a record, updating a record, or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors. Practically, you will club many PostgreSQL queries into a group and you will execute all of them together as a part of a transaction. Properties of Transactions Transactions have the following four standard properties, usually referred to by the acronym ACID − Atomicity − Ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state. Consistency − Ensures that the database properly changes states upon a successfully committed transaction. Isolation − Enables transactions to operate independently of and transparent to each other. Durability − Ensures that the result or effect of a committed transaction persists in case of a system failure. Transaction Control The following commands are used to control transactions − BEGIN TRANSACTION − To start a transaction. COMMIT − To save the changes, alternatively you can use END TRANSACTION command. ROLLBACK − To rollback the changes. Transactional control commands are only used with the DML commands INSERT, UPDATE and DELETE only. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database. The BEGIN TRANSACTION Command Transactions can be started using BEGIN TRANSACTION or simply BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command is encountered. But a transaction will also ROLLBACK if the database is closed or if an error occurs. The following is the simple syntax to start a transaction − BEGIN; or BEGIN TRANSACTION; The COMMIT Command The COMMIT command is the transactional command used to save changes invoked by a transaction to the database. The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command. The syntax for COMMIT command is as follows − COMMIT; or END TRANSACTION; The ROLLBACK Command The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued. The syntax for ROLLBACK command is as follows − ROLLBACK; Example Consider the COMPANY table is having the following records − 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 Now, let us start a transaction and delete records from the table having age = 25 and finally we use ROLLBACK command to undo all the changes. testdb=# BEGIN; DELETE FROM COMPANY WHERE AGE = 25; ROLLBACK; If you will check COMPANY table is still having the following records − 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 Now, let us start another transaction and delete records from the table having age = 25 and finally we use COMMIT command to commit all the changes. testdb=# BEGIN; DELETE FROM COMPANY WHERE AGE = 25; COMMIT; If you will check the COMPANY table, it still has the following records − id | name | age | address | salary —-+——-+—–+————+——– 1 | Paul | 32 | California | 20000 3 | Teddy | 23 | Norway | 20000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 (5 rows) Print Page Previous Next Advertisements ”;

PostgreSQL – ALIAS Syntax ”; Previous Next You can rename a table or a column temporarily by giving another name, which is known as ALIAS. The use of table aliases means to rename a table in a particular PostgreSQL statement. Renaming is a temporary change and the actual table name does not change in the database. The column aliases are used to rename a table”s columns for the purpose of a particular PostgreSQL query. Syntax The basic syntax of table alias is as follows − SELECT column1, column2…. FROM table_name AS alias_name WHERE [condition]; The basic syntax of column alias is as follows − SELECT column_name AS alias_name FROM table_name WHERE [condition]; Example Consider the following two tables, (a) COMPANY table is as follows − testdb=# select * from COMPANY; 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 (7 rows) (b) Another table is DEPARTMENT as follows − id | dept | emp_id —-+————–+——– 1 | IT Billing | 1 2 | Engineering | 2 3 | Finance | 7 4 | Engineering | 3 5 | Finance | 4 6 | Engineering | 5 7 | Finance | 6 (7 rows) Now, following is the usage of TABLE ALIAS where we use C and D as aliases for COMPANY and DEPARTMENT tables, respectively − testdb=# SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID; The above given PostgreSQL statement will produce the following result − id | name | age | dept —-+——-+—–+———— 1 | Paul | 32 | IT Billing 2 | Allen | 25 | Engineering 7 | James | 24 | Finance 3 | Teddy | 23 | Engineering 4 | Mark | 25 | Finance 5 | David | 27 | Engineering 6 | Kim | 22 | Finance (7 rows) Let us see an example for the usage of COLUMN ALIAS where COMPANY_ID is an alias of ID column and COMPANY_NAME is an alias of name column − testdb=# SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID; The above given PostgreSQL statement will produce the following result − company_id | company_name | age | dept ————+————–+—–+———— 1 | Paul | 32 | IT Billing 2 | Allen | 25 | Engineering 7 | James | 24 | Finance 3 | Teddy | 23 | Engineering 4 | Mark | 25 | Finance 5 | David | 27 | Engineering 6 | Kim | 22 | Finance (7 rows) Print Page Previous Next Advertisements ”;

PostgreSQL – HAVING Clause ”; Previous Next The HAVING clause allows us to pick out particular rows where the function”s result meets some condition. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. Syntax The following is the position of the HAVING clause in a SELECT query − SELECT FROM WHERE GROUP BY HAVING ORDER BY The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following is the syntax of the SELECT statement, including the HAVING clause − SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2 Example Consider the table COMPANY having records as follows − # select * from COMPANY; 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 (7 rows) The following is an example, which would display record for which the name count is less than 2 − testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2; This would produce the following result − name ——- Teddy Paul Mark David Allen Kim James (7 rows) Now, let us create three more records in COMPANY table using the following INSERT statements − INSERT INTO COMPANY VALUES (8, ”Paul”, 24, ”Houston”, 20000.00); INSERT INTO COMPANY VALUES (9, ”James”, 44, ”Norway”, 5000.00); INSERT INTO COMPANY VALUES (10, ”James”, 45, ”Texas”, 5000.00); Now, our table has the following records with duplicate names − 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 8 | Paul | 24 | Houston | 20000 9 | James | 44 | Norway | 5000 10 | James | 45 | Texas | 5000 (10 rows) The following is the example, which would display record for which the name count is greater than 1 − testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1; This would produce the following result − name ——- Paul James (2 rows) Print Page Previous Next Advertisements ”;

PostgreSQL – DISTINCT Keyword ”; Previous Next The PostgreSQL DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records. There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records. Syntax The basic syntax of DISTINCT keyword to eliminate duplicate records is as follows − SELECT DISTINCT column1, column2,…..columnN FROM table_name WHERE [condition] Example Consider the table COMPANY having records as follows − # select * from COMPANY; 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 (7 rows) Let us add two more records to this table as follows − INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (8, ”Paul”, 32, ”California”, 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (9, ”Allen”, 25, ”Texas”, 15000.00 ); Now, the records in the COMPANY table would be − 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 8 | Paul | 32 | California | 20000 9 | Allen | 25 | Texas | 15000 (9 rows) First, let us see how the following SELECT query returns duplicate salary records − testdb=# SELECT name FROM COMPANY; This would produce the following result − name ——- Paul Allen Teddy Mark David Kim James Paul Allen (9 rows) Now, let us use DISTINCT keyword with the above SELECT query and see the result − testdb=# SELECT DISTINCT name FROM COMPANY; This would produce the following result where we do not have any duplicate entry − name ——- Teddy Paul Mark David Allen Kim James (7 rows) Print Page Previous Next Advertisements ”;

PostgreSQL – JOINS ”; Previous Next The PostgreSQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each. Join Types in PostgreSQL are − The CROSS JOIN The INNER JOIN The LEFT OUTER JOIN The RIGHT OUTER JOIN The FULL OUTER JOIN Before we proceed, let us consider two tables, COMPANY and DEPARTMENT. We already have seen INSERT statements to populate COMPANY table. So just let us assume the list of records available in COMPANY table − id | name | age | address | salary | join_date —-+——-+—–+———–+——–+———– 1 | Paul | 32 | California| 20000 | 2001-07-13 3 | Teddy | 23 | Norway | 20000 | 4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13 5 | David | 27 | Texas | 85000 | 2007-12-13 2 | Allen | 25 | Texas | | 2007-12-13 8 | Paul | 24 | Houston | 20000 | 2005-07-13 9 | James | 44 | Norway | 5000 | 2005-07-13 10 | James | 45 | Texas | 5000 | 2005-07-13 Another table is DEPARTMENT, has the following definition − CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL ); Here is the list of INSERT statements to populate DEPARTMENT table − INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, ”IT Billing”, 1 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, ”Engineering”, 2 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, ”Finance”, 7 ); Finally, we have the following list of records available in DEPARTMENT table − id | dept | emp_id —-+————-+——– 1 | IT Billing | 1 2 | Engineering | 2 3 | Finance | 7 The CROSS JOIN A CROSS JOIN matches every row of the first table with every row of the second table. If the input tables have x and y columns, respectively, the resulting table will have x+y columns. Because CROSS JOINs have the potential to generate extremely large tables, care must be taken to use them only when appropriate. The following is the syntax of CROSS JOIN − SELECT … FROM table1 CROSS JOIN table2 … Based on the above tables, we can write a CROSS JOIN as follows − testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT; The above given query will produce the following result − emp_id| name | dept ——|——-|————– 1 | Paul | IT Billing 1 | Teddy | IT Billing 1 | Mark | IT Billing 1 | David | IT Billing 1 | Allen | IT Billing 1 | Paul | IT Billing 1 | James | IT Billing 1 | James | IT Billing 2 | Paul | Engineering 2 | Teddy | Engineering 2 | Mark | Engineering 2 | David | Engineering 2 | Allen | Engineering 2 | Paul | Engineering 2 | James | Engineering 2 | James | Engineering 7 | Paul | Finance 7 | Teddy | Finance 7 | Mark | Finance 7 | David | Finance 7 | Allen | Finance 7 | Paul | Finance 7 | James | Finance 7 | James | Finance The INNER JOIN A INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows, which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of table1 and table2 are combined into a result row. An INNER JOIN is the most common type of join and is the default type of join. You can use INNER keyword optionally. The following is the syntax of INNER JOIN − SELECT table1.column1, table2.column2… FROM table1 INNER JOIN table2 ON table1.common_filed = table2.common_field; Based on the above tables, we can write an INNER JOIN as follows − testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; The above given query will produce the following result − emp_id | name | dept ——–+——-+———— 1 | Paul | IT Billing 2 | Allen | Engineering The LEFT OUTER JOIN The OUTER JOIN is an extension of the INNER JOIN. SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL and PostgreSQL supports all of these. In case of LEFT OUTER JOIN, an inner join is performed first. Then, for each row in table T1 that does not satisfy the join condition with any row in table T2, a joined row is added with null values in columns of T2. Thus, the joined table always has at least one row for each row in T1. The following is the syntax of LEFT OUTER JOIN − SELECT … FROM table1 LEFT OUTER JOIN table2 ON conditional_expression … Based on the above tables, we can write an inner join as follows − testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; The above given query will produce the following result − emp_id | name | dept ——–+——-+———— 1 | Paul | IT Billing 2 | Allen | Engineering | James | | David | | Paul | | Mark | | Teddy | | James | The RIGHT OUTER JOIN First, an inner join is performed. Then, for each row in table T2 that does not satisfy the join condition with any row in table T1, a joined row is added with null values in columns of T1. This is the converse of a left join; the result table will always have a row for each row in T2. The following is the syntax of RIGHT OUTER JOIN − SELECT … FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression … Based on the above tables, we can write an inner join

PostgreSQL – DELETE Query ”; Previous Next The PostgreSQL DELETE Query is used to delete the existing records from a table. You can use WHERE clause with DELETE query to delete the 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 Consider the table COMPANY, having records as follows − # select * from COMPANY; 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 (7 rows) The following is an example, which would DELETE a customer whose ID is 7 − testdb=# DELETE FROM COMPANY WHERE ID = 2; Now, COMPANY table will have the following records − id | name | age | address | salary —-+——-+—–+————-+——– 1 | Paul | 32 | California | 20000 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 (6 rows) If you want to DELETE all the records from COMPANY table, you do not need to use WHERE clause with DELETE queries, which would be as follows − testdb=# DELETE FROM COMPANY; Now, COMPANY table does not have any record because all the records have been deleted by the DELETE statement. Print Page Previous Next Advertisements ”;

PostgreSQL – UPDATE Query ”; Previous Next The PostgreSQL UPDATE Query is used to modify the existing records in a table. You can use WHERE clause with UPDATE query to update the 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. Example Consider the table COMPANY, having records as follows − testdb# select * from COMPANY; 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 (7 rows) The following is an example, which would update ADDRESS for a customer, whose ID is 6 − testdb=# UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3; Now, COMPANY table would have the following records − id | name | age | address | salary —-+——-+—–+————+——– 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 3 | Teddy | 23 | Norway | 15000 (7 rows) If you want to modify all ADDRESS and SALARY column values in COMPANY table, you do not need to use WHERE clause and UPDATE query would be as follows − testdb=# UPDATE COMPANY SET ADDRESS = ”Texas”, SALARY=20000; Now, COMPANY table will have the following records − id | name | age | address | salary —-+——-+—–+———+——– 1 | Paul | 32 | Texas | 20000 2 | Allen | 25 | Texas | 20000 4 | Mark | 25 | Texas | 20000 5 | David | 27 | Texas | 20000 6 | Kim | 22 | Texas | 20000 7 | James | 24 | Texas | 20000 3 | Teddy | 23 | Texas | 20000 (7 rows) Print Page Previous Next Advertisements ”;