Google Images

Important tips on PostgreSQL

Recently, I have been working on my back-end skills development and before approaching the server side development, I needed to choose the database to do the transactions. For that, my team chose PostgreSQL, which comes with an extended SQL to work with the database management system.

I would like to thank Nelson for his amazing YouTube channel, which explains best things about PosgreSQL from scratch. Do check his channel out, and give him a medal, if you can. Hahaha!

Amigoscode

Who can explore this post? Those who have a basic understanding of how to use PostgreSQL with some basic SQL commands on Command Line Terminal on Windows and Terminal on Mac OS

Why PostgreSQL? This question is genuine, but first I would like to tell that, almost every start up company prefers PostgreSQL. Now the reasons:

a) Very professional platform to scale the complex data workloads

b) It has been almost 30 years, and the working is still commendable

c) PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions.

And many more. You want to know more about it, please follow this medium post blog: Why should I use PostgreSQL as Database in my StartUp/Company

Before we proceed, just wanted to tell you that, I am still working on my PostgreSQL skills, but I feel like these pointers will definitely help you making your database scalable :)

Also, check out this amazing website, if you really wanna refrain from adding a lot data from your side. The website is:

Mockaroo - Random Data Generator and API Mocking Tool | JSON / CSV / SQL / Excel

I NEED TO SEE THE CONTENTS OF THIS BLOG FIRST: Here is the list for you. Go to the particular topic, which you would like to explore:

1. LIMIT and OFFSET
2. IN
3. NUMERIC()
4. COALESCE()
5. Cascading NOW() for DATE and TIME
6. CHECK
7. Resetting the PRIMARY KEY
8. PostgreSQL generating UUIDs

Now let us jump to the part, where we can learn on some important pointers on PostgreSQL to make our development life easier:

1 LIMIT AND OFFSET: Frankly speaking, I did not have the conceptual knowledge about both of them, unless I tried doing them. To give you a definition,

A) LIMIT: It is the instruction which you give to the SQL query to actually limit the data set. For example, LIMIT 10, will give you 10 results only, despite of having numerous data

B) OFFSET: It is the instruction which you give on top of LIMIT, to give out the starting point of the data. For example, OFFSET 2 LIMIT 10 will give out data in the range starting from 3–10 [Please note: OFFSET number is EXCLUDED]. You can also use OFFSET only, to get the data starting from something to your Nth data. Let us get you some example on the terminal

Result of LIMIT and OFFSET

You can see the data coming up fine in my case, excluding the OFFSET LIMIT -> LIMIT. You can try more, do only LIMIT and OFFSET, and check out the result :D

2 IN: This doesn’t look very important when we see on the commands perspective, although it adds a great value when it comes to getting multiple data based upon a particular column. Like, what if we want to just choose people from a some country_of_births. We can use WHERE clause, and also use OR to get the data, but the problem here is it looks redundant and not a cleaner approach to what we want to do.

SELECT * FROM person WHERE country_of_birth = ‘China’ OR country_of_birth = ‘France’ OR country_of_birth = ‘Germany’;

Could you see, every time we had to use country_of_birth repetitively? Did not understand? Let me help you by giving an example:

Usage of IN query

You can add as many data you want, but it must be from the particular column, like here, country_of_birth

3 NUMERIC(Precision, Scale): This is also very important when it comes to playing with numerical data. Basically, a data type which helps you get the data up to the Precision [That is total length of the number] and Scale [Places of decimal]. Used while creating a table

CREATE TABLE table_name (price NUMERIC(8, 2) NOT NULL);

This will give out the data in column till 8 digits, with 2 place of decimal numbers only. See this:

Source: LearnSQL

For more information on this, please follow the below link

Understanding Numerical Data Types in SQL

4 COALESCE: Some of them might have used it, but I am sure, some beginners like would find it great. So what is it used for? It is used for defining the data for the null items. For example, you have some items in your column, which has some null data. Now, by default it would come blank. COALESCE helps you give out something if the data is null.

COALESCE usage in SQL

5 Cascading NOW() for DATE and TIME: I found this really useful, although the header might sound confusing, but, here is the thing, we usually get a data.

