-- Example:
CREATE TABLE IF NOT EXISTS employees (
emp_id serial PRIMARY KEY,
lastname varchar(100),
firstname varchar(100),
department varchar(100) NOT NULL,
start_date timestamp
);
-- ' IF NOT EXISTS ', ' PRIMARY KEY ', ' NOT NULL ' are optional
CREATE TABLE new_table_name AS
SELECT column1, column2,…
FROM exiting_table
WHERE …;
-- Example:
CREATE TABLE employees_short AS
SELECT emp_id, lastname, department
FROM employees;
1.3. Using SELECT Statement (with input data)
i. From a Different Table
SELECT emp_id, lastname, department
INTO employees_short_v2
FROM employees
ii. Manual
SELECT numbers, alphabet
FROM (VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d'),
(5,'e'),
(6,'f'),
(7,'g'),
(8,'h'),
(9,'i'),
(10,'j')) s (numbers,alphabet)
Prior to creating a table using a SELECT statement, first create the new table with desired datatypes and constraints, and then transfer the data into newly created table:
Step 1: Create a new table with desired columns, and the datatypes and constraints for those columns.
CREATE TABLE employees_short (
emp_id serial PRIMARY KEY,
emp_name text );
Step 2: Copy data from the old table to the new one.
INSERT INTO employees_short(emp_name)
SELECT emp_name FROM employees;
2. Data Modifications
2.1. Inserting Rows (Populating Table)
INSERT INTO <tablename> VALUES (val1, val2, …)
/*
When we have incomplete data and the table incorporates columns with
default values, we can insert rows using only the available data by
explicitly specifying those columns.
*/
INSERT INTO <tablename> (col1, col2, ..) VALUES (val1, val2, …)
2.2. Updating Rows
UPDATE <tablename> SET col1 = val1 , col2 = val2, …
WHERE condition;
2.3. Deleting Rows
DELETE FROM <tablename>
WHERE condition;
-- NOTE: without WHERE clause all records will be deleted
ALTER TABLE <tablename>
RENAME TO <new_tablename>;
3.3. Dropping tables
DROP TABLE IF EXISTS <tablename>;
-- Note: add CASCADE at the end, if the table is associated with other table(s) ;
Different databases offer different approaches to modify their tables. Therefore, it is advisable to refer to your database documentation before proceeding with any changes. Some of the most commonly used database systems: Postgres, MySQL, SQLite, and Microsoft SQL Server
4. Column Modifications (Most Commonly Used)
4.1. Renaming columns
ALTER TABLE <table_name>
RENAME COLUMN <old_column_name> TO <new_column_name>;
4.2. Changing columns' datatype
ALTER TABLE <table_name>
ALTER COLUMN <column_name> TYPE <new_data_type>;
4.3. Dropping columns
ALTER TABLE <table_name>
DROP COLUMN <column_name>;
ALTER COLUMN <column_name> TYPE <new_column_type>;
DROP COLUMN <column_name>;
For complete list of PostgreSQL column modifications, please check the documentation.
If you need to see the datatype for existing tables in Postgres, please check the Displaying Existing Tables' Columns and Their DataTypes in the Data Types section.
5. Creating Views
A view is a stored query that acts as a virtual table. When you query a view, PostgreSQL dynamically executes the underlying query and presents the results as if they were stored in a table. For more information about views please visit What is a View? section under Database Fundamentals.
5.1. Creating View(s)
CREATE VIEW <view_name> AS
SELECT <column_names> FROM <table_name>;
-- Once the view is created, we perform queries the same way
SELECT *
FROM <view_name>;
5.2. Deleting View(s)
DROP VIEW [IF EXISTS] view_name [, ...] [CASCADE | RESTRICT];
-- IF EXISTS: Optional clause to prevent an error if the view does not exist.
-- view_name: The name of the view you want to delete.
-- CASCADE | RESTRICT: Optional clauses to specify the behavior if there are dependent objects.
-- CASCADE deletes dependent objects as well
-- RESTRICT raises an error if there are dependent objects
-- Deleting single view
DROP VIEW IF EXISTS view1
-- Deleting multiple views
DROP VIEW IF EXISTS view1, view2, view3;
5.3. View Real World Example
Scenario:
Suppose we have two tables: orders and customers. we want to create a view that displays each customer’s name and the total amount they have spent on all their orders.
Tables Setup
-- create tables
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
order_date DATE NOT NULL,
amount NUMERIC(10, 2) NOT NULL
);
-- Insert sample data
INSERT INTO customers (customer_name) VALUES
('John Doe'),
('Jane Smith'),
('Alice Johnson');
INSERT INTO orders (customer_id, order_date, amount) VALUES
(1, '2023-01-10', 100.50),
(1, '2023-02-05', 200.75),
(2, '2023-03-01', 150.00),
(3, '2023-04-10', 250.30),
(2, '2023-05-22', 50.25);
Creating the View:
Now, let's create a view called customer_total_spending that shows each customer's name and the total amount they've spent on their orders.
CREATE VIEW customer_total_spending AS
SELECT
c.customer_name,
SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o USING (customer_id)
GROUP BY c.customer_name;
Querying the View:
Now you can query the view just like a table:
SELECT * FROM customer_total_spending;
Result:
The query on the view will return the following:
diffCopy code customer_name | total_spent
----------------+-------------
John Doe | 301.25
Jane Smith | 200.25
Alice Johnson | 250.30
Benefits of the View:
Simplification: The view simplifies querying by pre-joining the customers and orders tables and performing the aggregation.
Security: If you want to give a user access to only the summarized data (e.g., total spending) without exposing the full details of each order, you can grant access to the view but not the underlying tables.
