Category: postgresql

PostgreSQL – Sub Queries ”; Previous Next 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. There are a few rules that subqueries must follow − 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 Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows − SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE]) Example Consider the COMPANY table 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 (7 rows) Now, let us check the following sub-query with SELECT statement − testdb=# SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000) ; This would produce the following result − id | name | age | address | salary —-+——-+—–+————-+——– 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (2 rows) Subqueries with the INSERT Statement Subqueries also can be used with INSERT statements. 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. The basic syntax is as follows − INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ] ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ] Example Consider a table COMPANY_BKP, with similar structure as COMPANY table and can be created using the same CREATE TABLE using COMPANY_BKP as the table name. Now, to copy complete COMPANY table into COMPANY_BKP, following is the syntax − testdb=# INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY) ; 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. The basic syntax is as follows − UPDATE table SET column_name = new_value [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ] Example Assuming, we have COMPANY_BKP table available, which is backup of the COMPANY table. The following example updates SALARY by 0.50 times in the COMPANY table for all the customers, whose AGE is greater than or equal to 27 − testdb=# UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 ); This would affect two rows and finally the COMPANY table would have the following records − id | name | age | address | salary —-+——-+—–+————-+——– 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 1 | Paul | 32 | California | 10000 5 | David | 27 | Texas | 42500 (7 rows) Subqueries with the DELETE Statement The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above. The basic syntax is as follows − DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ] Example Assuming, we have COMPANY_BKP table available, which is a backup of the COMPANY table. The following example deletes records from the COMPANY table for all the customers, whose AGE is greater than or equal to 27 − testdb=# DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 ); This would affect two rows and finally the COMPANY table would have the following records − id | name | age | address | salary —-+——-+—–+————-+——– 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 5 | David | 27 | Texas | 42500 (6 rows) Print Page Previous Next Advertisements ”;

