PostgreSQL

In PostgreSQL, the CREATE TABLE is fundamental for defining new tables in a database. It specifies the table structure, including column names, data types, and constraints. Understanding how to use CREATE TABLE is crucial for setting up a database schema effectively. Below, you'll find a step-by-step guide to creating tables, along with sample code relevant to a banking system, including tables for customers, accounts, and transactions.

Steps to Create Tables in PostgreSQL

1. Basic Syntax

   CREATE TABLE table_name (
       column_name data_type [constraints],
       ...
   );
   

   • table_name The name of the table you want to create.
   • column_name The name of each column in the table.
   • data_type The type of data the column will hold (e.g., INTEGER, VARCHAR).
   • constraints Optional rules to enforce data integrity (e.g., PRIMARY KEY, NOT NULL).
2. Example: Creating a customers table

   CREATE TABLE customers (
       customer_id SERIAL PRIMARY KEY,
       first_name VARCHAR(50) NOT NULL,
       last_name VARCHAR(50) NOT NULL,
       email VARCHAR(100) UNIQUE,
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );
   

   • customer_id A unique identifier for each customer, automatically incremented.
   • first_name and last_name Required fields for customer names.
   • email A unique field for the customer's email address.
   • created_at A timestamp for when the record was created, defaulting to the current time.
3. Example: Creating an accounts table

   CREATE TABLE accounts (
       account_id SERIAL PRIMARY KEY,
       customer_id INTEGER REFERENCES customers(customer_id),
       account_type VARCHAR(20) NOT NULL,
       balance DECIMAL(15, 2) NOT NULL CHECK (balance >= 0),
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );
   

   • account_id A unique identifier for each account.
   • customer_id: A foreign key linking to the customers table.
   • account_type: Type of account (e.g., savings, checking).
   • balance: The account balance, must be non-negative.
   • created_at: A timestamp for when the account was created.
4. Example: Creating a transactionstable

   CREATE TABLE transactions (
       transaction_id SERIAL PRIMARY KEY,
       account_id INTEGER REFERENCES accounts(account_id),
       transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
       amount DECIMAL(15, 2) NOT NULL,
       transaction_type VARCHAR(10) CHECK (transaction_type IN ('deposit', 'withdrawal')),
       description TEXT
   );
   
   

   • transaction_id A unique identifier for each transaction.
   • account_id A foreign key linking to the accounts table.
   • transaction_date The date and time of the transaction.
   • amount The amount of money involved in the transaction.
   • transaction_type Specifies whether the transaction is a deposit or withdrawal.
   • description Optional text field for additional details about the transaction.
5. Considerations for Table Design
   • Ensure that each table has a primary key for unique identification.
   • Use foreign keys to establish relationships between tables.
   • Apply constraints to enforce data integrity and business rules.

To gain complete access, login with gmail or outlook, no need of signup. click here