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 an indication of termination with a semicolon to execute the query. In other words, if you want to execute a query, then you need to add a semicolon (;) or \g at the end of the 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 the 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".