Category: postgresql

PostgreSQL – AUTO INCREMENT ”; Previous Next PostgreSQL has the data types smallserial, serial and bigserial; these are not true types, but merely a notational convenience for creating unique identifier columns. These are similar to AUTO_INCREMENT property supported by some other databases. If you wish a serial column to have a unique constraint or be a primary key, it must now be specified, just like any other data type. The type name serial creates an integer columns. The type name bigserial creates a bigint column. bigserial should be used if you anticipate the use of more than 231 identifiers over the lifetime of the table. The type name smallserial creates a smallint column. Syntax The basic usage of SERIAL dataype is as follows − CREATE TABLE tablename ( colname SERIAL ); Example Consider the COMPANY table to be created as follows − testdb=# CREATE TABLE COMPANY( ID SERIAL PRIMARY KEY, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); Now, insert the following records into table COMPANY − INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( ”Paul”, 32, ”California”, 20000.00 ); INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES (”Allen”, 25, ”Texas”, 15000.00 ); INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES (”Teddy”, 23, ”Norway”, 20000.00 ); INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( ”Mark”, 25, ”Rich-Mond ”, 65000.00 ); INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( ”David”, 27, ”Texas”, 85000.00 ); INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( ”Kim”, 22, ”South-Hall”, 45000.00 ); INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( ”James”, 24, ”Houston”, 10000.00 ); This will insert seven tuples into the table COMPANY and COMPANY will 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 | South-Hall | 45000 7 | James | 24 | Houston | 10000 Print Page Previous Next Advertisements ”;

PostgreSQL – DATE/TIME Functions and Operators ”; Previous Next We had discussed about the Date/Time data types in the chapter Data Types. Now, let us see the Date/Time operators and Functions. The following table lists the behaviors of the basic arithmetic operators − Operator Example Result + date ”2001-09-28” + integer ”7” date ”2001-10-05” + date ”2001-09-28” + interval ”1 hour” timestamp ”2001-09-28 01:00:00” + date ”2001-09-28” + time ”03:00” timestamp ”2001-09-28 03:00:00” + interval ”1 day” + interval ”1 hour” interval ”1 day 01:00:00” + timestamp ”2001-09-28 01:00” + interval ”23 hours” timestamp ”2001-09-29 00:00:00” + time ”01:00” + interval ”3 hours” time ”04:00:00” – – interval ”23 hours” interval ”-23:00:00” – date ”2001-10-01” – date ”2001-09-28” integer ”3” (days) – date ”2001-10-01” – integer ”7” date ”2001-09-24” – date ”2001-09-28” – interval ”1 hour” timestamp ”2001-09-27 23:00:00” – time ”05:00” – time ”03:00” interval ”02:00:00” – time ”05:00” – interval ”2 hours” time ”03:00:00” – timestamp ”2001-09-28 23:00” – interval ”23 hours” timestamp ”2001-09-28 00:00:00” – interval ”1 day” – interval ”1 hour” interval ”1 day -01:00:00” – timestamp ”2001-09-29 03:00” – timestamp ”2001-09-27 12:00” interval ”1 day 15:00:00” * 900 * interval ”1 second” interval ”00:15:00” * 21 * interval ”1 day” interval ”21 days” * double precision ”3.5” * interval ”1 hour” interval ”03:30:00” / interval ”1 hour” / double precision ”1.5” interval ”00:40:00” The following is the list of all important Date and Time related functions available. S. No. Function & Description 1 AGE() Subtract arguments 2 CURRENT DATE/TIME() Current date and time 3 DATE_PART() Get subfield (equivalent to extract) 4 EXTRACT() Get subfield 5 ISFINITE() Test for finite date, time and interval (not +/-infinity) 6 JUSTIFY Adjust interval AGE(timestamp, timestamp), AGE(timestamp) S. No. Function & Description 1 AGE(timestamp, timestamp) When invoked with the TIMESTAMP form of the second argument, AGE() subtract arguments, producing a “symbolic” result that uses years and months and is of type INTERVAL. 2 AGE(timestamp) When invoked with only the TIMESTAMP as argument, AGE() subtracts from the current_date (at midnight). Example of the function AGE(timestamp, timestamp) is − testdb=# SELECT AGE(timestamp ”2001-04-10”, timestamp ”1957-06-13”); The above given PostgreSQL statement will produce the following result − age ————————- 43 years 9 mons 27 days Example of the function AGE(timestamp) is − testdb=# select age(timestamp ”1957-06-13”); The above given PostgreSQL statement will produce the following result − age ————————– 55 years 10 mons 22 days CURRENT DATE/TIME() PostgreSQL provides a number of functions that return values related to the current date and time. Following are some functions − S. No. Function & Description 1 CURRENT_DATE Delivers current date. 2 CURRENT_TIME Delivers values with time zone. 3 CURRENT_TIMESTAMP Delivers values with time zone. 4 CURRENT_TIME(precision) Optionally takes a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. 5 CURRENT_TIMESTAMP(precision) Optionally takes a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. 6 LOCALTIME Delivers values without time zone. 7 LOCALTIMESTAMP Delivers values without time zone. 8 LOCALTIME(precision) Optionally takes a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. 9 LOCALTIMESTAMP(precision) Optionally takes a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. Examples using the functions from the table above − testdb=# SELECT CURRENT_TIME; timetz ——————– 08:01:34.656+05:30 (1 row) testdb=# SELECT CURRENT_DATE; date ———— 2013-05-05 (1 row) testdb=# SELECT CURRENT_TIMESTAMP; now ——————————- 2013-05-05 08:01:45.375+05:30 (1 row) testdb=# SELECT CURRENT_TIMESTAMP(2); timestamptz —————————— 2013-05-05 08:01:50.89+05:30 (1 row) testdb=# SELECT LOCALTIMESTAMP; timestamp ———————— 2013-05-05 08:01:55.75 (1 row) PostgreSQL also provides functions that return the start time of the current statement, as well as the actual current time at the instant the function is called. These functions are − S. No. Function & Description 1 transaction_timestamp() It is equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns. 2 statement_timestamp() It returns the start time of the current statement. 3 clock_timestamp() It returns the actual current time, and therefore its value changes even within a single SQL command. 4 timeofday() It returns the actual current time, but as a formatted text string rather than a timestamp with time zone value. 5 now() It is a traditional PostgreSQL equivalent to transaction_timestamp(). DATE_PART(text, timestamp), DATE_PART(text, interval), DATE_TRUNC(text, timestamp) S. No. Function & Description 1 DATE_PART(”field”, source) These functions get the subfields. The field parameter needs to be a string value, not a name. The valid field names are: century, day, decade, dow, doy, epoch, hour, isodow, isoyear, microseconds, millennium, milliseconds, minute, month, quarter, second, timezone, timezone_hour, timezone_minute, week, year. 2 DATE_TRUNC(”field”, source) This function is conceptually similar to the trunc function for numbers. source is a value expression of type timestamp or interval. field selects to which precision to truncate the input value. The return value is of type timestamp or interval. The valid values for field are : microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium The following are examples for DATE_PART(”field”, source) functions − testdb=# SELECT date_part(”day”, TIMESTAMP ”2001-02-16 20:38:40”); date_part ———– 16 (1 row) testdb=# SELECT date_part(”hour”, INTERVAL ”4 hours 3 minutes”); date_part ———– 4 (1 row) The following are examples for DATE_TRUNC(”field”, source) functions − testdb=# SELECT date_trunc(”hour”, TIMESTAMP ”2001-02-16 20:38:40”); date_trunc ——————— 2001-02-16 20:00:00 (1 row) testdb=# SELECT date_trunc(”year”, TIMESTAMP ”2001-02-16 20:38:40”); date_trunc ——————— 2001-01-01 00:00:00 (1 row) EXTRACT(field from timestamp), EXTRACT(field from interval) The EXTRACT(field FROM source) function retrieves subfields such as year or hour from date/time values. The source must be a value expression of type timestamp, time, or interval. The field is an identifier or string that selects what field to extract from the source value. The EXTRACT function returns values of type double precision. The following are valid field names (similar to DATE_PART function field names): century, day, decade, dow, doy, epoch, hour, isodow, isoyear, microseconds, millennium, milliseconds, minute, month, quarter, second, timezone,

