PostgreSQL Tutorial: Creating Tables, Inserting and Basic Querying.
Published: 2023-04-29 13:37:12

### Introduction
PostgreSQL is an open-source object-relational database management system that provides a reliable, scalable, and secure platform for storing and managing structured data. PostgreSQL is known for its robustness, flexibility, and extensibility, which make it a popular choice for a wide range of applications, from small-scale web applications to large-scale enterprise systems. It supports a variety of data types, including numeric, character, date/time, and binary data, and offers advanced features such as support for transaction processing, multi-version concurrency control, and nested transactions.

In this article i'll be demonstrating how you can create databases, and also creating tables in this database, as well as querying the table for data. To get started we need to login into the postgres server. 

**1. Connecting the server**
```
ceejay@ceejay:~$ psql

psql (12.14 (Ubuntu 12.14-1.pgdg22.04+1))

Type "help" for help.



ceejay=# 

```

In case the above command doesn't work for you run the following command
`sudo -u postgres psql`. Then go ahead and create a user and grant the user required privileges.
You can follow the guide in this article to create a new user [https://phoenixnap.com/kb/postgres-create-user](https://phoenixnap.com/kb/postgres-create-user
)


**2. Creating a database.**

Currently these are the databases available. (Yours might be different)
```
ceejay=# \l

                             List of databases

   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   

-----------+----------+----------+---------+-------+-----------------------

 ceejay    | ceejay   | UTF8     | en_NG   | en_NG | 

 postgres  | postgres | UTF8     | en_NG   | en_NG | 

 template0 | postgres | UTF8     | en_NG   | en_NG | =c/postgres          +

           |          |          |         |       | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_NG   | en_NG | =c/postgres          +

           |          |          |         |       | postgres=CTc/postgres

 test      | postgres | UTF8     | en_NG   | en_NG | 

(5 rows)

```
Now let's create a new database named `Record`

```
ceejay=# CREATE DATABASE Record;

CREATE DATABASE

ceejay=# \l

                             List of databases

   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   

-----------+----------+----------+---------+-------+-----------------------

 ceejay    | ceejay   | UTF8     | en_NG   | en_NG | 

 postgres  | postgres | UTF8     | en_NG   | en_NG | 

 record    | ceejay   | UTF8     | en_NG   | en_NG | 

 template0 | postgres | UTF8     | en_NG   | en_NG | =c/postgres          +

           |          |          |         |       | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_NG   | en_NG | =c/postgres          +

           |          |          |         |       | postgres=CTc/postgres

 test      | postgres | UTF8     | en_NG   | en_NG | 

(6 rows)
```

Now we've created a database. Next we connect to the database.


**3. Connect to the database**

```
ceejay=# \connect record

You are now connected to database "record" as user "ceejay".

record=# 

```


We just connected to the record database. But before we start creating tables in postgresql databases there's what we call schemas. 

In PostgreSQL, a schema is a named logical container for database objects such as tables, indexes, views, and functions. Schemas are a way to organize and group database objects, and can be used to separate different applications or parts of an application that use the same database.

Each schema in a PostgreSQL database is a namespace that contains a collection of database objects. Schemas allow you to logically separate database objects and organize them in a hierarchical structure, which makes it easier to manage the database and avoid naming conflicts between different objects.

By default, PostgreSQL creates a schema named `public` when you create a new database. However, you can create additional schemas as needed, and assign database objects to different schemas to organize them according to their purpose or ownership. This can help to improve security, as you can grant or revoke privileges on a per-schema basis, and control access to specific parts of the database.

Maybe for personal practice it's not really important to create a personalized schema for your objects(Tables, functions, triggers...), But it's a good practice to always group your objects into schemas for production database. To check the schemas present in your current database. 

```
record=# \dn

  List of schemas

  Name  |  Owner   

--------+----------

 public | postgres

(1 row)

```

As said earlier the default schema for newly created databases is the `public` schema. So if we don't specify a schema it just creates them in the `public` namespace. For the sake of demonstration we'll be using the public schema. But if you need to create a schema and create let's say a table in that schema you can just work with the following commands in psql. 

create the schema.
```
CREATE SCHEMA <schema_name>;
```

After creating the switch to the schema using

```
SET search_path TO <schema_name>;
```

Then you can create tables. 
 

**4. Create Tables**
Let's create a table `user_table`
```
record=# CREATE TABLE user_table(

record(# id SERIAL PRIMARY KEY,

record(# first_name VARCHAR(20) NOT NULL,

record(# last_name VARCHAR(20) NOT NULL,

record(# age INTEGER NOT NULL,

record(# married BOOLEAN NOT NULL DEFAULT FALSE

record(# );

CREATE TABLE

record=# 

```

