PostgreSQL Basics 101 - Database and Tables

What is PostgreSQL?

PostgreSQL is a free and open-source database management system, commonly known as Postgres. As an object-relational database, it supports more complex data types and allows objects to inherit properties. However, this also makes working with Postgres more complex.

Similar to Oracle's MySQL, Postgres is a traditional relational database management system (RDBMS).

For those starting out, it's advisable to learn Postgres over MySQL, as its syntax aligns more closely with standard SQL. That being said, once you've grasped the fundamentals of one system, you can switch to the other with relative ease. As a newcomer to databases, the most challenging aspects will be understanding the underlying principles and utilizing the system effectively. Once you've got the basics, transitioning between systems becomes a much simpler task.

What are PSQL and PSQL commands?

PSQL refers to the Postgres interactive terminal, a command-line tool that enables users to interact with the PostgreSQL database system. Through PSQL, you can execute a wide range of SQL commands, manage tables, databases, users, and other objects within the system.

• \l lists all the available databases.
• \c <database_name>: connects to selected database
• \dt: lists all the tables in the current database
• \d <table_name>: describes the schame of a particular table
• \du: lists all the users and their roles.

• \password <username>: changes the password for a specific user.
• \q: quits the PSQL

Default database

When you first run your database and execute \l, you will see template1 and template0 already created for you. These are PostgreSQL's default databases.

If you plan to make modifications, you should always do so on template1 or any other database you have created. Never touch or edit template0, as it serves as a backup.

In case something breaks in template1, template0 is available to create a new template1.

Technically, you can delete these, but it isn't recommended. You won't gain anything from doing so, and some tools and extensions rely on their presence. Therefore, it's best to leave them untouched.

Creating your own database

running this command will create a new database.

CREATE DATABASE myroom;

Don't forget to add ; at the end and CREATE DATABASE doesn't need to be capital letters. Both Work just fine.

Database Collections

Databases are collections of related data tables grouped together. Imagine creating a database for your room, where you record your personal items. Adding your computer, desk, bed, etc. into the same database makes sense because they are all logically related.

Including your kitchen appliances in the myRoom database wouldn't make much sense, as they are not related and can quickly lead to confusion.

When creating a database, it's important to think about the future and plan well, as scalability can quickly become a problem.

For example, if you had an app that maintained error logs and user analytics, it would be sensible to separate them into two different databases. Because in a real world application, combining two inside the same database can cause scalability issues.

When starting out, you don't have to worry about this, most of the projects and tutorials you follow won't be complex enough to crate scalability issues. Just focus on learning and improving your understanding.

Creating a table

after running this command,

CREATE DATABASE myroom;

We connect to it via:

\c myroom

and create a table like this:

CREATE TABLE electronics (
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  title VARCHAR ( 255 ) UNIQUE NOT NULL
);

Let's break it down:

• CREATE TABLE, creates a new table and we name it electronics

Tables resemble spreadsheets, consisting of rows and columns. When writing the second line of code in Postgres, you essentially instruct the system to "Whenever I create a new item, assign it an id, ensure it is unique, and increment it by one."

• id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,

We need to use INTEGER because when we add a new item to the table, such as a cellphone, Postgres will assign a number starting from 1 as its id and won't accept a string/text. If a non-integer value is input, an error will be thrown, stating that the id must be an integer. The GENERATED ALWAYS AS IDENTITY clause gives control to Postgres, allowing it to create and assign a unique id to each value.

You can also use INT instead of INTEGER, which is the same thing. Also, INT4, DEC,DECIMAL and FIXED will do the same thing.

• title VARCHAR ( 255 ) UNIQUE NOT NULL

VARCHAR specifies that the title must be a string/text with a maximum of 0 to 255 characters, which corresponds to a single byte.

