Mastering PostgreSQL Table Creation: SQL Data Types, Constraints.

Welcome back to our "Learn Postgres with Me" series! In this instalment, we'll be diving into the fundamental concept of creating tables in PostgreSQL. Tables are at the heart of any relational database management system, serving as structured containers for storing and organizing your data. So, let's roll up our sleeves and get started with creating tables in PostgreSQL.

Let's see some prerequisite concepts before creating tables

Data types:

Data types define the kind of data a column can hold. PostgreSQL supports a wide range of data types, including:

Numeric Types: INTEGER, BIGINT, DECIMAL, etc.
Character Types: CHAR, VARCHAR, TEXT, etc.
Date and Time Types: DATE, TIMESTAMP, TIME, etc.
Boolean Type: BOOLEAN
Binary Data Types: BYTEA, BLOB, etc.
Enumerated Types: ENUM
Array Types: INTEGER[], VARCHAR[], etc.
JSON Types: JSON, JSONB
Geometric Types: POINT, LINE, POLYGON, etc.
Network Address Types: INET, CIDR
UUID Type: UUID
...and many more!

Data types determine the storage format, behaviour, and operations that can be performed on the data. Choosing the appropriate data type ensures efficient storage and manipulation of your data.

I am giving a link to the official Postgres documentation to learn more about datatypes

https://www.postgresql.org/docs/current/datatype.html

Constraints:

Constraints in PostgreSQL are rules or conditions that you can apply to your database tables to ensure data integrity, enforce relationships, and maintain consistency. They help to define the structure and behaviour of your database by specifying limitations on the data that can be stored in the tables. Constraints play a crucial role in maintaining the accuracy and reliability of your data. Here are some commonly used constraints in PostgreSQL:

PRIMARY KEY: Uniquely identifies each row in the table.
FOREIGN KEY: Establishes a relationship between two tables.
NOT NULL: Ensures a column cannot contain NULL values.
UNIQUE: Ensures values in a column are unique.
CHECK: Enforces a condition on the values in a column.
DEFAULT: Provides a default value for a column if not explicitly specified.

We will learn more about constraints later in this blog series.

Let's dive into creating Tables in PSQL

Tables without Constraints:

While constraints provide data integrity and maintain relationships, there might be scenarios where you need more flexibility, especially during initial data loading. Creating tables without constraints can simplify the process. However, remember that constraints help ensure data accuracy and consistency over time.

CREATE TABLE person (
    id BIGSERIAL,
    first_name VARCHAR(50),
    second_name VARCHAR(50),
    age INT,
    email VARCHAR(150) );

This will create a table named 'person' with the following columns id, first_name, second_name, age and email.

There is a unique datatype we introduced here called BIGSERIAL for the 'id' column. In PostgreSQL, the BIGSERIAL datatype is a special type designed to represent a large auto-incrementing integer. It's commonly used for creating primary key columns that need to store unique and automatically increasing values, such as IDs in tables. The BIGSERIAL type is essentially an auto-incrementing 8-byte integer that starts at 1 and increments by 1 for each new row inserted into the table. It can store values ranging from 1 to 9223372036854775807.

let's see the definition of the 'person' table. The definition of a table can be seen by following command \d person

Here the id value has a definition of person_id_seq which means it will auto-increment the id value.

Tables with Constraints:

For example, we will be seeing only one constraint for this blog 'NOT NULL'.

The NOT NULL constraint is a rule applied to a column in a database table that ensures the column must always contain a value; it cannot be left empty or contain a null value. In other words, the NOT NULL constraint enforces the requirement that a column must have a non-null value whenever a new row is inserted or an existing row is updated.

CREATE TABLE employees (
    employee_id BIGSERIAL ,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL,
    salary DECIMAL
);

Congratulations! You've just unlocked the power of creating tables in PostgreSQL. You've learned how to structure your data using different data types and how to enforce rules using constraints. These concepts are foundational for building effective and efficient databases.

In the next instalment of our series, we'll explore the exciting world of inserting and querying data with PSQL. Stay tuned, and keep practising your PostgreSQL skills!

Learn Postgres with Me: Day -2 - Creating Tables