We start by using the command `CREATE TABLE` to create a table. The command is case insensitive (i.e you can use `create table`)
We the define the various fields which would serve as the column heading for the `user_table`. The line `id SERIAL PRIMARY KEY` simply means define a field name `id` with data type of `SERIAL` and a primary key constraint. There are different data types in postgreSQL you can read more on the official documentation [here](https://www.postgresql.org/docs/current/datatype.html)

The `Serial` datatype is an integer type(numbers) only that it'll auto-increment for every entry into the database. So you don't have to manually set it. The Primary Key constraint means this is going to be a unique field used to uniquely identify a row in the table. 

`VARCHAR` is simply for variable length character, and we set the max length for both the first name and last name to be `20`. The `NOT NULL` is a flag that simply means the field cannot be empty, which means when we add a new row in the table we cannot omit this field, there must be a set value. 
We can also have default values as seen in the `married` field.


There many more datatypes and constraints that you can use on tables, (e.g FOREIGN KEY, UNIQUE etc...) we won't go deep into those yet. 

Now that we've created a table let's insert data into the created table. 


**5. Insert Rows into the table**
Let's insert few rows into our table.

-> Inserting one row
```
record=# INSERT INTO user_table (first_name, last_name, age, married) VALUES ('John', 'Doe', 23, TRUE);

INSERT 0 1

```
-> We can insert multiple rows

```
record=# INSERT INTO user_table (first_name, last_name, age, married) VALUES ('chris', 'hemsworth', 32, TRUE), ('tom', 'hanks', 52, TRUE), ('Tom', 'holland', 23, FALSE), ('Jason', 'bourne', 33, FALSE);

INSERT 0 4

record=# 

```

-> Inserting while neglecting the married field (we allow postgres to set the default value).

```
record=# INSERT INTO user_table (first_name, last_name, age) VALUES ('johnny', 'depp', 42);

INSERT 0 1

record=# 

```

Now that we have some rows in our `user_table` how do we get the rows ?



**6. Basic Database Querying**

-> To show all the fields of the rows inserted

```
record=# INSERT INTO user_table (first_name, last_name, age) VALUES ('johnny', 'depp', 42);

INSERT 0 1

record=# SELECT * FROM user_table;

 id | first_name | last_name | age | married 

----+------------+-----------+-----+---------

  1 | John       | Doe       |  23 | t

  2 | chris      | hemsworth |  32 | t

  3 | tom        | hanks     |  52 | t

  4 | Tom        | holland   |  23 | f

  5 | Jason      | bourne    |  33 | f

  6 | johnny     | depp      |  42 | f

(6 rows)



record=# 
```

**Notice the how we didn't need to manually set the `id` field while inserting and yet still present in the table.**

-> Get just the first_name and last_name
```
record=# SELECT first_name, last_name FROM user_table;

 first_name | last_name 

------------+-----------

 John       | Doe

 chris      | hemsworth

 tom        | hanks

 Tom        | holland

 Jason      | bourne

 johnny     | depp

(6 rows)



record=# 
```

-> Changing the column header names to "FIRST NAME" and "LAST NAME"
```
record=# SELECT first_name AS "FIRST NAME", last_name AS "LAST NAME" FROM user_table;

 FIRST NAME | LAST NAME 

------------+-----------

 John       | Doe

 chris      | hemsworth

 tom        | hanks

 Tom        | holland

 Jason      | bourne

 johnny     | depp

(6 rows)



record=# 

```

-> Get the first 3 results

```
record=# SELECT first_name AS "FIRST NAME", last_name AS "LAST NAME" FROM user_table LIMIT 3;

 FIRST NAME | LAST NAME 

------------+-----------

 John       | Doe

 chris      | hemsworth

 tom        | hanks

(3 rows)



record=# 

```

-> Get rows where the first name is tom
```
record=# SELECT first_name AS "FIRST NAME", last_name AS "LAST NAME" FROM user_table WHERE first_name LIKE 'tom';

 FIRST NAME | LAST NAME 

------------+-----------

 tom        | hanks

(1 row)



record=# SELECT first_name AS "FIRST NAME", last_name AS "LAST NAME" FROM user_table WHERE first_name ILIKE 'tom';

 FIRST NAME | LAST NAME 

------------+-----------

 tom        | hanks

 Tom        | holland

(2 rows)



record=# 

```

The first query is case sensitive while the second is insensitive to the casing of the text.


-> We can also order our outputs based on a different column, default is using the `id` column, let's change this to the first_name. 

```
record=# SELECT * FROM user_table ORDER BY first_name;

 id | first_name | last_name | age | married 

----+------------+-----------+-----+---------

  2 | chris      | hemsworth |  32 | t

  5 | Jason      | bourne    |  33 | f

  1 | John       | Doe       |  23 | t

  6 | johnny     | depp      |  42 | f

  3 | tom        | hanks     |  52 | t

  4 | Tom        | holland   |  23 | f

(6 rows)



record=# 

```


### Conclusion

Creating, inserting, and querying a database can be as easy as those shown in this article and but can also get really complicated. But understanding the basics is the first step into becoming a pro at constructing a sophisticated table structure. Learn more about the different constraints, different table relationships, then delving more into how to make complex queries, aggregations, regex etc.