PostgreSQL

Chapter 5 - DDL (Data Definition Language)

Data Types

PostgreSQL provides a wide array of data types to handle different kinds of data effectively. Understanding the available data types and selecting the correct one ensures that your database performs efficiently and stores data in a reliable manner. In this context, we'll focus on data types useful for a banking system—for customers, accounts, and transactions. This guide starts with a brief overview of common data types, followed by a sample table creation combining these types.

Common Data Types in PostgreSQL

Below is a list of 30+ key data types in PostgreSQL, categorized for ease of understanding. These data types are particularly useful for defining tables related to banking systems like customers, accounts, and transactions.

1. Numeric Data Types

Used for storing numbers such as account balances, customer IDs, etc.

  1. SMALLINT: Stores small-range integers (-32,768 to +32,767).
  2. INTEGER: Stores standard whole numbers (-2 billion to +2 billion).
  3. BIGINT: Stores large integers (-9 quintillion to +9 quintillion).
  4. DECIMAL(p, s): Stores fixed-point numbers (precision and scale). Ideal for precise monetary values.
  5. NUMERIC(p, s): Same as DECIMAL. Used for monetary or other exact numeric values.
  6. REAL: Stores single precision floating-point numbers.
  7. DOUBLE PRECISION: Stores double precision floating-point numbers.
  8. SERIAL: Auto-incrementing integer, typically used for primary keys.
  9. BIGSERIAL: Auto-incrementing large integers for unique keys.

2. String Data Types

Used to store text such as customer names, account numbers, and descriptions.

  1. CHAR(n): Fixed-length character string. Used when you know the exact length.
  2. VARCHAR(n): Variable-length character string, with a limit of n. Example: Customer names.
  3. TEXT: Unlimited-length string. Example: Transaction descriptions or notes.
  4. UUID: Stores universally unique identifiers. Ideal for generating unique customer or account IDs.

3. Date and Time Data Types

Stores date and time information like account creation time and transaction dates.

  1. DATE: Stores only date (YYYY-MM-DD). Used for birthdates, transaction dates, etc.
  2. TIME: Stores time only (HH:MM:SS). Useful for storing banking hours or transaction time.
  3. TIMESTAMP: Stores both date and time (YYYY-MM-DD HH:MM:SS).
  4. TIMESTAMPTZ: Same asTIMESTAMP, but includes timezone information. Ideal for tracking transaction times across multiple locations.
  5. INTERVAL: Stores a period of time, like the duration between two dates.

4. Boolean Data Type

Stores TRUE orFALSE values, often used for flags or status indicators.

  1. BOOLEAN: Stores true/false values. Example: Whether an account is active.

5. JSON and JSONB Data Types

Used for storing unstructured data in JSON format.

  • JSON: Stores text-based JSON data. Useful for storing metadata or customer preferences.
  • JSONB: Stores binary JSON. Better for indexing and querying JSON data.

6. Geometric Data Types

Used for storing geometric shapes or spatial data, which can be useful for mapping branch locations.

  • POINT: Stores a point in a plane. Useful for GPS coordinates of bank branches.
  • LINE: Stores a line segment in a plane.
  • POLYGON: Stores a polygon. Useful for defining geographical areas.

7. Miscellaneous Data Types

Additional data types that don’t fit into the above categories but are still useful for various scenarios.

  1. BYTEA: Stores binary data, such as images or documents.
  2. INET: Stores an IPv4 or IPv6 host address. Example: Logging IP addresses for security.
  3. CIDR: Stores IP addresses and subnet masks.
  4. MACADDR: Stores MAC addresses.
  5. ARRAY: Used to store arrays (lists) of any data type.
  6. TSVECTOR: Stores text search vectors for full-text search.
  7. TSQUERY: Stores queries for full-text search.

Key Data Types for Banking Tables

Let's now apply these data types to a banking system by creating tables for customers, accounts, and transactions. These tables will utilize the appropriate data types to store customer information, account balances, and transaction details efficiently.

1. Customers Table

The Customerstable holds essential information like customer names, email, phone number, and birth date.

CREATE TABLE Customers (
    CustomerID SERIAL PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    PhoneNumber VARCHAR(15),
    DateOfBirth DATE,
    IsActive BOOLEAN DEFAULT TRUE
);
  • CustomerID: Auto-incrementing unique identifier (SERIAL).
  • FirstName and LastName: Variable-length text fields for storing names (VARCHAR).
  • Email: Unique email address for the customer.
  • IsActive: Boolean flag indicating if the customer is active.

2. Accounts Table

The Accounts table holds information about bank accounts, including account numbers, balance, and the associated customer.