PostgreSQL – C/C++ Interface ”; Previous Next This tutorial is going to use libpqxx library, which is the official C++ client API for PostgreSQL. The source code for libpqxx is available under the BSD license, so you are free to download it, pass it on to others, change it, sell it, include it in your own code, and share your changes with anyone you choose. Installation The the latest version of libpqxx is available to be downloaded from the link Download Libpqxx. So download the latest version and follow the following steps − wget http://pqxx.org/download/software/libpqxx/libpqxx-4.0.tar.gz tar xvfz libpqxx-4.0.tar.gz cd libpqxx-4.0 ./configure make make install Before you start using C/C++ PostgreSQL interface, find the pg_hba.conf file in your PostgreSQL installation directory and add the following line − # IPv4 local connections: host all all 127.0.0.1/32 md5 You can start/restart postgres server in case it is not running using the following command − [root@host]# service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ] C/C++ Interface APIs The following are important interface routines which can sufice your requirement to work with PostgreSQL database from your C/C++ program. If you are looking for a more sophisticated application then you can look into the libpqxx official documentation, or you can use commercially available APIs. S. No. API & Description 1 pqxx::connection C( const std::string & dbstring ) This is a typedef which will be used to connect to the database. Here, dbstring provides required parameters to connect to the datbase, for example dbname = testdb user = postgres password=pass123 hostaddr=127.0.0.1 port=5432. If connection is setup successfully then it creates C with connection object which provides various useful function public function. 2 C.is_open() The method is_open() is a public method of connection object and returns boolean value. If connection is active, then this method returns true otherwise it returns false. 3 C.disconnect() This method is used to disconnect an opened database connection. 4 pqxx::work W( C ) This is a typedef which will be used to create a transactional object using connection C, which ultimately will be used to execute SQL statements in transactional mode. If transaction object gets created successfully, then it is assigned to variable W which will be used to access public methods related to transactional object. 5 W.exec(const std::string & sql) This public method from transactional object will be used to execute SQL statement. 6 W.commit() This public method from transactional object will be used to commit the transaction. 7 W.abort() This public method from transactional object will be used to rollback the transaction. 8 pqxx::nontransaction N( C ) This is a typedef which will be used to create a non-transactional object using connection C, which ultimately will be used to execute SQL statements in non-transactional mode. If transaction object gets created successfully, then it is assigned to variable N which will be used to access public methods related to non-transactional object. 9 N.exec(const std::string & sql) This public method from non-transactional object will be used to execute SQL statement and returns a result object which is actually an interator holding all the returned records. Connecting To Database The following C code segment shows how to connect to an existing database running on local machine at port 5432. Here, I used backslash for line continuation. #include <iostream> #include <pqxx/pqxx> using namespace std; using namespace pqxx; int main(int argc, char* argv[]) { try { connection C(“dbname = testdb user = postgres password = cohondob hostaddr = 127.0.0.1 port = 5432”); if (C.is_open()) { cout << “Opened database successfully: ” << C.dbname() << endl; } else { cout << “Can”t open database” << endl; return 1; } C.disconnect (); } catch (const std::exception &e) { cerr << e.what() << std::endl; return 1; } } Now, let us compile and run the above program to connect to our database testdb, which is already available in your schema and can be accessed using user postgres and password pass123. You can use the user ID and password based on your database setting. Remember to keep the -lpqxx and -lpq in the given order! Otherwise, the linker will complain bitterly about the missing functions with names starting with “PQ.” $g++ test.cpp -lpqxx -lpq $./a.out Opened database successfully: testdb Create a Table The following C code segment will be used to create a table in previously created database − #include <iostream> #include <pqxx/pqxx> using namespace std; using namespace pqxx; int main(int argc, char* argv[]) { char * sql; try { connection C(“dbname = testdb user = postgres password = cohondob hostaddr = 127.0.0.1 port = 5432”); if (C.is_open()) { cout << “Opened database successfully: ” << C.dbname() << endl; } else { cout << “Can”t open database” << endl; return 1; } /* Create SQL statement */ sql = “CREATE TABLE COMPANY(” “ID INT PRIMARY KEY NOT NULL,” “NAME TEXT NOT NULL,” “AGE INT NOT NULL,” “ADDRESS CHAR(50),” “SALARY REAL );”; /* Create a transactional object. */ work W(C); /* Execute SQL query */ W.exec( sql ); W.commit(); cout << “Table created successfully” << endl; C.disconnect (); } catch (const std::exception &e) { cerr << e.what() << std::endl; return 1; } return 0; } When the above given program is compiled and executed, it will create COMPANY table in your testdb database and will display the following statements − Opened database successfully: testdb Table created successfully INSERT Operation The following C code segment shows how we can create records in our COMPANY table created in above example − #include <iostream> #include <pqxx/pqxx> using namespace std; using namespace pqxx; int main(int argc, char* argv[]) { char * sql; try { connection C(“dbname = testdb user = postgres password = cohondob hostaddr = 127.0.0.1 port = 5432”); if (C.is_open()) { cout << “Opened database successfully: ” << C.dbname() << endl; } else { cout << “Can”t open database” << endl; return 1; } /* Create SQL statement */ sql = “INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) ” “VALUES (1, ”Paul”, 32, ”California”, 20000.00

