Category: postgresql

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

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

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

PostgreSQL – ORDER BY Clause ”; Previous Next The PostgreSQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Syntax The basic syntax of ORDER BY clause is as follows − SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort, that column should be available in column-list. 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 sort the result in ascending order by SALARY − testdb=# SELECT * FROM COMPANY ORDER BY AGE ASC; This would produce the following result − id | name | age | address | salary —-+——-+—–+————+——– 6 | Kim | 22 | South-Hall | 45000 3 | Teddy | 23 | Norway | 20000 7 | James | 24 | Houston | 10000 8 | Paul | 24 | Houston | 20000 4 | Mark | 25 | Rich-Mond | 65000 2 | Allen | 25 | Texas | 15000 5 | David | 27 | Texas | 85000 1 | Paul | 32 | California | 20000 9 | James | 44 | Norway | 5000 10 | James | 45 | Texas | 5000 (10 rows) The following is an example, which would sort the result in ascending order by NAME and SALARY − testdb=# SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC; This would produce the following result − id | name | age | address | salary —-+——-+—–+————–+——– 2 | Allen | 25 | Texas | 15000 5 | David | 27 | Texas | 85000 10 | James | 45 | Texas | 5000 9 | James | 44 | Norway | 5000 7 | James | 24 | Houston | 10000 6 | Kim | 22 | South-Hall | 45000 4 | Mark | 25 | Rich-Mond | 65000 1 | Paul | 32 | California | 20000 8 | Paul | 24 | Houston | 20000 3 | Teddy | 23 | Norway | 20000 (10 rows) The following is an example, which would sort the result in descending order by NAME − testdb=# SELECT * FROM COMPANY ORDER BY NAME DESC; This would produce the following result − id | name | age | address | salary —-+——-+—–+————+——– 3 | Teddy | 23 | Norway | 20000 1 | Paul | 32 | California | 20000 8 | Paul | 24 | Houston | 20000 4 | Mark | 25 | Rich-Mond | 65000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 9 | James | 44 | Norway | 5000 10 | James | 45 | Texas | 5000 5 | David | 27 | Texas | 85000 2 | Allen | 25 | Texas | 15000 (10 rows) Print Page Previous Next Advertisements ”;

PostgreSQL – LIMIT Clause ”; Previous Next The PostgreSQL LIMIT clause is used to limit the data amount returned by the SELECT statement. Syntax The basic syntax of SELECT statement with LIMIT clause is as follows − SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] The following is the syntax of LIMIT clause when it is used along with OFFSET clause − SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num] LIMIT and OFFSET allow you to retrieve just a portion of the rows that are generated by the rest of the query. 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 limits the row in the table according to the number of rows you want to fetch from table − testdb=# SELECT * FROM COMPANY LIMIT 4; 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 (4 rows) However, in certain situation, you may need to pick up a set of records from a particular offset. Here is an example, which picks up three records starting from the third position − testdb=# SELECT * FROM COMPANY LIMIT 3 OFFSET 2; This would produce the following result − id | name | age | address | salary —-+——-+—–+———–+——– 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (3 rows) Print Page Previous Next Advertisements ”;

