Category: postgresql

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

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

PostgreSQL – CONSTRAINTS ”; Previous Next Constraints are the rules enforced on data columns on table. These are used to prevent invalid data from being entered into the database. This ensures the accuracy and reliability of the data in the database. Constraints could be column level or table level. Column level constraints are applied only to one column whereas table level constraints are applied to the whole table. Defining a data type for a column is a constraint in itself. For example, a column of type DATE constrains the column to valid dates. The following are commonly used constraints available in PostgreSQL. NOT NULL Constraint − Ensures that a column cannot have NULL value. UNIQUE Constraint − Ensures that all values in a column are different. PRIMARY Key − Uniquely identifies each row/record in a database table. FOREIGN Key − Constrains data based on columns in other tables. CHECK Constraint − The CHECK constraint ensures that all values in a column satisfy certain conditions. EXCLUSION Constraint − The EXCLUDE constraint ensures that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s), not all of these comparisons will return TRUE. NOT NULL Constraint By default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that column. A NOT NULL constraint is always written as a column constraint. A NULL is not the same as no data; rather, it represents unknown data. Example For example, the following PostgreSQL statement creates a new table called COMPANY1 and adds five columns, three of which, ID and NAME and AGE, specify not to accept NULL values − CREATE TABLE COMPANY1( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); UNIQUE Constraint The UNIQUE Constraint prevents two records from having identical values in a particular column. In the COMPANY table, for example, you might want to prevent two or more people from having identical age. Example For example, the following PostgreSQL statement creates a new table called COMPANY3 and adds five columns. Here, AGE column is set to UNIQUE, so that you cannot have two records with same age − CREATE TABLE COMPANY3( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 ); PRIMARY KEY Constraint The PRIMARY KEY constraint uniquely identifies each record in a database table. There can be more UNIQUE columns, but only one primary key in a table. Primary keys are important when designing the database tables. Primary keys are unique ids. We use them to refer to table rows. Primary keys become foreign keys in other tables, when creating relations among tables. Due to a ”longstanding coding oversight”, primary keys can be NULL in SQLite. This is not the case with other databases A primary key is a field in a table, which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key. If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s). Example You already have seen various examples above where we have created COMAPNY4 table with ID as primary key − CREATE TABLE COMPANY4( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); FOREIGN KEY Constraint A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables. They are called foreign keys because the constraints are foreign; that is, outside the table. Foreign keys are sometimes called a referencing key. Example For example, the following PostgreSQL statement creates a new table called COMPANY5 and adds five columns. CREATE TABLE COMPANY6( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); For example, the following PostgreSQL statement creates a new table called DEPARTMENT1, which adds three columns. The column EMP_ID is the foreign key and references the ID field of the table COMPANY6. CREATE TABLE DEPARTMENT1( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT references COMPANY6(ID) ); CHECK Constraint The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and is not entered into the table. Example For example, the following PostgreSQL statement creates a new table called COMPANY5 and adds five columns. Here, we add a CHECK with SALARY column, so that you cannot have any SALARY as Zero. CREATE TABLE COMPANY5( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL CHECK(SALARY > 0) ); EXCLUSION Constraint Exclusion constraints ensure that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null. Example For example, the following PostgreSQL statement creates a new table called COMPANY7 and adds five columns. Here, we add an EXCLUDE constraint − CREATE TABLE COMPANY7( ID INT PRIMARY KEY NOT NULL, NAME TEXT, AGE INT , ADDRESS CHAR(50), SALARY REAL, EXCLUDE USING gist (NAME WITH =, AGE WITH ) ); Here, USING gist is the type of index to build and use for enforcement. You need to execute the command CREATE EXTENSION btree_gist, once per database. This will install the btree_gist extension, which defines the exclusion

PostgreSQL – SELECT Query ”; Previous Next PostgreSQL SELECT statement is used to fetch the data from a database table, which returns data in the form of result table. These result tables are called result-sets. Syntax The basic syntax of SELECT statement is as follows − SELECT column1, column2, columnN FROM table_name; Here, column1, column2…are the fields of a table, whose values you want to fetch. If you want to fetch all the fields available in the field then you can use the following syntax − SELECT * FROM table_name; Example Consider the table COMPANY having records as follows − 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 fetch ID, Name and Salary fields of the customers available in CUSTOMERS table − testdb=# SELECT ID, NAME, SALARY FROM COMPANY ; This would produce the following result − id | name | salary —-+——-+——– 1 | Paul | 20000 2 | Allen | 15000 3 | Teddy | 20000 4 | Mark | 65000 5 | David | 85000 6 | Kim | 45000 7 | James | 10000 (7 rows) If you want to fetch all the fields of CUSTOMERS table, then use the following query − testdb=# SELECT * FROM COMPANY; This would 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) Print Page Previous Next Advertisements ”;

PostgreSQL – LIKE Clause ”; Previous Next The PostgreSQL LIKE operator is used to match text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1. There are two wildcards used in conjunction with the LIKE operator − The percent sign (%) The underscore (_) The percent sign represents zero, one, or multiple numbers or characters. The underscore represents a single number or character. These symbols can be used in combinations. If either of these two signs is not used in conjunction with the LIKE clause, then the LIKE acts like the equals operator. Syntax The basic syntax of % and _ is as follows − SELECT FROM table_name WHERE column LIKE ”XXXX%” or SELECT FROM table_name WHERE column LIKE ”%XXXX%” or SELECT FROM table_name WHERE column LIKE ”XXXX_” or SELECT FROM table_name WHERE column LIKE ”_XXXX” or SELECT FROM table_name WHERE column LIKE ”_XXXX_” You can combine N number of conditions using AND or OR operators. Here XXXX could be any numeric or string value. Example Here are number of examples showing WHERE part having different LIKE clause with ”%” and ”_” operators − S. No. Statement & Description 1 WHERE SALARY::text LIKE ”200%” Finds any values that start with 200 2 WHERE SALARY::text LIKE ”%200%” Finds any values that have 200 in any position 3 WHERE SALARY::text LIKE ”_00%” Finds any values that have 00 in the second and third positions 4 WHERE SALARY::text LIKE ”2_%_%” Finds any values that start with 2 and are at least 3 characters in length 5 WHERE SALARY::text LIKE ”%2” Finds any values that end with 2 6 WHERE SALARY::text LIKE ”_2%3” Finds any values that have 2 in the second position and end with a 3 7 WHERE SALARY::text LIKE ”2___3” Finds any values in a five-digit number that start with 2 and end with 3 Postgres LIKE is String compare only. Hence, we need to explicitly cast the integer column to string as in the examples above. Let us take a real 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 all the records from COMPANY table where AGE starts with 2 − testdb=# SELECT * FROM COMPANY WHERE AGE::text LIKE ”2%”; This would produce the following result − id | name | age | address | salary —-+——-+—–+————-+——– 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 (7 rows) The following is an example, which would display all the records from COMPANY table where ADDRESS will have a hyphen (-) inside the text − testdb=# SELECT * FROM COMPANY WHERE ADDRESS LIKE ”%-%”; This would produce the following result − id | name | age | address | salary —-+——+—–+——————————————-+——– 4 | Mark | 25 | Rich-Mond | 65000 6 | Kim | 22 | South-Hall | 45000 (2 rows) Print Page Previous Next Advertisements ”;