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
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
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.
Top comments (0)