PostgreSQL

The SELECT INTO statement in PostgreSQL is used to create a new table and populate it with the results of a SELECT query. It is useful when you need to make a backup of data or create a subset of data from existing tables. This operation copies the structure and data from the existing table(s) into the newly created table. It's a simple way to perform operations without affecting the original data.

Here’s a step-by-step breakdown of how SELECT INTO works, with sample definitions for banking, customers, accounts, and transactions:

1. Basic Syntax ofSELECT INTO
   • The SELECT INTO statement creates a new table and inserts the result of a SELECT query into it.
   • Syntax:

   SELECT columns
   INTO new_table
   FROM existing_table
   WHERE conditions;
   

2. Example for Banking Data
   • Suppose you have a table for banking transactions and want to create a new table high_value_transactions that contains all transactions above $10,000.

   SELECT transaction_id, customer_id, account_id, amount, transaction_date
   INTO high_value_transactions
   FROM transactions
   WHERE amount > 10000;
   

3. Sample Table Definitions
   • Before using SELECT INTO, assume you have the following table structures in your PostgreSQL database:
   • Customers:

   CREATE TABLE customers (
     customer_id SERIAL PRIMARY KEY,
     name VARCHAR(100) NOT NULL,
     email VARCHAR(100) UNIQUE,
     phone VARCHAR(15),
     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );
   

   • Accounts:

   CREATE TABLE accounts (
     account_id SERIAL PRIMARY KEY,
     customer_id INT REFERENCES customers(customer_id),
     balance DECIMAL(12, 2),
     account_type VARCHAR(50),
     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );
   

   • Transactions:

   CREATE TABLE transactions (
     transaction_id SERIAL PRIMARY KEY,
     customer_id INT REFERENCES customers(customer_id),
     account_id INT REFERENCES accounts(account_id),
     amount DECIMAL(12, 2),
     transaction_date DATE NOT NULL,
     description VARCHAR(255)
   );
   

4. Creating a New Table withSELECT INTO
   • Let's say you want to extract all customer details who have made transactions worth more than $5,000. You can create a new table high_value_customers with the SELECT INTO statement:

   SELECT DISTINCT c.customer_id, c.name, c.email, c.phone
   INTO high_value_customers
   FROM customers c
   JOIN transactions t ON c.customer_id = t.customer_id
   WHERE t.amount > 5000;
   

5. Points to Remember:
   • New Table Creation: The new table is created in the database with the same column definitions as the selected columns in the query.
   • Data Types: The columns in the new table will automatically inherit the data types from the original table.
   • Performance Consideration: SELECT INTO is generally faster than INSERT INTO...SELECT for large data sets, as it avoids transaction logging.
   • Limitations: The newly created table does not have any constraints, such as primary keys or foreign keys, unless explicitly defined.

By following these steps, students can effectively create backup or analysis tables from existing data without altering the original tables. The SELECT INTO statement is a powerful tool for handling data manipulation tasks in PostgreSQL.

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