Overview of Open Source Postgre SQL Database

Introduction

It's an object-relational database that is often named "Postgres", which means "PostgreSQL". It's a powerful open-source database introduced in 1996. Head of Elephant is the official logo of this database. It's a cross platform database usually used on UNIX based OS and Windows.

Download

You can download it here for various platforms

Note: It will ask for a password during installation. The same password will be needed in the SQL Shell.

SQL Shell (psql)

After installation of Postgres, open the SQL Shell (psql) where you need to provide the password which you typed during the installation.



Psql is a command-line tool for Postgres as shown in the preceding screenshot.



Help Command

Foe learning some basic commands of psql such as:

• copyright
• help with SQL commands
• help with psql commands
• terminate with semicolon to execute query
• quit command

Just type help as you can see in the following image.



\copyright: for distribution (legal) terms between the user and the company.



\h: for help with SQL commands, type \h and press  enter to learn more of this type.

\?: for help with psql commands.

\g: this is for indication of termination with a semicolon to execute query. In other words , if you want to execute a query, then you need to add a semicolon (;) or \g at the end of query.

Note: As you can see in the preceding image, a query is executed with the use of a \g and semicolon (;) at the end. If this is not used, then the query won't execute.

\q: to quit the command-line tool.

Create Database

To create a database in Postgres, just type the following SQL command.

Create database databasename;




List of databases

Once a database gets created, you can check it in the list of databases using \l. In other words, you can use backslash l command.

Note: There are 4 databases as shown in the preceding screenshot. Under this, 3 are the system databases and 1 is the user database named "test" that was created earlier.

Select the Database

To select the database just type the following command.

\c databasename;

In the same way we need to change the default database, which is "postgres".

\c postgres

Create Table

To create a table you need to first select the database in which the table will be created. Here, the database name is "test".

Now, we will create a table in the "test" database with the name "mytable" as shown in the following screenshot. Table is created in the same way as it gets created in any Relational Database Management System (RDBMS) database such as Microsoft SQL Server.

See all Tables

If you want to see all the tables in the "test" database, use /d in the selected database. Here, our selected database is "test".

Schema of a Table

If you want to see the schema of a table such as "mytable", then use \d mytable.

CRUD Operations in Postgre

All CRUD operations are the same as in any RDBMS (Relational Database Management System) database such as Microsoft SQL Server.

Insert Data

Now, insert data in the table "mytable".

Select Data

Select data from the table "mytable".

Update Data

Update data from the table "mytable".

Delete Data

Delete data from the table "mytable".



Drop Table

Drop "mytable" table from the "test" database.



Drop Database

To drop the "test" database, we need to switch the database, otherwise it will show an error as shown in the following screenshot.
Error: cannot drop the currently open database.

Now, just switch to the database named "postgres".

Now, drop the database.