PostgreSQL – Operators ”; Previous Next What is an Operator in PostgreSQL? An operator is a reserved word or a character used primarily in a PostgreSQL statement”s WHERE clause to perform operation(s), such as comparisons and arithmetic operations. Operators are used to specify conditions in a PostgreSQL statement and to serve as conjunctions for multiple conditions in a statement. Arithmetic operators Comparison operators Logical operators Bitwise operators PostgreSQL Arithmetic Operators Assume variable a holds 2 and variable b holds 3, then − Example Operator Description Example + Addition – Adds values on either side of the operator a + b will give 5 – Subtraction – Subtracts right hand operand from left hand operand a – b will give -1 * Multiplication – Multiplies values on either side of the operator a * b will give 6 / Division – Divides left hand operand by right hand operand b / a will give 1 % Modulus – Divides left hand operand by right hand operand and returns remainder b % a will give 1 ^ Exponentiation – This gives the exponent value of the right hand operand a ^ b will give 8 |/ square root |/ 25.0 will give 5 ||/ Cube root ||/ 27.0 will give 3 ! factorial 5 ! will give 120 !! factorial (prefix operator) !! 5 will give 120 PostgreSQL Comparison Operators Assume variable a holds 10 and variable b holds 20, then − Show Examples Operator Description Example = Checks if the values of two operands are equal or not, if yes then condition becomes true. (a = b) is not true. != Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. (a != b) is true. <> Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. (a <> b) is true. > Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true. (a > b) is not true. < Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true. (a < b) is true. >= Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true. (a >= b) is not true. <= Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true. (a <= b) is true. PostgreSQL Logical Operators Here is a list of all the logical operators available in PostgresSQL. Show Examples S. No. Operator & Description 1 AND The AND operator allows the existence of multiple conditions in a PostgresSQL statement”s WHERE clause. 2 NOT The NOT operator reverses the meaning of the logical operator with which it is used. Eg. NOT EXISTS, NOT BETWEEN, NOT IN etc. This is negate operator. 3 OR The OR operator is used to combine multiple conditions in a PostgresSQL statement”s WHERE clause. PostgreSQL Bit String Operators Bitwise operator works on bits and performs bit-by-bit operation. The truth table for & and | is as follows − p q p & q p | q 0 0 0 0 0 1 0 1 1 1 1 1 1 0 0 1 Assume if A = 60; and B = 13; now in binary format they will be as follows − A = 0011 1100 B = 0000 1101 —————– A&B = 0000 1100 A|B = 0011 1101 ~A  = 1100 0011 Show Examples The Bitwise operators supported by PostgreSQL are listed in the following table − Operator Description Example & Binary AND Operator copies a bit to the result if it exists in both operands. (A & B) will give 12 which is 0000 1100 | Binary OR Operator copies a bit if it exists in either operand. (A | B) will give 61 which is 0011 1101 ~ Binary Ones Complement Operator is unary and has the effect of ”flipping” bits. (~A ) will give -61 which is 1100 0011 in 2”s complement form due to a signed binary number. << Binary Left Shift Operator. The left operands value is moved left by the number of bits specified by the right operand. A << 2 will give 240 which is 1111 0000 >> Binary Right Shift Operator. The left operands value is moved right by the number of bits specified by the right operand. A >> 2 will give 15 which is 0000 1111 # bitwise XOR. A # B will give 49 which is 00110001 Print Page Previous Next Advertisements ”;

PostgreSQL – CREATE Table ”; Previous Next The PostgreSQL CREATE TABLE statement is used to create a new table in any of the given database. Syntax Basic syntax of CREATE TABLE statement is as follows − CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, PRIMARY KEY( one or more columns ) ); CREATE TABLE is a keyword, telling the database system to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Initially, the empty table in the current database is owned by the user issuing the command. Then, in brackets, comes the list, defining each column in the table and what sort of data type it is. The syntax will become clear with an example given below. Examples The following is an example, which creates a COMPANY table with ID as primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table − CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); Let us create one more table, which we will use in our exercises in subsequent chapters − CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL ); You can verify if your table has been created successfully using d command, which will be used to list down all the tables in an attached database. testdb-# d The above given PostgreSQL statement will produce the following result − List of relations Schema | Name | Type | Owner ——–+————+——-+———- public | company | table | postgres public | department | table | postgres (2 rows) Use d tablename to describe each table as shown below − testdb-# d company The above given PostgreSQL statement will produce the following result − Table “public.company” Column | Type | Modifiers ———–+—————+———– id | integer | not null name | text | not null age | integer | not null address | character(50) | salary | real | join_date | date | Indexes: “company_pkey” PRIMARY KEY, btree (id) Print Page Previous Next Advertisements ”;

PostgreSQL – DROP Database ”; Previous Next In this chapter, we will discuss how to delete the database in PostgreSQL. There are two options to delete a database − Using DROP DATABASE, an SQL command. Using dropdb a command-line executable. Be careful before using this operation because deleting an existing database would result in loss of complete information stored in the database. Using DROP DATABASE This command drops a database. It removes the catalog entries for the database and deletes the directory containing the data. It can only be executed by the database owner. This command cannot be executed while you or anyone else is connected to the target database (connect to postgres or any other database to issue this command). Syntax The syntax for DROP DATABASE is given below − DROP DATABASE [ IF EXISTS ] name Parameters The table lists the parameters with their descriptions. S. No. Parameter & Description 1 IF EXISTS Do not throw an error if the database does not exist. A notice is issued in this case. 2 name The name of the database to remove. We cannot drop a database that has any open connections, including our own connection from psql or pgAdmin III. We must switch to another database or template1 if we want to delete the database we are currently connected to. Thus, it might be more convenient to use the program dropdb instead, which is a wrapper around this command. Example The following is a simple example, which will delete testdb from your PostgreSQL schema − postgres=# DROP DATABASE testdb; postgres-# Using dropdb Command PostgresSQL command line executable dropdb is a command-line wrapper around the SQL command DROP DATABASE. There is no effective difference between dropping databases via this utility and via other methods for accessing the server. dropdb destroys an existing PostgreSQL database. The user, who executes this command must be a database super user or the owner of the database. Syntax The syntax for dropdb is as shown below − dropdb [option…] dbname Parameters The following table lists the parameters with their descriptions S. No. Parameter & Description 1 dbname The name of a database to be deleted. 2 option command-line arguments, which dropdb accepts. Options The following table lists the command-line arguments dropdb accepts − S. No. Option & Description 1 -e Shows the commands being sent to the server. 2 -i Issues a verification prompt before doing anything destructive. 3 -V Print the dropdb version and exit. 4 –if-exists Do not throw an error if the database does not exist. A notice is issued in this case. 5 –help Show help about dropdb command-line arguments, and exit. 6 -h host Specifies the host name of the machine on which the server is running. 7 -p port Specifies the TCP port or the local UNIX domain socket file extension on which the server is listening for connections. 8 -U username User name to connect as. 9 -w Never issue a password prompt. 10 -W Force dropdb to prompt for a password before connecting to a database. 11 –maintenance-db=dbname Specifies the name of the database to connect to in order to drop the target database. Example The following example demonstrates deleting a database from OS command prompt − dropdb -h localhost -p 5432 -U postgress testdb Password for user postgress: **** The above command drops the database testdb. Here, I have used the postgres (found under the pg_roles of template1) username to drop the database. Print Page Previous Next Advertisements ”;

