PostgreSQL Create Database and Table Tutorial (Step by Step)
PostgreSQL Create Database and Table Tutorial (Step by Step). In this article, we will look at how to create a database and table on PostgreSQL.
What is PostgreSQL
PostgreSQL is a free object relational database system with over 30 years of active development and featuring SQL compliance. PostgreSQL, commonly known as Postgres is a free open source Object Relational Database Management System (ORDBMS), launched in the late nineties by the University of California, Berkley. They designed it to handle a wide variety of workloads from single machines to data warehouses and it is state of the art software.
PostgreSQL features
- Atomicity, consistency, isolation and durability. So called (ACID) properties that display updatable views, foreign, triggers and stored procedures.
- It is available for windows and Linux systems.
- Compatible with various programming languages: It supports multiple programming interfaces such as C/C++, JAVA, Python, Perl, Ruby.
- Supports data integrity ( Primary Keys, Foreign Keys).
- Exclusion Constraints.
- Support multiple features of SQL.
- Highly extensible and secure ( LDAP)
- Postgres community is very helpful and accommodating to guide new users. The project continues to evolve under the alias PostgreSQL License.
For these reasons, it is the favorable solution for making databases and tables.
Connect to PostgreSQL Database from Windows Command Line
Open pgAdmin4 database and click Create Database.
CREATE DATABASE guru99
WITH OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'English_India.1252'
LC_CTYPE = 'English_India.1252'
TABLESPACE = pg_default
CONNECTION LIMIT = -1
COMMENT ON DATABASE guru99
IS 'Database for Guru99'
Create a Database in PostgreSQL
Next section OF PostgreSQL Create Database and Table is to create a database. The process is relatively simple and easy. You need to follow the steps below to create your own database. Bear in mind that installing RDMBS on Windows 10 is quite complicated process and requires reasonable level of expertise. So, we will start by teaching you how to connect to PostgreSQL database through the Windows 10 command prompt.
1. Establish the Environment Variable on Windows 10
- Before using the CLI to access PostgreSQL, you first need to establish your system’s environment variable. Failure to do so will result in future complications. Type ‘’env’’ in the Windows search bar and proceed to the ‘’Edit the system environment variables’’ option.
- After navigating to the mentioned option, you will be redirected to system properties, where you need to click on the Environment Variables Button under the Advanced tab.
- Then go to the path option located in the system variables section. This enables you to access the PostgreSQL environment easily from the Windows command prompt.
- Add a new path by copying and pasting the path of your PostgreSQL server’s bin directory at the required location, then click OK and close the System Properties Window.
2. Launch Windows Command Prompt
Following step of PostgreSQL Create Database and Table Tutorial (Step by Step) is to do the following:
- After the above-mentioned step, proceed to the windows search bar and type cmd
- Choose the icon of the command prompt
- The default Command Prompt window will become visible with no commands initiated
3. Use Command Prompt to Access PostgreSQL Environment
In order to get to your PostgreSQL environment through command prompt, you will need to use the following command:
psql –U postgres
- The Postgres in the above command represents the default username for the PostgreSQL server
- You can choose to create a new user during or after the installation of the PostgreSQL server
- If you have not created a new user, then the default username will remain ‘’Postgres’’
- After running the command above, you will be required to provide your user account password, which is created during the PostgreSQL server’s installation
- After entering the correct password, you will be able to access the PostgreSQL environment without exiting the Windows command prompt.
4. Create PostgreSQL Database
Next step in PostgreSQL Create Database and Table Tutorial (Step by Step) is to:
- Create a database, if you have already created one, then feel free to skip ahead in the tutorial.
- Use the following command to create database:
CREATE DATABASE sampleDB WITH ENCODING 'UTF8' LC_COLLATE=‘English_United States' LC_CTYPE=‘English_United States';
- SampleDB represents the database name
- If you have followed the previous steps correctly, then you will get a ‘’CREATE DATABASE’’ on your command prompt
5. Establish connection between the newly created PostgreSQL Database
- Execute the following command to allow connection of the database:
psql sampleDB
- At this step, sampleDB represents the database to which you want to connect, this can be replaced with desired PostgreSQL database. If you have followed the steps correctly, you will be able to establish connection to the database, but there will be no acknowledgement on the command prompt.
Create a Table in PostgreSQL
CREATE TABLE Products
(
ProductCode int primary key,
ProductName varchar(50) not null,
PurchaseDate date
)
In order to create a table in PostgreSQL, you need to follow a series of simple steps given below. This tutorial is easy to follow and helps you save hours of work.
Syntax
In order to create a table, you need to use the CREATE TABLE statement, as a relational database consists of multiple tables which are interrelated. Below is the illustration of basic syntax for creating a table:
CREATE TABLE [IF NOT EXISTS] table_name (
column1 datatype(length) column_contraint,
column2 datatype(length) column_contraint,
column3 datatype(length) column_contraint,
table_constraints
);
- In the above syntax, specify the name of the table after the keywords CREATE TABLE.
- It should be noted that this is for creating a new table if a table exists and you still apply the command it will result in an error.
- Then, specify a comma separated list of table columns. Where each column consists of the column name, the data type that the column stores, data length, and column of constraint.
- The column constraints specify the rules for column data to be followed
- Finally, you should specify the table constraints including primary key, foreign key, and check constraints.
Constraints
- NOT NULL:
- ensures that column value is not NULL
- UNIQUE:
- ensures that values across columns unique across the rows within the same table
- PRIMARY KEY:
- It identifies the rows in a table. This feature helps you to decide the primary key of the table as a table can only have one primary key
- CHECK:
- This ensures that data satisfies a boolean expression
- FOREIGN KEY:
- It ensures that value in a column from a table exists in a column in another table. A table can have multiple foreign keys
Table Creation Summary
- Use the CREATE TABLE statement to create a new table
- Use the IF NOT EXISTS option to create the new table only if it does not exist
- Apply the primary key, foreign key, not null, unique, and check constraints to columns of a table.
- Table constraints are similar to column constraints, except they apply to many columns instead of just one.
PostgreSQL Create Database and Table Tutorial Conclusion
We covered how to create databases and tables in PostgreSQL. PostgreSQL is one of the best tools for maintaining, generating and manipulating tables and databases on the market. The user interface is simple to use, convenient, and accessible. On the program’s official forums, there is a wealth of information and lessons. For the past 40 years, the software has been updated on a regular basis to assure quality and security.
Creating a database in PostgreSQL ensures that data is stored efficiently and securely for a variety of applications, including Web, mobile and analytical applications. As we’ve seen, a database may be established with a few basic instructions, which may take some experience. However, making a table is a far simpler operation. PostgreSQL should be your first choice for creating databases and tables since it is open-source, secure and community driven.