PostgreSQL – Functions ”; Previous Next PostgreSQL functions, also known as Stored Procedures, allow you to carry out operations that would normally take several queries and round trips in a single function within the database. Functions allow database reuse as other applications can interact directly with your stored procedures instead of a middle-tier or duplicating code. Functions can be created in a language of your choice like SQL, PL/pgSQL, C, Python, etc. Syntax The basic syntax to create a function is as follows − CREATE [OR REPLACE] FUNCTION function_name (arguments) RETURNS return_datatype AS $variable_name$ DECLARE declaration; […] BEGIN < function_body > […] RETURN { variable_name | value } END; LANGUAGE plpgsql; Where, function-name specifies the name of the function. [OR REPLACE] option allows modifying an existing function. The function must contain a return statement. RETURN clause specifies that data type you are going to return from the function. The return_datatype can be a base, composite, or domain type, or can reference the type of a table column. function-body contains the executable part. The AS keyword is used for creating a standalone function. plpgsql is the name of the language that the function is implemented in. Here, we use this option for PostgreSQL, it Can be SQL, C, internal, or the name of a user-defined procedural language. For backward compatibility, the name can be enclosed by single quotes. Example The following example illustrates creating and calling a standalone function. This function returns the total number of records in the COMPANY table. We will use the COMPANY table, which has the following records − 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) Function totalRecords() is as follows − CREATE OR REPLACE FUNCTION totalRecords () RETURNS integer AS $total$ declare total integer; BEGIN SELECT count(*) into total FROM COMPANY; RETURN total; END; $total$ LANGUAGE plpgsql; When the above query is executed, the result would be − testdb# CREATE FUNCTION Now, let us execute a call to this function and check the records in the COMPANY table testdb=# select totalRecords(); When the above query is executed, the result would be − totalrecords ————– 7 (1 row) Print Page Previous Next Advertisements ”;

PostgreSQL – VIEWS ”; Previous Next Views are pseudo-tables. That is, they are not real tables; nevertheless appear as ordinary tables to SELECT. A view can represent a subset of a real table, selecting certain columns or certain rows from an ordinary table. A view can even represent joined tables. Because views are assigned separate permissions, you can use them to restrict table access so that the users see only specific rows or columns of a table. A view can contain all rows of a table or selected rows from one or more tables. A view can be created from one or many tables, which depends on the written PostgreSQL query to create a view. Views, which are kind of virtual tables, allow users to do the following − Structure data in a way that users or classes of users find natural or intuitive. Restrict access to the data such that a user can only see limited data instead of complete table. Summarize data from various tables, which can be used to generate reports. Since views are not ordinary tables, you may not be able to execute a DELETE, INSERT, or UPDATE statement on a view. However, you can create a RULE to correct this problem of using DELETE, INSERT or UPDATE on a view. Creating Views The PostgreSQL views are created using the CREATE VIEW statement. The PostgreSQL views can be created from a single table, multiple tables, or another view. The basic CREATE VIEW syntax is as follows − CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column1, column2….. FROM table_name WHERE [condition]; You can include multiple tables in your SELECT statement in very similar way as you use them in normal PostgreSQL SELECT query. If the optional TEMP or TEMPORARY keyword is present, the view will be created in the temporary space. Temporary views are automatically dropped at the end of the current session. 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, following is an example to create a view from COMPANY table. This view would be used to have only few columns from COMPANY table − testdb=# CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE FROM COMPANY; Now, you can query COMPANY_VIEW in a similar way as you query an actual table. Following is the example − testdb=# SELECT * FROM COMPANY_VIEW; This would produce the following result − id | name | age —-+——-+—– 1 | Paul | 32 2 | Allen | 25 3 | Teddy | 23 4 | Mark | 25 5 | David | 27 6 | Kim | 22 7 | James | 24 (7 rows) Dropping Views To drop a view, simply use the DROP VIEW statement with the view_name. The basic DROP VIEW syntax is as follows − testdb=# DROP VIEW view_name; The following command will delete COMPANY_VIEW view, which we created in the last section − testdb=# DROP VIEW COMPANY_VIEW; Print Page Previous Next Advertisements ”;

PostgreSQL – TRUNCATE TABLE Command ”; Previous Next The PostgreSQL TRUNCATE TABLE command is used to delete complete data from an existing table. You can also use DROP TABLE command to delete complete table but it would remove complete table structure from the database and you would need to re-create this table once again if you wish to store some data. It has the same effect as DELETE on each table, but since it does not actually scan the tables, it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables. Syntax The basic syntax of TRUNCATE TABLE is as follows − TRUNCATE TABLE table_name; Example Consider the 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 (7 rows) The following is the example to truncate − testdb=# TRUNCATE TABLE COMPANY; Now, COMPANY table is truncated and the following would be the output of SELECT statement − testdb=# SELECT * FROM CUSTOMERS; id | name | age | address | salary —-+——+—–+———+——– (0 rows) Print Page Previous Next Advertisements ”;

