PostgreSQL - Day 9 - Part 2

• Wed, Jun 15, 2022
• 2-minute read

Keep learning PostgreSQL by Learn PostgreSQL Tutorial - Full Course for Beginners.

In this part, we’ll learn how to add extensions to Postgres and UUIDs.

53. Extensions

Check the extensions can be used in Postgres.

SELECT * FROM pg_available_extensions;

• uuid-ossp
  • It makes a good fit for primary keys.

54. Understanding UUID Data Type

UUID is the acronym for Universally Unique Identifiers (UUID), which is used to identify an object or entity on the internet uniquely. It’s globally unique. In other words, the collision is pretty much impossible.

1. Use the CREATE EXTENSION command to install an extension.

2. Install the uuid-ossp

   CREATE EXTENSION IF NOT EXISTS 'uuid-ossp';
   

3. use the \df command to check the available functions.

4. Generate uuid by the uuid_generate_v4() function

SELECT uuid_generate_v4();

Benefits of using UUIDs

Using UUIDs for tables’ primary keys can prevent tables from being deleted by hackers.

It’s globally unique that you can migrate data across databases without conflicts.

55. UUID As Primary Keys

There is a UUID data type in Postgres. Thus, the column which uses a UUID should be the UUID type.

Let’s recreate the person table with an id column using a UUID.

CREATE table person (
    id UUID NOT NULL PRIMARY KEY,
                ︙
);

Then we have to invoke the function which generates a UUID when inserting a record for a person.

insert into person (id, ...)
values (uuid_generate_v4(), ...);