Introduction to PostgreSQL Database
PostgreSQL database is an open-source database system also an object-relational database system also it is accessed by using psql command, the current latest version of PostgreSQL is 12, we create a database using create database statement and drop the database using drop database statement, in PostgreSQL we can query to the database using database SQL prompt and OS command prompt, to query using database SQL prompt we need to login into the database by using username, password, and connecting database name, If we query to the database using OS command prompt also need to provide username and password.
How to Create a Database in PostgreSQL?
We can create a database by using the create database statement. We can create a database in PostgreSQL by using two methods:
Create database statement
- The database is created using the SQL command to create database statements. Please find below syntax and example for creating a database statement.
Syntax
Create database database_name;
Example
Postgres=# Create database db_testing;
Output:
- In the above syntax, the name shows the database name that we have using to create a new database in PostgreSQL. We can assign owner database user privileges to other user or we can change the owner of the database user later.
- After installing PostgreSQL first database is created by using initdb command.
Below are the default databases that were created at the time of installation.
- Postgres
- Template0
- Template1
- Postgres database is the administrative database of all the users in PostgreSQL. It is the default connection database of admin users.
- Template0 and template1 are the Meta database of other databases. At the time of creating a new database, these databases are used.
Create DB statement
- We can create a database in by using shell prompt, using the command as Createdb statement.
- Please find below syntax and example for createdb statement.
- Createdb statement connects to the database and issues create database command by using Createdb on shell prompt.
- If we have not given any arguments at the time of database creation it will create the database name same as username.
Syntax
Createdb Above syntax shows createdb as the command to create a database on the shell.
createdb database_name Database name refers to give any name to the newly created database.
createdb –U username database_name Username refers to the database user name.
Example
createdb
createdb test_db
createdb –U postgres db_test
\l+ test_db;
\l+ db_test;
Output:
- We can also set the database owner in PostgreSQL. The database owner has all the privileges and permissions to execute database related queries.
- We can query or select the PostgreSQL database using two methods.
Please find below the methods to query the database:
- Database SQL prompt
- OS command prompt
1. Database SQL prompt
- In Database SQL prompt first, we need to login into the PostgreSQL database.
- We can login into the database by providing a username, password, hostname and database name.
- Please find below the example to login to the database by providing username and password.
Example
psql –U postgres –d postgres
Output:
2. OS command prompt
- We can select the PostgreSQL database using the OS command prompt.
- We can select the database in OS prompt by supplying the same parameter that we have using in the database SQL prompt.
- We have to use parameters like username, password, port, hostname, etc.
- Please find below the example for the OS command prompt are as follows.
Example
psql -U postgres -d testing -c "select * from employee";
psql -U postgres -d testing -c "select version()";
Output:
How to select a particular database from multiple databases?
- We can select a particular database from multiple databases by using \l command.
- We can use the below command to select a particular database from multiple databases in PostgreSQL.
- Mostly we have using \l db_name command to select a particular database in PostgreSQL.
- Please find below syntax and example for the same.
Syntax
Please find below syntax to select a particular database from multiple databases in PostgreSQL.
\l database_name The database name refers to the specific name of the newly created database in PostgreSQL.
\l+ database_name If we need a descriptive output of the database then we have used \l+ and database name to display descriptive output.
Example
Please find below the example to select a particular database from multiple databases.
\l db_testing;
\l+ db_testing;
Output:
- In the above example, we have selected the db_testing database from multiple databases like db_test, Postgres, and test_db, etc.
- Also, we have checked the descriptive output of the db_testing database by using the \l+ db_testing command.
- This is a very important feature and commands to select a single database from multiple databases.
How to drop a database?
- We can drop the database using drop database command in PostgreSQL. We can drop the database using login to the database and using the OS command prompt in PostgreSQL.
- If we need a database no longer then we delete a specific database by issuing drop database command in PostgreSQL.
- Drop database statement deletes database entry, catalogs, and data directory permanently.
- Only database owners of the database can execute drop database command other users have not permission to execute drop database command.
- Also, the admin user has permission to execute the drop statement command.
- In this dropdb program utility available to drop the database. This utility allows you to remove the database. This utility executes the drop statement command behind the scene.
Please find below syntax and example to drop the database in PostgreSQL:
Syntax
drop database database_name
Example
postgres=# drop database db_testing;
Output:
Conclusion
We can create a database using create database statement also we have to define the owner of the database at the time of database creation. We can drop the database by using the drop database statement, only owner and database admin have privileges to drop databases in PostgreSQL.
Recommended Articles
This is a guide to PostgreSQL Database. Here we discuss how to create a database, drop the database and select data from multiple databases along with the respective examples. You may also have a look at the following articles to learn more –
