PostgreSQL

The INSERT statement in PostgreSQL is used to add new rows of data to a table. When working with tables related to banking, such as customers, accounts, and transactions, the INSERT command helps populate the database with initial or transactional data. To use this command, you specify the table name, the columns to insert data into, and the corresponding values.

Example Scenario:

You have a banking system with tables for customers, accounts, and transactions, and you want to add new data to these tables using the INSERT statement.

Steps to Insert Data:

1. Inserting a Customer into the customers Table
   • Before inserting any data into a table, ensure that the table exists.
   • Use the INSERT INTO command to add data to the customers table.

   INSERT INTO customers (customer_id, name, email, phone)
   
   VALUES (1, 'John Doe', 'john.doe@example.com', '123-456-7890');

   • Explanation:
     • The INSERT INTO specifies the table customers where data is being inserted.
     • (customer_id, name, email, phone) defines the columns where the data will go.
     • VALUES specifies the actual values to be inserted.
2. Inserting an Account into the accounts Table
   • Insert new data into the accounts table that tracks customer accounts.

   INSERT INTO accounts (account_id, customer_id, account_type, balance)
   
   VALUES (101, 1, 'savings', 5000.00);

   • Explanation:
     • account_id is unique for each account.
     • customer_id associates the account with the customer.
     • account_type can be 'savings' or 'checking'.
     • balance holds the initial balance of the account.
3. Inserting a Transaction into the transactions Table
   • After setting up customers and accounts, transactions can be recorded in the transactions table.

   INSERT INTO transactions (transaction_id, account_id, amount,
   transaction_date)
   
   VALUES (1001, 101, 1500.00, '2024-09-17');

   • Explanation:
     • transaction_id is the unique ID for the transaction.
     • account_id links the transaction to the appropriate account.
     • amount is the transaction amount.
     • transaction_date records when the transaction occurred.
4. Inserting Multiple Records at Once
   • PostgreSQL allows inserting multiple rows in one statement.

   INSERT INTO customers (customer_id, name, email, phone)
   
   VALUES
   
   (2, 'Jane Smith', 'jane.smith@example.com', '987-654-3210'),
   
   (3, 'Emily Johnson', 'emily.johnson@example.com', '555-123-4567');

   • Explanation:
     • This inserts two rows into the customers table with a single INSERT statement.
5. Inserting Data without Specifying All Columns
   • You can insert data without specifying all columns, as long as default values or NULL are acceptable for the missing columns.

   INSERT INTO accounts (account_id, customer_id, account_type)
   
   VALUES (102, 2, 'checking');

   • Explanation:
     • The balance column will take a default value (or NULL if no default is set) because it's not specified in the INSERT.

This format provides an easy-to-understand, step-by-step guide for beginners working with PostgreSQL's INSERT command in the context of banking applications.