We could use TEXT instead of VARCHAR, but TEXT would accept an infinite number of characters. It's unlikely that the items in our room would have names exceeding 255 characters (though German readers might disagree), so using VARCHAR is more sensible and helps keep the database size smaller. For this application, saving a byte or two may not make a significant difference, but it can be important for large-scale enterprise applications.

UNIQUE NOT NULL does exactly what it sounds like. It ensures that each value is unique, so it won't allow us to enter "cellphone" twice. Additionally, NOT NULL means that a name must be provided and cannot be empty.

This code creates a table, automatically generates an id for each entry (value) and makes sure that each title is unique, and not empty.

Let's add "cellphone" to our table,

INSERT INTO myroom (title) VALUES ('cellphone');

This command adds one row to our table, with title of cellphone.

Remember the GENERATED ALWAYS AS IDENTITY command we have written earlier? Thanks to that, we don't need to do anything else, because Postgres will generate a unique id and assigns it to cellphone automatically. On older documentations, you may see SERIAL being used instead of GENERATED Al... they have the same functionality but GENERATED ... follows the generic SQL rules better with better performance. Use of SERIAL is not recommended.

Let's SELECT from table.

SELECT * FROM myroom;

will display a table just like this

id |    title
----+-------------
 1 | cellphone
(1 row)

Dropping table

We can drop our table if needed, let's say we messed up and our data isn't correct. We can do it simply like this:

DROP TABLE myroom;

However, you need to be careful when executing this command, as there is no turning back. Once Postgres drops your table, you cannot recover it.

If you've dropped the table, you can recreate it just like you did before.

Alter a table

Now, suppose we want to add images to our myroom table, which means we need to add another field.

ALTER TABLE electronics ADD COLUMN image VARCHAR (255);

ALTER TABLE informs Postgres that you want to modify a table.

• First, you select the table you want, in this case, electronics.

• Next, you specify the action, such as ADD COLUMN, which will add a column to the myroom table.

• After that, you provide a name for the new column, image, and define the type of data it will hold.

• As you may recall, VARCHAR(255) indicates that the image field will store a string containing between 0-255 letters/characters.

If you made a mistake or changed your mind, you can drop the image field like this:

ALTER TABLE electronics DROP COLUMN image;

This process is quite straightforward. We inform Postgres that we want to modify a table again. Then, we select the desired table, electronics, and specify the action, DROP COLUMN, followed by the column we want to remove, image.

Adding multiple records

We can add multiple records to our table like this:

ALTER TABLE electronics
ADD COLUMN location VARCHAR ( 255 ),
ADD COLUMN color VARCHAR ( 50 ) NOT NULL DEFAULT 'black';

Fun fact: Make sure its 'black' not "black" otherwise you will receive an error.

First, we inform Postgres that we want to alter a table and select the desired one. Next, we add a column called location, specify it as varchar, and limit the number of characters it can hold.

After that, we use a comma , to add another column. NOT NULL DEFAULT 'black'; means that each entry must have a value and cannot be null. If no value is provided, the default will be set to black. Make sure to add a semicolon ; at the end to complete your command. Otherwise, Postgres will wait for additional input.

Now, run \d electronics and you should see all the columns we have created.

Transaction control in Database management

When we need to perform a task with multiple steps, we want it to be 100% successful and nothing less. If you run commands one by one, your task will have a success rate ranging from 0% to 100%. By running everything at once or executing a block of related commands all together, you guarantee that the task will either completely fail or pass, with no in-between. This way, you can go back and check your work until it achieves a 100% success rate.

This is called Transaction control in database systems. We group multiple related commands into a single unit of work, also known as a Transaction.

This approach follows the ACID principles:

• Atomicity
• Consistency
• Isolation
• Durability

The ACID approach ensures data integrity and reliability. Transactions can help maintain data consistency, prevent inconsistent updates, and make troubleshooting easier.

More on data types

Postgres has many different data types and you can find them here

and there is even more! Postgres supports extensions, if you need something very specific there is a chance that you can find it. There are some extensions that can perform math equations.