PostgreSQL – INSERT Query ”; Previous Next The PostgreSQL INSERT INTO statement allows one to insert new rows into a table. One can insert a single row at a time or several rows as a result of a query. Syntax Basic syntax of INSERT INTO statement is as follows − INSERT INTO TABLE_NAME (column1, column2, column3,…columnN) VALUES (value1, value2, value3,…valueN); Here, column1, column2,…columnN are the names of the columns in the table into which you want to insert data. The target column names can be listed in any order. The values supplied by the VALUES clause or query are associated with the explicit or implicit column list left-to-right. You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. However, make sure the order of the values is in the same order as the columns in the table. The SQL INSERT INTO syntax would be as follows − INSERT INTO TABLE_NAME VALUES (value1,value2,value3,…valueN); Output The following table summarizes the output messages and their meaning − S. No. Output Message & Description 1 INSERT oid 1 Message returned if only one row was inserted. oid is the numeric OID of the inserted row. 2 INSERT 0 # Message returned if more than one rows were inserted. # is the number of rows inserted. Examples Let us create COMPANY table in testdb as follows − CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL, JOIN_DATE DATE ); The following example inserts a row into the COMPANY table − INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, ”Paul”, 32, ”California”, 20000.00,”2001-07-13”); The following example is to insert a row; here salary column is omitted and therefore it will have the default value − INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE) VALUES (2, ”Allen”, 25, ”Texas”, ”2007-12-13”); The following example uses the DEFAULT clause for the JOIN_DATE column rather than specifying a value − INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, ”Teddy”, 23, ”Norway”, 20000.00, DEFAULT ); The following example inserts multiple rows using the multirow VALUES syntax − INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, ”Mark”, 25, ”Rich-Mond ”, 65000.00, ”2007-12-13” ), (5, ”David”, 27, ”Texas”, 85000.00, ”2007-12-13”); All the above statements would create the following records in COMPANY table. The next chapter will teach you how to display all these records from a table. ID NAME AGE ADDRESS SALARY JOIN_DATE —- ———- —– ———- ——- ——– 1 Paul 32 California 20000.0 2001-07-13 2 Allen 25 Texas 2007-12-13 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 2007-12-13 5 David 27 Texas 85000.0 2007-12-13 Print Page Previous Next Advertisements ”;

PostgreSQL – Schema ”; Previous Next A schema is a named collection of tables. A schema can also contain views, indexes, sequences, data types, operators, and functions. Schemas are analogous to directories at the operating system level, except that schemas cannot be nested. PostgreSQL statement CREATE SCHEMA creates a schema. Syntax The basic syntax of CREATE SCHEMA is as follows − CREATE SCHEMA name; Where name is the name of the schema. Syntax to Create Table in Schema The basic syntax to create table in schema is as follows − CREATE TABLE myschema.mytable ( … ); Example Let us see an example for creating a schema. Connect to the database testdb and create a schema myschema as follows − testdb=# create schema myschema; CREATE SCHEMA The message “CREATE SCHEMA” signifies that the schema is created successfully. Now, let us create a table in the above schema as follows − testdb=# create table myschema.company( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); This will create an empty table. You can verify the table created with the command given below − testdb=# select * from myschema.company; This would produce the following result − id | name | age | address | salary —-+——+—–+———+——– (0 rows) Syntax to Drop Schema To drop a schema if it is empty (all objects in it have been dropped), use the command − DROP SCHEMA myschema; To drop a schema including all contained objects, use the command − DROP SCHEMA myschema CASCADE; Advantages of using a Schema It allows many users to use one database without interfering with each other. It organizes database objects into logical groups to make them more manageable. Third-party applications can be put into separate schemas so they do not collide with the names of other objects. Print Page Previous Next Advertisements ”;