Creating database and tables | PostgreSQL

You create a database to store all your data and information in one place. The database is like an envelope containing and enclosing many sheets of data called tables.

The sheets represent the tables in the database where your data is stored. Data is stored in a grid or tabular format as shown below.

Each row in the grid is an entry in the table.

Creating database

CREATE DATABASE store_database;

The above command creates a database called store_database for us it is the name of the envelope where we will put our store sheets.

Creating Tables

What is data a store manager would like to keep?

• Employee details
• Product details
• Order details

These are called entities, each entity served a specific purpose, you cannot put employee details and product details together. For us, they mean a sheet in an envelope, each sheet in the envelope has particular data written on it. A sheet may be a list of products in the store, another sheet may be a list of employees in the store. In the terms of the SQL, each of these sheets represents a table in the database.

Each of the sheets (table) stores only one kind of data object(entity). Each entity has some attribute associated with it, for example, an employee Aniket has an ID, Name, and Salary whereas a product has an ID, Name, and Price. These attributes or columns are properties of the entity and will vary as the entity varies.

CREATE TABLE employees (
  id bigserial,
  full_name varchar(50),
  salary numeric
);

The above commands create a sheet/table for Employee data with each employee having properties id, full_name, and salary.

If you look at the above command you will notice full_name varchar(50) the full_name is the field or attribute an employee has whereas varchar(50) is the datatype the column/attribute can have, i.e name can only be characters in the same way salary can only be numeric and not a char because 2500qwerty is not a valid salary but 2500 is a valid salary.

Inserting rows into table

Let's start putting the data of employees in the employee table.

INSERT INTO store_database
  (full_name,    salary)
VALUES
  ('Aniket',     2500),
  ('Robert',    2600);

• The above command insert data into store_database.
• If you notice we are specifying the attribute or column names for which data has to be inserted.
• Notice we are inserting multiple values (...), (...) as comma-separated in parathesis.
• Data will appear in order of insertion

The result looks like the following in the PostgresSQL

In this article, we have learned how to create a database and table, and how to insert data into those tables. In the next article, we will learn how to read and query this tables.