CREATE TABLE Accounts (
    AccountID SERIAL PRIMARY KEY,
    AccountNumber VARCHAR(20) UNIQUE NOT NULL,
    AccountType VARCHAR(20) NOT NULL,
    Balance NUMERIC(12, 2) NOT NULL,
    CustomerID INT REFERENCES Customers(CustomerID),
    CreatedAt TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
  • AccountNumber: A unique identifier for the account.
  • Balance: Stores monetary values using the NUMERIC type to ensure precision.
  • CustomerID: Foreign key referencing the Customers table.

3. Transactions Table

The Transactionstable logs all transactions, including the accounts involved, the transaction amount, and the date.

CREATE TABLE Transactions (
    TransactionID SERIAL PRIMARY KEY,
    FromAccountID INT REFERENCES Accounts(AccountID),
    ToAccountID INT REFERENCES Accounts(AccountID),
    Amount NUMERIC(10, 2) NOT NULL,
    TransactionType VARCHAR(20),
    TransactionDate TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
  • TransactionID: Auto-incrementing identifier for each transaction.
  • Amount: UsesNUMERICto store the precise amount of money transferred.
  • TransactionDate: Automatically logs the date and time when the transaction occurs.

4. Branches Table

The Branches table stores information about bank branches, including their name and location.

CREATE TABLE Branches (
    BranchID SERIAL PRIMARY KEY,
    BranchName VARCHAR(100) NOT NULL,
    Location POINT
);
  • BranchID: Auto-incrementing identifier.
  • Location: Stores geographical coordinates using thePOINTdata type.

Complete Code Sample

Here’s the combined SQL code with all the table definitions for Customers, Accounts, Transactions, and Branches:

CREATE TABLE Customers (
    CustomerID SERIAL PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    PhoneNumber VARCHAR(15),
    DateOfBirth DATE,
    IsActive BOOLEAN DEFAULT TRUE
);

CREATE TABLE Accounts (
    AccountID SERIAL PRIMARY KEY,
    AccountNumber VARCHAR(20) UNIQUE NOT NULL,
    AccountType VARCHAR(20) NOT NULL,
    Balance NUMERIC(12, 2) NOT NULL,
    CustomerID INT REFERENCES Customers(CustomerID),
    CreatedAt TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Transactions (
    TransactionID SERIAL PRIMARY KEY,
    FromAccountID INT REFERENCES Accounts(AccountID),
    ToAccountID INT REFERENCES Accounts(AccountID),
    Amount NUMERIC(10, 2) NOT NULL,
    TransactionType VARCHAR(20),
    TransactionDate TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Branches (
    BranchID SERIAL PRIMARY KEY,
    BranchName VARCHAR(100) NOT NULL,
    Location POINT
);

This guide covers important PostgreSQL data types and demonstrates how to apply them when creating tables for a banking system. By using the correct data types, you ensure that the database stores and processes information efficiently, especially for critical operations like handling customer data, accounts, and transactions.

Tansy SQL Course - Data Types - Video Thumbnail

Sample implementation of MySQL data types

CREATE TABLE customer(
 customer_id BIGINT AUTO_INCREMENT PRIMARY KEY,
 customer_name VARCHAR(300),
 profile_picture BLOB,
 gender CHAR(1),
 birth_year YEAR,
 complete_address JSON,
 joining_date DATE,
 active_flag TINYINT,
 salary MEDIUMINT,
 credit_limit SMALLINT,
 to_date_purchase_amount DECIMAL(10,2),
 marital_status ENUM('Single', 'Married', 'Divorced', 'Widowed'),
 education SET('Bachelors', 'Masters', 'PHD'),
 feedback TEXT,
 record_creation TIMESTAMP
);

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100),
encrypted_security_question VARBINARY(512),
encrypted_security_answer VARBINARY(512),
password_hash BINARY(64) -- Assuming 64-byte hash value (e.g., SHA-256)
);

CREATE TABLE simulation_results (
id INT AUTO_INCREMENT PRIMARY KEY,
simulation_id INT,
experiment_start_time DATETIME,
experiment_duration TIME,
pressure DOUBLE,
velocity FLOAT,
energy DOUBLE
);
Previous Next
Comments(0 comments)

Comments Not Found

Most visited
PostgreSQL SELECT * 52 views
PostgreSQL WHERE Clause 38 views
PostgreSQL LIMIT OFFSET 36 views
PostgreSQL DDL ASSIGNMENT1 - Banking Application System 31 views
PostgreSQL Comparison Operators 27 views
PostgreSQL JSON SELECT 27 views