Installation of PostgreSQL
As PostgreSQL is an open source, we can download it from official website (www.postgresql.org) by following below steps:
- Go to official website >> Click on Download >> Choose your System OS >> Click “Download the installer”
- Select latest version (15.2 present) of PostgreSQL, based on you system requirements, click on Download
- It start downloading postgres executable file (.exe file), you can check the progress from downloads of your browser.
Installation into System:
After completion , click on the executable file setup PostgreSQL
- Opens up a setup window >> Click Next
- Installation directory (default) >> Click Next
- Select all components >> Click Next
- Data Directory (default) >> Click Next
- Create a password (remember for future reference) >> Click Next
- Port 5432 (default) >> Click Next
- Select locale to be used by new database cluster >> Click Next
- Pre installation Summary >> Click Next
- Stars installation progress …
- Later, uncheck stack builder prompt >> Click Next
There are mainly two common interfaces used with PostgreSQL database, which are:
Command-Line Interface (CLI) – PostgreSQL provides a command- line interface tool called “psq1”, which allows you to interact with the database directly from the terminal. With psql, you can create, delete, and modify databases and tables, execute SQL queries, and perform administrative tasks.
Connecting to database through psql:
To access psql, you need to open SQL Shell (psql) which is installed in your computer and do the following process:
1 2 3 4 5 |
Server [localhost]: >> press enter -Server is selected as localhost(default) Database [postgres]: >> press enter -Database selected as postgres(default) Port [5432]: >> press enter -5432 port is selected by default Username [postgres]: >> press enter -By default Username is postgres Password for user postgres: >> enter password -Created while installation |
Now You will be welcomed in psql with the following message:
1 2 3 |
Psql(15.2) Type “help” for help. postgres=# |
The last printed by psql “postgres=# “ is the prompt where you can type psql commands. Try out few commands,
1 2 3 |
postgres=# select version(); //prints version of psql postgres=# select current_date; //prints today’s date postgres=# select 10+2; // prints the sum value |
Some of internal commands not related to SQL commands
1 2 |
postgres=# \h // help on the syntax of various PostgreSQL SQL commands postgres=# \q // used to exit |
Graphical User Interface (GUI) – A GUI provides a visual interface to interact with the PostgreSQL Some popular GUI tools for PostgreSQL is pgAdmin.
Uninstalling of PostgreSQL:
You can uninstall PostgreSQL by following the instructions below if you’re using Windows.
Firstly Close all the applications that are using PostgreSQL.
Go to the Control Panel and click on “Programs and Features”.
Find PostgreSQL in the list of installed programs and click on it.
Click on the “Uninstall” button and follow the on-screen instructions.
Once the uninstallation process is complete, you need to manually remove the PostgreSQL data directory. By default, the PostgreSQL data directory is located at C:\Program Files\PostgreSQL\13\data. You can delete this directory if you no longer need the data stored in it.
Connecting to database through pgadmin:
By following the below steps you can easily connect to the database,
Launch of the pgadmin 4 | • Search pgadmin >> run as administrator |
Creating a server | • Right click on the server >> open create menu >> click on server |
New server credentials | • In Create – Server window, Name : “enter a name for new server” |
Later, click on Connection > Host : localhost > port : 5432 > | • Password : “enter password” >> click on save |
Expand the server icon | • In the server a Database named postgres is created by default |
Accessing a Query Tool | • Expand the tools menu >> click on Query Tool |
A Query Editor will be opened there you can enter the SQL commands.
Try out the sql commands :
1 2 3 |
select version(); //prints version select current_date; //prints today’s date select 10+2; // prints the sum value |
Here, After entering the SQL Query used click on the run button, Then the Data output will be printed in the output widow. And it has an additional feature, you can run the selected Queries by highlighting those Queries.
These tools provide a visual interface to create, modify, and delete databases and tables, execute SQL queries, and manage the database server. With a GUI tool, you can easily navigate through the database schema, view data in tables, and create complex queries without typing SQL code manually.
** Using a GUI tool can be more user-friendly and convenient for those who prefer a visual interface over a command-line interface.
CREATE DATABASE:
Creating a Database using pgadmin:
By following steps you can create a database using pgadmin 4.
1 – Launch pgadmin 4 >> connect the server that you selected to create database.
2 – In the expand menu, right click on the “Databases” >> select “Create” >> select “Database..”
3 – In “Create –Database” window, enter a name for the database in the “Database” field.
4 – Check
Owner : By default currently logged in user
Encoding : UTF8 (default)
5 – Click on “Save” to create the Database.
Later you can check the created list of databases by expanding the database icon in the selected server. By default there is a database named “postgres” is created.
Creating a Database using psql:
To access the command line, open the SQL shell application and enter the login credentials to connect to the server.
In the prompt, enter the following SQL command
1 2 3 4 5 6 7 8 9 |
Postgres=# CREATE DATABASE dbname; // replace dbname with your database name By using Following command you can connect to required Database Postgres=# \c dbname; // replace dbname with your database name Now the prompt changes to database name it indicates user is now connected to the new Database : dbname=# Later you can check the list of databases by using following command: Postgres=# \l |
It provide the list of databases with attributes Name, Owner, Encoding, Collate, Ctype.
DROP/DELETE DATABASE:
Note that removing a database will result in all of its data being permanently deleted. The database should therefore only be dropped if you are absolutely convinced you want to.
Using pgAdmin:
To delete a database using pgAdmin
Expand the database menu >> right click on database to be deleted >> click on the drop/delete database >> click on “Yes”. It’s important to note that deleting a database is a permanent action and cannot be undone.
Using command line :
Firstly, connect to the postgres server and then select the database form the list of databases. later run the below command.
1 |
Postgres=# DROP DATABASE db_name //replace db_name with database name that you want to drop |
Author : Prudhvi Teja |
LinkedIn : http://linkedin.com/in/prudhvi-teja-nagabhyru-715052224
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTExperts is always active on social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform