PostgreSQL Advance

PostgreSQL Advance

PostgreSQL Advance consists of some of the additional features or concepts that PostgreSQL provides over the other databases that are present right now in the market. Some of these additional features and functionalities of PostgreSQL are:

  • PostgreSQL Inheritance
  • Psql commands
  • PostgreSQL Except
  • PostgreSQL Alias
  • PostgreSQL Date & Time
  • PostgreSQL Functions
  • PostgreSQL Union
  • PostgreSQL Intersect

Let us see some of them in detail to have a better understanding of them.

Inheritance in PostgreSQL Database:

PostgreSQL supports table Inheritance. Table inheritance means we can extend columns from the parent table into the child table instead of writing those columns again for the child. With the help of the table inheritance in PostgreSQL, we can achieve reusability and readability. And it also reduces the redundancy of the code and supports code reusability of the code.

Now let us understand the concept of table inheritance with the help of an example.

Here we have created a table named comapny_details having two columns comp_name and comp_address to store the name of the company and the address of the company, respectively by using the syntax:

 CREATE TABLE company_details(
 comp_name text,
 comp_address text
 ); 

Now let us create another table that will inherit this company_details table. The syntax for inheriting a table in PostgreSQL is:

 CREATE TABLE name_of_table(
 columnOnedata_type,
 columnTwodata_type,
 columnThreedata_type,
 …
 columnNdata_type
 ) INHERITS (name_of_base_table); 

So, we will create a table named employee_details that will inherit the company_details table, and this table will have an additional column named emp_name to store the name of the employee in the table.

The syntax for creating employee_details table will be:

 CREATE TABLE employee_details(
 emp_name text
 ) INHERITS (company_details); 

As shown in the image, employee_details table is created successfully with three columns, two from the base table company_details and one newly created.

Now let us add data in both of these tables and observe the changes.

As shown in the image, we have successfully added three rows of data in the company_details table using the command:

 INSERT INTO company_details
 VALUES
 ('Oracle','Texas'),
 ('Alphabet','California') ,
 ('NVIDIA','Santa Clara'); 

Similarly, we will also add data into the employee_details table using the command:

 INSERT INTO employee_details
  VALUES
 ('Pitney Bowes','Connecticut', 'Harry') ,
 ('Qualys','Foster City', 'Andrew'); 

As shown, we have successfully added two rows of data into all the three columns of the employee_details table, out of which two columns are inherited from the company_details table.

Because the two columns of the employee_details table are inherited from the company_details table, the data that we have added in the employee_details table will also be reflected in the company details table.

As shown in the image now, the company_details table has five rows of data, out of which three we have inserted using the insert command, and the rest of the two rows are added automatically while adding data to the employee_details table.

To display only the explicitly inserted values in the company_details table, we have to use the ONLY keyword in the select command with the following syntax:

select * from ONLY company_details;

As shown in the image, only three rows of data are displayed from the company_details table after using the ONLY keyword that we have added explicitly using the INSERT command.

Psql Commands:

Psql is the command-line interface for using PostgreSQL by CLI method. There are various commands for the psql. We will discuss some of the major commands.

‘\l’ command:

This command is used to list all the databases that are present.

In the above image, we can see the output of the ‘\l’ command in the psql shell.

‘\c’ command:

This command is used to select a particular database that is present in the system. To select a database, pass the name of that database as a parameter to the ‘\c’ command in the psql.

As shown in the image, we have passed ‘mypersonaldb’ as a parameter to the ‘\c’ command, and as a result, the mypersonaldb is selected.

‘\d’ Command:

This command is used to display all the relations present in that particular database that has been currently selected for use.

As shown in the image, the ‘\d’ command listed all the tables or relations present in the Postgres database.

If we pass a table name as a parameter to the ‘\d’ command, it will display the schema of that table whose name is passed as a parameter.

As shown in the image, the schema of the students table is displayed as the result of the ‘\d’ command.

So, with the help of this article, we can get a better insight into some of the advanced features of the PostgreSQL database.