Category: postgresql

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

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