CRUD Operations in postgreSQL

1. What is CRUD?

• CRUD is an acronym for the below methioned database operations:
• Create or add new entries
• Read,retrieve,search, or view existing entries
• Update or edit existing entries
• Delete, deactivate, or remove existing entries.

2. Create operations with examples

Create command can be used to create various types of objects in the database.

Examples:

Create TABLE tablename(Columns datatype);

Create Table table_name
(
column1 datatype [NULL | NOT NULL],
column2 datatype [NULL | NOT NULL],
CONSTRAINT constraint_name UNIQUE (col1, col2, ... col_n)
);

3.Data Types in PostgreSQL

• PostgreSQL offers a rich set of native data types for users
• Character types such as char,varchar, and text.
• Numeric types such as integer and floating-point number.
• Boolean
• Temporal types such as date,time,timestamp, and interval.
• Array for storing array strings, numbers, etc.
• JSON stores JSON data.
• Special types such as network address and geometric data.

4. Constraints

• Constraints are the rules enforced on data columns on table. These are used to prevent invalid data from being entered into the database.
• This ensures the accuracy and reliability of the data in the database.
• Constraints can be define at column level or table level.
• Table level constraints are applied to the whole table.
• Column level constraints are applied only to one column
• We can create constraints during creation of table or use alter command to modify an existing table.
• Types of Constraint

Constraint Name	Descritpion
NOT NULL	Ensures that a column cannot have NULL value.
UNIQUE	Ensures that all values in a column are different.
PRIMARY	Uniquely identifies each row/record in a database table
FOREIGN	Constrains data based on columns in other tables.
CHECK	The CHECK constraint ensures that all values in a column satisfy certain conditions.

5. PostgreSQL Functions & Operators

Build In Functions	Examples
Aggregate Fuctions	Avg(), Count(),Max(),Min(),Sum()
String Fuctions	Chr, Concat, format, Initcap,Lower,Rtrim,Ltrim,Substring,Upper
Date and Time Functions	Age(Timestamp), now(),Current_date,current_time, Current_timestamp, transaction_timestamp()
Comparison  Operators	<,>,<=.>=,=, !=
Mathematical  Operators	+,-,*,/,abs(X), ceil(),floor(),mod(y,x),round(numeric)

6.Table Inheritance

• Table inheritance allows to extract a common set of columns into a parent, master table with children defining additional fields.
• Query references all rows of that master table plus all of its children tables.
• “only” keyword can be used to indicate that the query should apply only to a particular table and not any tables .
• PostgreSQL allows table partitioning via table inheritance.
• PostgreSQL implements range and list partitioning methods

7.Copy Table

• Copy Table is used to copy the structure of a table along with data.
• Unlike Inheritance table , copy table does not have any relationship with the base table.

Syntax with data:

 CREATE TABLE new_table AS TABLE existing_table;

 Syntax without data:

 CREATE TABLE new_table AS TABLE existing_table WITH NO DATA;

8.Read operations and Column Aliases

•Retrieving data from all columns in the table.

•Retrieve data from single column in the table.

•Select distinct rows using DISTINCT operator.

•Sort rows using ORDER BY clause.

•Filter rows using WHERE clause.

•Group rows into groups using GROUP BY clause

• An Alias is a substitute for a table or column

•Syntax  

SELECT column_name AS alias_name   ROM table_name  conditions...  ;  

9. Create Table,Data Types, Constraints, Functions and Operators.

postgres=# create table employees(empid int constraint emp_pk primary key,emp_name varchar(200) not null, computer_id varchar(50), salary int,constraint id_comp unique(computer_id));
CREATE TABLE
postgres=# \d employees;
                       Table "public.employees"
   Column    |          Type          | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
 empid       | integer                |           | not null |
 emp_name    | character varying(200) |           | not null |
 computer_id | character varying(50)  |           |          |
 salary      | integer                |           |          |
Indexes:
    "emp_pk" PRIMARY KEY, btree (empid)
    "id_comp" UNIQUE CONSTRAINT, btree (computer_id)


postgres=# \di
                List of relations
 Schema |  Name   | Type  |  Owner   |   Table
--------+---------+-------+----------+-----------
 public | emp_pk  | index | postgres | employees
 public | id_comp | index | postgres | employees