PostgreSQL – INDEXES ”; Previous Next Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. For example, if you want to reference all pages in a book that discusses a certain topic, you have to first refer to the index, which lists all topics alphabetically and then refer to one or more specific page numbers. An index helps to speed up SELECT queries and WHERE clauses; however, it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data. Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order. Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there”s an index. The CREATE INDEX Command The basic syntax of CREATE INDEX is as follows − CREATE INDEX index_name ON table_name; Index Types PostgreSQL provides several index types: B-tree, Hash, GiST, SP-GiST and GIN. Each Index type uses a different algorithm that is best suited to different types of queries. By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations. Single-Column Indexes A single-column index is one that is created based on only one table column. The basic syntax is as follows − CREATE INDEX index_name ON table_name (column_name); Multicolumn Indexes A multicolumn index is defined on more than one column of a table. The basic syntax is as follows − CREATE INDEX index_name ON table_name (column1_name, column2_name); Whether to create a single-column index or a multicolumn index, take into consideration the column(s) that you may use very frequently in a query”s WHERE clause as filter conditions. Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the multicolumn index would be the best choice. Unique Indexes Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows − CREATE UNIQUE INDEX index_name on table_name (column_name); Partial Indexes A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicate. The basic syntax is as follows − CREATE INDEX index_name on table_name (conditional_expression); Implicit Indexes Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints. Example The following is an example where we will create an index on COMPANY table for salary column − # CREATE INDEX salary_index ON COMPANY (salary); Now, let us list down all the indices available on COMPANY table using d company command. # d company This will produce the following result, where company_pkey is an implicit index, which got created when the table was created. Table “public.company” Column | Type | Modifiers ———+—————+———– id | integer | not null name | text | not null age | integer | not null address | character(50) | salary | real | Indexes: “company_pkey” PRIMARY KEY, btree (id) “salary_index” btree (salary) You can list down the entire indexes database wide using the di command − The DROP INDEX Command An index can be dropped using PostgreSQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved. The basic syntax is as follows − DROP INDEX index_name; You can use following statement to delete previously created index − # DROP INDEX salary_index; When Should Indexes be Avoided? Although indexes are intended to enhance a database”s performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered − Indexes should not be used on small tables. Tables that have frequent, large batch update or insert operations. Indexes should not be used on columns that contain a high number of NULL values. Columns that are frequently manipulated should not be indexed. Print Page Previous Next Advertisements ”;

