Categories
PostgreSQL Interview Questions and Answers
1. What is PostgreSQL?
PostgreSQL is an enterprise-class open source database management system. It supports both SQL and JSON for relational and non-relational queries for extensibility and SQL compliance. PostgreSQL supports advanced data types and performance optimization features, which are only available in expensive commercial databases, like Oracle and SQL Server. It is also known as Postgres.
2. Data Types in PostgreSQL
PostgreSQL supports the following data types:
1) Text Types
2) Numeric Types
3) Dates and Times
4) XML
5) JSON
6) Boolean
7) Bits
8) Binary Data
9) Network
10) Arrays
11) Create your Data Type
12) Boolean
13) Temporal
14) UUID
15) Array
16) JSON
17) Special Data types for storing a network address and geometric data.
1) Text Types
2) Numeric Types
3) Dates and Times
4) XML
5) JSON
6) Boolean
7) Bits
8) Binary Data
9) Network
10) Arrays
11) Create your Data Type
12) Boolean
13) Temporal
14) UUID
15) Array
16) JSON
17) Special Data types for storing a network address and geometric data.
3. Drop Database in PostgreSQL
The DROP DATABASE in PostgreSQL is a statement to permanently remove all catalog entries and data directory. The database owner can only execute this command. It can not be executed while someone is connected with the target database. You need to connect to some other database to execute the DROP DATABASE command.
PostgreSQL DROP DATABASE Syntax:
DROP DATABASE [IF EXISTS) name;
PostgreSQL DROP DATABASE Syntax:
DROP DATABASE [IF EXISTS) name;
4. PostgreSQL INSERT statement
In PostgreSQL, the insert statement helps insert a new row or row into the table. You can insert rows specified by value expressions, zero, or multiple rows resulting from the query.
Syntax of PostgreSQL INSERT INTO
INSERT INTO TABLE_NAME (column1, column2, ...columnN) VALUES (value1, value2,...valueN);
Syntax of PostgreSQL INSERT INTO
INSERT INTO TABLE_NAME (column1, column2, ...columnN) VALUES (value1, value2,...valueN);
5. What is PostgreSQL Array?
In PostgreSQL, we can define a column as an array of valid data types. The data type can be built-in, user-defined, or enumerated type. Other than this, arrays play an important role in PostgreSQL.
Every corresponding PostgreSQL data type comes with a relevant array type. For example, the integer data type has the integer[] array type, the character data type has the character[] array type, etc.
Every corresponding PostgreSQL data type comes with a relevant array type. For example, the integer data type has the integer[] array type, the character data type has the character[] array type, etc.
6. What is BETWEEN Operator in PostgreSQL
The PostgreSQL BETWEEN Operator helps in retrieving values within a range in SELECT, UPDATE, INSERT, or DELETE statement. With the BETWEEN operator, it is possible to match a value against a range of values.
PostgreSQL Between Query Syntax
expression BETWEEN value-1 AND value-2;
PostgreSQL Between Query Syntax
expression BETWEEN value-1 AND value-2;
7. What is PostgreSQL In ?
The IN operator is used in a WHERE clause that allows checking whether a value is present in a list of other values. In Operation helps to reduce the need for multiple OR conditions in SELECT, UPDATE, INSERT, or DELETE statements.
8. What is PostgreSQL LIKE ?
The PostgreSQL LIKE operator helps us to match text values against patterns using wildcards. It is possible to match the search expression to the pattern expression.
If a match occurs, the LIKE operator returns true. With the help of LIKE operator, it is possible to use wildcards in the WHERE clause of SELECT, UPDATE, INSERT or DELETE statements.
If a match occurs, the LIKE operator returns true. With the help of LIKE operator, it is possible to use wildcards in the WHERE clause of SELECT, UPDATE, INSERT or DELETE statements.
9. What is Wild cards ?
There are only two wildcards that can be used together with
1) Percent sign (%)
2) Underscore (_)
The underscore wildcard (_) is used to represent one character or number. These symbols can also be combined. If the LIKE operator is not used together with these two signs, it will act like the equals operator.
1) Percent sign (%)
2) Underscore (_)
The underscore wildcard (_) is used to represent one character or number. These symbols can also be combined. If the LIKE operator is not used together with these two signs, it will act like the equals operator.
10. What is Exists in PostgreSQL?
The PostgreSQL EXISTS operator tests whether a row(s) exists in a subquery. This means that the operator is used together with a subquery. The Exists operator is said to have been met when at least one row is found in the subquery. You can use this operation along with SELECT, UPDATE, INSERT, and DELETE statements.
Postgres Exists Query Syntax
WHERE EXISTS (subquery);
Postgres Exists Query Syntax
WHERE EXISTS (subquery);
11. Delete Query in PostgreSQL
The Delete Statement in PostgreSQL is used to delete either one or more records from a table. If you want to delete select rows from a table PostgreSQL allows you to combine the DELETE statement with the WHERE clause else it will delete all records.
12. What is PostgreSQL Substring?
The PostgreSQL substring function helps you to extract and return part of a string. Instead of returning the whole string, it only returns a part of it.
The PostgreSQL substring function takes the following syntax:
substring( string [from starting_position] [for length] )
The PostgreSQL substring function takes the following syntax:
substring( string [from starting_position] [for length] )
13. What is PostgreSQL Substring?
The PostgreSQL substring function helps you to extract and return part of a string. Instead of returning the whole string, it only returns a part of it.
14. What is PostgreSQL Union?
The PostgreSQL UNION operator is used for combining result sets from more than one SELECT statement into one result set. Any duplicate rows from the results of the SELECT statements are eliminated. The UNION operator works under two conditions:
1) The SELECT queries MUST return a similar number of queries.
2) The data types of all corresponding columns must be compatible.
1) The SELECT queries MUST return a similar number of queries.
2) The data types of all corresponding columns must be compatible.
15. What are Joins in PostgreSQL?
PostgreSQL Joins are used for retrieving data from more than one tables. With Joins, it is possible for us to combine the SELECT and JOIN statements into a single statement. A JOIN condition is added to the statement, and all rows that meet the conditions are returned. The values from different tables are combined based on common columns. The common column mostly is a primary key in the first table and a foreign key of the second table.
16. What is PostgreSQL View?
In PostgreSQL, a view is a pseudo-table. This means that a view is not a real table. However, we can SELECT it as an ordinary table. A view can have all or some of the table columns. A view can also be a representation of more than one table.
The tables are referred to as base tables. When creating a view, you just need to create a query then give it a name, making it a useful tool for wrapping complex and commonly used queries.
The tables are referred to as base tables. When creating a view, you just need to create a query then give it a name, making it a useful tool for wrapping complex and commonly used queries.
17. What is Trigger in PostgreSQL?
A PostgreSQL Trigger is a function that is triggered automatically when a database event occurs on a database object. For example, a table. Examples of database events that can activate a trigger include INSERT, UPDATE, DELETE, etc. Moreover, when you create a trigger for a table, the trigger will be dropped automatically when that table is deleted.
18. What is Constraint?
Data types are used to limit the kind of information that can be stored in a table. But there are 2 issues with this approach.
Issue 1: Restraints enforced by data types are not adequate for certain applications. For example, a column containing an Item price should only accept positive values. However, there is no specific data type which only accepts the positive number.
Issue 2: You may want to limit the information in a row/column data in relation to other columns or rows. For example, a table containing Item information should have only one row for each unique column constraint.
Issue 1: Restraints enforced by data types are not adequate for certain applications. For example, a column containing an Item price should only accept positive values. However, there is no specific data type which only accepts the positive number.
Issue 2: You may want to limit the information in a row/column data in relation to other columns or rows. For example, a table containing Item information should have only one row for each unique column constraint.
19. What is PostgreSQL?
Postgre is an object-relational database management system (ORDBMS). It was developed at the Computer Science Department in the University of California. Postgres pioneered many concepts.
Postgre is an Enterprise-class relational database system. It is easy to setup and installs. It offers support for SQL and NoSQL. It has a great community which happy to serve you when you are facing issues while using PostgreSQL.
Postgre is an Enterprise-class relational database system. It is easy to setup and installs. It offers support for SQL and NoSQL. It has a great community which happy to serve you when you are facing issues while using PostgreSQL.