PostgreSQL – PRIVILEGES ”; Previous Next Whenever an object is created in a database, an owner is assigned to it. The owner is usually the one who executed the creation statement. For most kinds of objects, the initial state is that only the owner (or a superuser) can modify or delete the object. To allow other roles or users to use it, privileges or permission must be granted. Different kinds of privileges in PostgreSQL are − SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE Depending on the type of the object (table, function, etc.,), privileges are applied to the object. To assign privileges to the users, the GRANT command is used. Syntax for GRANT Basic syntax for GRANT command is as follows − GRANT privilege [, …] ON object [, …] TO { PUBLIC | GROUP group | username } privilege − values could be: SELECT, INSERT, UPDATE, DELETE, RULE, ALL. object − The name of an object to which to grant access. The possible objects are: table, view, sequence PUBLIC − A short form representing all users. GROUP group − A group to whom to grant privileges. username − The name of a user to whom to grant privileges. PUBLIC is a short form representing all users. The privileges can be revoked using the REVOKE command. Syntax for REVOKE Basic syntax for REVOKE command is as follows − REVOKE privilege [, …] ON object [, …] FROM { PUBLIC | GROUP groupname | username } privilege − values could be: SELECT, INSERT, UPDATE, DELETE, RULE, ALL. object − The name of an object to which to grant access. The possible objects are: table, view, sequence PUBLIC − A short form representing all users. GROUP group − A group to whom to grant privileges. username − The name of a user to whom to grant privileges. PUBLIC is a short form representing all users. Example To understand the privileges, let us first create a USER as follows − testdb=# CREATE USER manisha WITH PASSWORD ”password”; CREATE ROLE The message CREATE ROLE indicates that the USER “manisha” is created. 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) Next, let us grant all privileges on a table COMPANY to the user “manisha” as follows − testdb=# GRANT ALL ON COMPANY TO manisha; GRANT The message GRANT indicates that all privileges are assigned to the USER. Next, let us revoke the privileges from the USER “manisha” as follows − testdb=# REVOKE ALL ON COMPANY FROM manisha; REVOKE The message REVOKE indicates that all privileges are revoked from the USER. You can even delete the user as follows − testdb=# DROP USER manisha; DROP ROLE The message DROP ROLE indicates USER ‘Manisha’ is deleted from the database. Print Page Previous Next Advertisements ”;

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 – 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 ”;