(2 rows)


postgres=# insert into employees values(100, 'Shuan', 'DXB100', 5000);
INSERT 0 1
postgres=# select * from employees;
 empid | emp_name | computer_id | salary
-------+----------+-------------+--------
   100 | Shuan    | DXB100      |   5000
(1 row)


postgres=# insert into employees values(100, 'Dexter','DXB101',3000);
ERROR:  duplicate key value violates unique constraint "emp_pk"
DETAIL:  Key (empid)=(100) already exists.
postgres=# insert into employees(empid,computer_id,salary) values(101,'DX101',3000);
ERROR:  null value in column "emp_name" of relation "employees" violates not-null constraint
DETAIL:  Failing row contains (101, null, DX101, 3000).
postgres=# insert into employees values(101,'Dexter','DXB100',3000);
ERROR:  duplicate key value violates unique constraint "id_comp"
DETAIL:  Key (computer_id)=(DXB100) already exists.
postgres=#
postgres=# \l
                                                                List of databases
   Name    |  Owner   | Encoding |          Collate           |           Ctype            | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+----------------------------+----------------------------+------------+-----------------+-----------------------
 nano      | postgres | UTF8     | English_United States.1252 | English_United States.1252 |            | libc            | =Tc/postgres         +
           |          |          |                            |                            |            |                 | postgres=CTc/postgres+
           |          |          |                            |                            |            |                 | ron=c/postgres
 postgres  | postgres | UTF8     | English_United States.1252 | English_United States.1252 |            | libc            |
 template0 | postgres | UTF8     | English_United States.1252 | English_United States.1252 |            | libc            | =c/postgres          +
           |          |          |                            |                            |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | English_United States.1252 | English_United States.1252 |            | libc            | =c/postgres          +
           |          |          |                            |                            |            |                 | postgres=CTc/postgres
(4 rows)


postgres=# \c dvdrental
connection to server at "localhost" (::1), port 5432 failed: FATAL:  database "dvdrental" does not exist
Previous connection kept
postgres=# \dt
           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | employees | table | postgres
(1 row)


postgres=#

10. Transactions

• Transactions are units or sequences of work accomplished in a logical order.

• It is performed either manually or automated by some program.

• Transaction controls ensures data integrity and Consistency.

• Every Transaction has a Begin statement followed with the action.

• Commit or Rollback are used to control the flow of the transaction.

• The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.

ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.

11.Views in PostgreSQL

•  View is a logical table that represents data of one or more underlying tables through a Select statement.

• view helps simplify the complexity of a query because you can query a view, which is based on a complex query, using a simple SELECT statement.

• View provides a consistent layer even the columns of underlying table changes.

• It does not store a data by itself and is created on the fly on user request.

• Syntax:

    CREATE VIEW view_name AS query;

12.Sequence

• Sequence  is a special type of data created to generate unique numeric identifiers in the  PostgreSQL  database.

• CREATE SEQUENCE creates a new sequence number generator. This involves creating and initializing a new special single-row table with the name sequence name.

• Functions like nextval , currval , and setval to operate on the sequence.

• Syntax : CREATE SEQUENCE serial START number;

 

13.Index and types of index

• Indexes are primarily used to enhance database performance.

• CREATE INDEX constructs an index on the specified column(s) of the specified table.

• An index allows the database server to find and retrieve specific rows much faster.

• Multiple fields can be specified if the index method supports multicolumn indexes

• Syntax: CREATE UNIQUE INDEX title_idx ON table( Column_name )

14. Types of Indexes

Index Name	Description
B-Tree (Default)	handle equality and range queries on data that can be sorted.
Hash Indexes	useful for equality comparisons, hash index whenever an indexed column is involved in a comparison using the = operator
Generalized Inverted Indexes (GIN)	Inverted indexes. GINs are good for indexing array values as well as for implementing full-text search.
Generalized Search Tree (GiST)	Useful for geometric data types, as well as full-text search.GiST indexes are also capable of optimizing “nearest-neighbor” searches
SP-GiST	Used for a wide range of different non-balanced disk-based data structures, such as quadtrees, k-d trees, and radix trees.
BRIN (Block Range Indexes)	store summaries about the values stored in consecutive physical block ranges of a table.