Sign in
Log inSign up

PostgreSQL

shaifali agarwal's photo
shaifali agarwal
·Jun 26, 2022·

3 min read

PostgreSQL is a powerful, open-source object-relational database system. PostgreSQL language, such as create a database, drop a database, select database, select table, update a record, create a table, delete record, drop table, triggers, functions, insert the record, cursors.

Set up PostgreSQL

Download PostgreSQL from here. After installation is done you need to search for Pgadmin.

1.png

You need to set a master password for security purposes.

2.png

Now you are able to see the dashboard for pgAdmin.

3.png

Create a Server

4.png

Add the Server name which you want to give.

5.png

Add hostname and password.

6.png

Now you can see the server jsdatabase created successfully

7.png

Create Database

8.png

Add database name.

9.png

shaily database created successfully.

10.png

In Schemas you can create a database table.

11.png

Add columns using datatype.

12.png

Open query editor, and insert data into a table using the following query.

13.png

Show table data. Datatypes are supported by PostgreSQL.

  • Boolean
  • Character Types [ such as char, varchar, and text]
  • Numeric Types [ such as integer and floating-point numbers]
  • Temporal Types [ such as date, time, timestamp, and interval]
  • UUID [ for storing UUID (Universally Unique Identifiers) ]
  • Array [ for storing array strings, numbers, etc.]
  • JSON [ stores JSON data]
  • hstore [ stores key-value pair]
  • Special Types [ such as network address and geometric data]

14.png

Created one more table with different data.

15.png

INNER JOIN

16.png

CROSS JOIN

17.png

RIGHT OUTER JOIN

18.png

LEFT OUTER JOIN

19.png

WHERE Condition.

20.png

UPDATE TABLE

21.png

A Cursor in PostgreSQL is used to process large tables. Suppose a table has 10 million or billion rows. While performing a SELECT operation on the table it will take some time to process the result and most likely give an “out of memory” error and the program will be terminated.

A Cursor can only be declared inside a transaction. The cursor does not calculate the data but only prepares the query so that your data can be created when FETCH is called. In the end, simply commit the transaction.

22.png 23.png 24.png

A PostgreSQL Trigger is a function invoked automatically whenever an event associated with a table occurs. An event could be any of the following: INSERT, UPDATE, DELETE or TRUNCATE.

A trigger is a special user-defined function associated with a table. To create a new trigger, you must define a trigger function first, and then bind this trigger function to a table. The difference between a trigger and a user-defined function is that a trigger is automatically invoked when an event occurs.

PostgreSQL provides two main types of triggers:

*- Row level-triggers

  • Statement-level triggers*

25.png 26.png 27.png 28.png 29.png