PostgreSQL is a full-featured, open-source object/relational database management system (DBMS) supporting SQL and the most important data models used in software development today. The core of PostgreSQL includes SQL-92 compliance, full ACID transaction support, and built-in high availability.
What is the database?
Your information, details, data are stored in a database. The database is the collection of data.
The database engine is software that provides the functionality of managing databases. In other words, the database engine helps you create, manage, access, modify, and store data in a database. The database engine allows you to interact with your data in various ways. It also makes it easier to access, update, and retrieve information from the database. The database engine is a critical component of any relational database management system (RDBMS).
What is PostgreSQL?
PostgreSQL is a powerful, open-source, object-relational database system. It’s very well suited for data management and data analysis, and it’s used by hundreds of thousands of people in the world. What does that mean? For starters, you can use it to store all kinds of data, including structured data like data from web applications or documents, as well as unstructured data like text, audio, and video files.
Postgres itself is a very solid, mature database that works well on almost any platform. For those of you who are new to the Postgres ecosystem, this is a great opportunity to get started with it.
In this blog post, we are going to see how to create a database in postgresql using command line. We will also see how to import data from text file into the newly created database.
What to consider before creating a new database of PostgreSQL?
When you are creating a new database on your PostgreSQL server, you should think about what kind of data will be stored in it. If you have some idea about the nature of your application and you know which kind of data will be needed for this application, you can make a better choice about what kind of database you will need.
In the case of a web application, you usually store all user-related information in one database table: users. You may also want to store other data about users such as their groups or the last login time. The data that will be needed by the application is not always only the user data. For example, an administrator might need to access some data about users and the system configuration.
How to create database using PostgreSQL?
You can create a database using the CREATE DATABASE command.
The command will create a database from the shell prompt, but you should have the right to create a database. The standard system database template1 will be cloned in order to create a new database.
Using CREATE DATABASE command:
Syntax
The basic syntax of CREATE DATABASE statement is as follows −
CREATE DATABASE database_name;
where database_name is the name of a database to create.
Example
The following is a simple example, which will create orageekdb in your PostgreSQL command prompt
postgres=# CREATE DATABASE orageekdb; postgres-#
Using createdb Command:
The createdb is a wrapper around the command CREATE DATABASE. The only difference between the two commands is that the former can be run from the command line and it allows a comment to be added into the database.
The CREATE DATABASE command is used in the PostgreSQL command line. The only difference between the two commands is that the former can be run directly from the command line and it allows a comment to be added into the database.
1) Create a database with default parameters
You can create database without any parameter. It means with default parameters.
Example:
createdb orageekdb or
CREATE DATABASE orageekdb;
2) Create a database with some parameters.
You can create database with some or all parameters. It means you need to provide values of parameters.
Example:
CREATE DATABASE orageekdb
WITH
ENCODING = 'UTF8'
OWNER = orageek
TABLESPACE = users
CONNECTION LIMIT = 200;
Syntax
The syntax for createdb is as shown below −
createdb [option...] [dbname [description]]
Parameters
The table given below lists the parameters with their descriptions.
S. No. | Parameter & Description |
---|---|
1 | dbname
The name of a database to create. |
2 | description
Specifies a comment to be associated with the newly created database. |
3 | options
command-line arguments, which createdb accepts. |
Options
The following table lists the command line arguments createdb accepts −
S. No. | Option & Description |
---|---|
1 | -D tablespace
Specifies the default tablespace for the database. |
2 | -e
Echo the commands that createdb generates and sends to the server. |
3 | -E encoding
Specifies the character encoding scheme to be used in this database. |
4 | -l locale
Specifies the locale to be used in this database. |
5 | -T template
Specifies the template database from which to build this database. |
6 | –help
Show help about createdb command line arguments, and exit. |
7 | -h host
Specifies the host name of the machine on which the server is running. |
8 | -p port
Specifies the TCP port or the local Unix domain socket file extension on which the server is listening for connections. |
9 | -U username |
10 | -w
Never issue a password prompt. |
11 | -W
Force createdb to prompt for a password before connecting to a database. |
You can open the command prompt and go to the directory where the database is installed. To create a database, you have to go to the bin directory and execute the following command.
createdb –h localhost –p 1567 –U postgresadm orageekdb password ******
By default, the password for the postgresadm admin user will be prompt by the above given command. If you want to create a new database, you must provide a password.
PostgreSQL Create Database using pgAdmin:
The pgAdmin tool provides you with an intuitive interface for creating a new database. It also allows you to browse and query existing databases. You can add, edit, and delete tables and views as well as add, edit, and delete columns in those tables. The pgAdmin tool also provides you with an intuitive interface for creating a new database. It also allows you to browse and query existing databases.
The following are some of the features of pgAdmin: Standard database management features for PostgreSQL, such as create, edit, rename, and drop tables, indexes, views, sequences, and functions Navigation between the tables in the database Finding the data types.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. These all Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.