NOW() cascading for DATE and TIME

The cascading element is :: which is used with NOW() which is a DATE data type function. Here you see the example:

So can you see the data coming up for only DATE and TIME specifically? Also, we have only NOW() being implemented first, which gives the data in timestamp format

So, if you ever want your data to come in DATE or TIME format specifically, you can use this useful cascading function to get your desired data.

6 CHECK: Now, this is also a SQL Constraint which can be added if you want to add some extra layer of check on a particular column. For example, in my person table, I want the gender to be only Male and Female, no third gender. Then what we can do, is to add a constraint CHECK for that, and we can do that

ALTER TABLE table_name ADD CONSTRAINT constraint_name (column_name = condition);

Now, when you define this, and try to add a data, which gives false for your constraint, you will get the error coming from the database, that it doesn’t satisfy your provided condition. For more information, please follow this

SQL CHECK Constraint

7 Resetting the PRIMARY KEY: Now this is a way where you can go to the command line and reset the Primary Key. But there are certain pre-requisites, which you need to take care before making use of this:

→ You must have used BIGSERIAL in place of BIGINT data type. BIGSERIAL is a 8-bit signed auto-increment BIGINT data type, but it gives you the ability to take control of the Primary Key, if you have deleted all the data, and you don’t want to start the id from number other than 1, when you insert.

Please read upon PostgreSQL Data Types to know more about it

PostgreSQL: Documentation: 12: Chapter 8. Data Types

You can do this when you create a table CREATE TABLE table_name (id BIGSERIAL NOT NULL PRIMARY KEY)

Now when you do this \d table_name in your PostgreSQL command line terminal, you get the information like this

Primary Key Sequecne

You can see that, I have done this command SELECT * FROM sequence_name, which I got from doing \d table_name from the column Default. Since my last_value is already set to 1, but you can do this by doing this command in your PSQL Terminal

ALTER SEQUENCE your_sequence_name RESTART WITH your_number;

And then you can run SELECT * FROM sequence_name; And you will get the number which it was set by you.

8 PostgreSQL Generating UUID: Now, here is the truth, Universally Unique Identifier (UUID) is the safest and most cleaner way of approaching your data storage. It is universally accepted, and it disallow the attackers to really predict the ID for fetching out the data from the hosted Database.

We will learn how we will achieve this of generating UUIDs. This is bifurcated into the below topics:

A) Installing UUID generating extension on PostgreSQL using Command Line: This is important, cos without the installation you won’t be able to use the command uuid_generate_v4(). V4 is being the random UUID generator

Read more about extensions here:

PostgreSQL: Documentation: 12: H.4. Extensions

And more about UUIDs here:

Universally unique identifier

Now the real deal, how can we install the extension, simply do this in your console SELECT * FROM pg_available_extensions; and you will be loaded with extensions. We need to check for uuid-ossp is installed, if installed, the column installed_version will show you the version of this extension, else blank. Install it with command:

CREATE EXTENSION IF NOT EXISTS “uuid-ossp”;

The above command is idempotent, that means, you can type the command for N number, but it will only install if it is not installed already.

B) Generating the UUID: To generate the UUID, simply type the command SELECT uuid_generate_v4(); to see if it is working on your platform. Here is the representation

Generating UUID via terminal

You can now use the function uuid_generate_v4() while inserting the data for your column_name. You don’t need to make a primary key anymore, cos now it won’t autoincrement, but generate unique data every time you insert the data using uuid_generate_v4(). Please make sure that you have a column which uses this, so that you can do insert like this

INSERT INTO table_name (column_name) VALUES (uuid_generate_v4())

DISADVANTAGE: A major fallback I saw here is, it doesn’t auto operates, you either need loop to pass the data with uuid_generate_v4(), or use uuid_generate_v4() every time when you insert the data like the above mentioned command.

So that’s pretty much from my side. Big shout out to Nelson, for making awesome contents, which made me capable of writing this blog post. I am really grateful to him. I am sure you might have learnt some thing from that.

ALL THE BEST! KEEP LEARNING!