PostgreSQL – TRIGGERS ”; Previous Next PostgreSQL Triggers are database callback functions, which are automatically performed/invoked when a specified database event occurs. The following are important points about PostgreSQL triggers − PostgreSQL trigger can be specified to fire Before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE or DELETE is attempted) After the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed) Instead of the operation (in the case of inserts, updates or deletes on a view) A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies. In contrast, a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies. Both, the WHEN clause and the trigger actions, may access elements of the row being inserted, deleted or updated using references of the form NEW.column-name and OLD.column-name, where column-name is the name of a column from the table that the trigger is associated with. If a WHEN clause is supplied, the PostgreSQL statements specified are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the PostgreSQL statements are executed for all rows. If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name. The BEFORE, AFTER or INSTEAD OF keyword determines when the trigger actions will be executed relative to the insertion, modification or removal of the associated row. Triggers are automatically dropped when the table that they are associated with is dropped. The table to be modified must exist in the same database as the table or view to which the trigger is attached and one must use just tablename, not database.tablename. A CONSTRAINT option when specified creates a constraint trigger. This is the same as a regular trigger except that the timing of the trigger firing can be adjusted using SET CONSTRAINTS. Constraint triggers are expected to raise an exception when the constraints they implement are violated. Syntax The basic syntax of creating a trigger is as follows − CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name ON table_name [ — Trigger logic goes here…. ]; Here, event_name could be INSERT, DELETE, UPDATE, and TRUNCATE database operation on the mentioned table table_name. You can optionally specify FOR EACH ROW after table name. The following is the syntax of creating a trigger on an UPDATE operation on one or more specified columns of a table as follows − CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table_name [ — Trigger logic goes here…. ]; Example Let us consider a case where we want to keep audit trial for every record being inserted in COMPANY table, which we will create newly as follows (Drop COMPANY table if you already have it). testdb=# CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); To keep audit trial, we will create a new table called AUDIT where log messages will be inserted whenever there is an entry in COMPANY table for a new record − testdb=# CREATE TABLE AUDIT( EMP_ID INT NOT NULL, ENTRY_DATE TEXT NOT NULL ); Here, ID is the AUDIT record ID, and EMP_ID is the ID, which will come from COMPANY table, and DATE will keep timestamp when the record will be created in COMPANY table. So now, let us create a trigger on COMPANY table as follows − testdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc(); Where auditlogfunc() is a PostgreSQL procedure and has the following definition − CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$ BEGIN INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp); RETURN NEW; END; $example_table$ LANGUAGE plpgsql; Now, we will start the actual work. Let us start inserting record in COMPANY table which should result in creating an audit log record in AUDIT table. So let us create one record in COMPANY table as follows − testdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Paul”, 32, ”California”, 20000.00 ); This will create one record in COMPANY table, which is as follows − id | name | age | address | salary —-+——+—–+————–+——– 1 | Paul | 32 | California | 20000 Same time, one record will be created in AUDIT table. This record is the result of a trigger, which we have created on INSERT operation on COMPANY table. Similarly, you can create your triggers on UPDATE and DELETE operations based on your requirements. emp_id | entry_date ——–+——————————- 1 | 2013-05-05 15:49:59.968+05:30 (1 row) Listing TRIGGERS You can list down all the triggers in the current database from pg_trigger table as follows − testdb=# SELECT * FROM pg_trigger; The above given PostgreSQL statement will list down all triggers. If you want to list the triggers on a particular table, then use AND clause with table name as follows − testdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname=”company”; The above given PostgreSQL statement will also list down only one entry as follows − tgname —————– example_trigger (1 row) Dropping TRIGGERS The following is the DROP command, which can be used to drop an existing trigger − testdb=# DROP TRIGGER trigger_name; Print Page Previous Next Advertisements ”;

PostgreSQL – GROUP BY ”; Previous Next The PostgreSQL GROUP BY clause is used in collaboration with the SELECT statement to group together those rows 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 follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. Syntax The basic syntax of GROUP BY clause is given below. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used. SELECT column-list FROM table_name WHERE [ conditions ] GROUP BY column1, column2….columnN ORDER BY column1, column2….columnN You can use more than one column in the GROUP BY clause. Make sure whatever column you are using to group, that column should be available in column-list. 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) If you want to know the total amount of salary of each customer, then GROUP BY query would be as follows − testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME; This would produce the following result − name | sum ——-+——- Teddy | 20000 Paul | 20000 Mark | 65000 David | 85000 Allen | 15000 Kim | 45000 James | 10000 (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) Again, let us use the same statement to group-by all the records using NAME column as follows − testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME; This would produce the following result − name | sum ——-+——- Allen | 15000 David | 85000 James | 20000 Kim | 45000 Mark | 65000 Paul | 40000 Teddy | 20000 (7 rows) Let us use ORDER BY clause along with GROUP BY clause as follows − testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC; This would produce the following result − name | sum ——-+——- Teddy | 20000 Paul | 40000 Mark | 65000 Kim | 45000 James | 20000 David | 85000 Allen | 15000 (7 rows) Print Page Previous Next Advertisements ”;

