SQL – Create a Database and a Table in PostgreSQL.

Let’s say you have some data about your company and want to create a database and tables to store and analyse this data.

Th employees salary information are in the Salary table and employees information in employees table.

id is the primary key for the Salary table and employee_id is the primary key for the employees table. And employee_id in the Salary table is a foreign key for the employee table.

Solution –

Creating a Database in PostgreSQL –

What is a database ?

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just database. – Oracle

Let’s create a database Life with data (lwd) to store both employee and salary table.

To create a database in SQL, we use the CREATE DATABASE statement. Open the pgAdmin tool that we installed previously. You can find the instruction for installing PostgreSQL and pgAdmin from here –

1. Windows.
2. Mac.

How to use pgAdmin from here – pgAdmin

Open the pgAdmin > PostgreSQL 14 > Databases > postgres > right click on postgres > Query Tool

Then write –

CREATE DATABASE lwd;

This statement creates a database on your server named lwd using default postgres settings.

Once you execute the query, right click and refresh the Databases on the left navigation, you will see that lwd database is created.

Connect to the lwd database –

Now that we have successfully created the lwd database but before we create any table we have to make sure that we are connected to this database and not the default postgres database.

1. Close the query tool by click x on top right, no need to save the previous query.
2. In the object browser right click on lwd and select Query Tool.
3. In the top right you will see lwd/postgres@PostgreSQL 14.
4. Now, any code you execute will be applied to the lwd database.

Create Tables in PostgreSQL –

What is a Table?

A table is a grid of rows and columns that stores data. Each rows holds a collection of columns, and each column contains data of a specified type: most commonly, numbers, characters, and dates. When you create a table, you assign a name to each column (sometimes referred to as a field or attribute) and assign it a data type. The Data type of a column determine what kind of data can be stored in that column. If you enter a text data into a date column then you will get an error.

CREATE TABLE Statement –

To create a table in a database we use the CREATE TABLE statement followed by the table name.

Syntax –

CREATE TABLE table_name (
    col1 datatype,
    col2 datatype,
    col3 datatype,
   ....
);

Let’s create both the salary table.

Salary Table –

CREATE TABLE Salary (
	id		bigserial,
	employee_id	int,
	amount		int,
	pay_date	date
	
);

The id column in this table is bigserial data type. It is a special integer data type that auto-increments every time you add a new row to the table. the first row gets the value of 1, second row 2, and so on.

The employee_id and amount is of int type, sort for integer. And the pay_date is a date (year, month, day) type.

To insert text in a column we can use char(n) for fixed length string and varchar(n) for variable length string where n is the length of the string.

Later we will talk more about various data types and constraints in a table. But for now, we will keep it simple. Make sure to follow our blog for the latest updates.

Once you execute the above code and right click refresh on lwd database, salary table will be inside the lwd > schemas > public > Tables > salary.

INSERT statement –

often when we work with large number of rows, the easiest method is to import data from a csv file or from other databases directly into table, which I will explain in the upcoming posts. But for now we will learn how to do it manually.

To insert data into a table we use the INSERT Statement.

Syntax –

INSERT INTO table_name (col1, col2, col3, ...)
VALUES (value1, value2, value3, ...);

Let’s insert data into the salary table.

INSERT INTO
    salary (employee_id, amount, pay_date)
VALUES
    (1, 9000, '2017/03/31'),
    (2, 6000, '2017/03/31'),
    (3, 10000, '2017/03/31'),
    (1, 7000, '2017/02/28'),
    (2, 6000, '2017/02/28'),
    (3, 8000, '2017/02/28');

If you run the above query, the salary table will be created. After the INSERT INTO we write the table name in which we want to insert the data followed by column names inside the parenthesis. In the next row we use the VALUES keyword followed by the data to insert into each column in each row. We did not inserted the values for the id column as it is bigserial type and the values gets automatically inserted for each rows as we discussed before.

To confirm that everything worked correctly, you can run the following sql query to see the data.

SELECT * FROM salary

We have successfully created the database and the salary table. Now go ahead and create the employee table yourself.

Related Posts –

1. How to install PostgreSQL and pgAdmin on Windows.
2. How to install PostgreSQL and pgAdmin on Mac.
3. How to use pgAdmin for PostgreSQL.
4. INSERT INTO – Add new record in a table.
5. Copy Table columns without rows in SQL.
6. Copy Rows from One Table into Another.