PostgreSQL – WITH Clause ”; Previous Next In PostgreSQL, the WITH query provides a way to write auxiliary statements for use in a larger query. It helps in breaking down complicated and large queries into simpler forms, which are easily readable. These statements often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. The WITH query being CTE query, is particularly useful when subquery is executed multiple times. It is equally helpful in place of temporary tables. It computes the aggregation once and allows us to reference it by its name (may be multiple times) in the queries. The WITH clause must be defined before it is used in the query. Syntax The basic syntax of WITH query is as follows − WITH name_for_summary_data AS ( SELECT Statement) SELECT columns FROM name_for_summary_data WHERE conditions <=> ( SELECT column FROM name_for_summary_data) [ORDER BY columns] Where name_for_summary_data is the name given to the WITH clause. The name_for_summary_data can be the same as an existing table name and will take precedence. You can use data-modifying statements (INSERT, UPDATE or DELETE) in WITH. This allows you to perform several different operations in the same query. Recursive WITH Recursive WITH or Hierarchical queries, is a form of CTE where a CTE can reference to itself, i.e., a WITH query can refer to its own output, hence the name recursive. 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) Now, let us write a query using the WITH clause to select the records from the above table, as follows − With CTE AS (Select ID , NAME , AGE , ADDRESS , SALARY FROM COMPANY ) Select * From CTE; 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 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows) Now, let us write a query using the RECURSIVE keyword along with the WITH clause, to find the sum of the salaries less than 20000, as follows − WITH RECURSIVE t(n) AS ( VALUES (0) UNION ALL SELECT SALARY FROM COMPANY WHERE SALARY < 20000 ) SELECT sum(n) FROM t; The above given PostgreSQL statement will produce the following result − sum ——- 25000 (1 row) Let us write a query using data modifying statements along with the WITH clause, as shown below. First, create a table COMPANY1 similar to the table COMPANY. The query in the example effectively moves rows from COMPANY to COMPANY1. The DELETE in WITH deletes the specified rows from COMPANY, returning their contents by means of its RETURNING clause; and then the primary query reads that output and inserts it into COMPANY1 TABLE − CREATE TABLE COMPANY1( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); WITH moved_rows AS ( DELETE FROM COMPANY WHERE SALARY >= 30000 RETURNING * ) INSERT INTO COMPANY1 (SELECT * FROM moved_rows); The above given PostgreSQL statement will produce the following result − INSERT 0 3 Now, the records in the tables COMPANY and COMPANY1 are 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 7 | James | 24 | Houston | 10000 (4 rows) testdb=# SELECT * FROM COMPANY1; id | name | age | address | salary —-+——-+—–+————-+——– 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 (3 rows) Print Page Previous Next Advertisements ”;

PostgreSQL – UNIONS Clause ”; Previous Next The PostgreSQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows. To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order but they do not have to be the same length. Syntax The basic syntax of UNION is as follows − SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] Here, given condition could be any given expression based on your requirement. 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 − testdb=# SELECT * from DEPARTMENT; 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 let us join these two tables using SELECT statement along with UNION clause as follows − testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID UNION SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; This would produce the following result − emp_id | name | dept ——–+——-+————– 5 | David | Engineering 6 | Kim | Finance 2 | Allen | Engineering 3 | Teddy | Engineering 4 | Mark | Finance 1 | Paul | IT Billing 7 | James | Finance (7 rows) The UNION ALL Clause The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows. The same rules that apply to UNION apply to the UNION ALL operator as well. Syntax The basic syntax of UNION ALL is as follows − SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION ALL SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] Here, given condition could be any given expression based on your requirement. Example Now, let us join above-mentioned two tables in our SELECT statement as follows − testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID UNION ALL SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; This would produce the following result − emp_id | name | dept ——–+——-+————– 1 | Paul | IT Billing 2 | Allen | Engineering 7 | James | Finance 3 | Teddy | Engineering 4 | Mark | Finance 5 | David | Engineering 6 | Kim | Finance 1 | Paul | IT Billing 2 | Allen | Engineering 7 | James | Finance 3 | Teddy | Engineering 4 | Mark | Finance 5 | David | Engineering 6 | Kim | Finance (14 rows) Print Page Previous Next Advertisements ”;