PGAdmin4 and psql
Sumit Jadhav
Data Scientist at TechStalwarts | Almost 2+ years of experience with data | Python | SQL | Tableau | Power BI | Machine Learning | AI | Generative AI | LLM | MLOps| Fast API | Langchain | Prompt Engineering | Basic AWS
Operations on PGadmin4 without codes. Use inbuilt functions
— — — — — — — — — — — — — — — — — — — — — — — — — — — —
A)Tree Control
— — — — — — — — — — — — — — — — — — — — — — —
1. Server
a)Create
b)Refresh: Use after the change made in the server
c)Remove server group: Delete server
d)Properties: Give information about the server
Server Name(e.g. PostgreSQL 14)
a)Create
-Create server
-Create database
-Login/Group role
-Tablespace
b)Refresh: Use after the change made in the server
c)Backup Globals: Use the Backup Globals dialog to create a plain-text script that recreates all of the database objects within a cluster, and the global objects that are shared by those databases
-Move the Verbose messages switch to the No position to exclude status messages from the backup. The default is Yes.
-Move the Force double quote on identifiers switch to the Yes position to name identifiers without changing case. The default is No.
d)Backup Server: Click to open the Backup Server dialog to backup a server.
e)Disconnect server: Disconnect connection from the server
f) Remove server: Delete or remove or drop the server
g)Add named restore point: Click to create and enter the name of a restore point.
h)Reload Configuration: Click to update configuration files without restarting the server.
i)Clear saved password?: clear all password which saved in the server
j)Properties?: Give information about server
— — — — — — — — — — — — — — — — — — — — — — —
2. Database
a)Create
b)Refresh: Use after the change made in the database
#Database name(e.g. DB2):
a)Create
-Cast?: Used to create cast function(to change datatype)
-Database?: Create new database
-Event Trigger?: Create new event (Used to made some event. If event triggered then inside that event this will occurred. Means task performed inside the event)
-Extension?: Create new Extension(These are the functions which gives extra functionality to the database)
select * from pg_available_extensions;
-Foreign data wrapper
-Language
-Publication
-Scheme?: Create new Scheme (Used for collection of number of tables. Schema is a named collection of tables)
-Subscription
b)Refresh: Use after the change made in the database
c)Backup: You can backup a single table, a schema, or a complete database. Select the name of the backup source in the pgAdmin tree control, right-click to open the context menu, and select Backup… to open the Backup dialogue . The name of the object selected will appear in the dialogue title bar.
d)Delete/drop: Delete or drop the database
e)Restore: Click to access the Restore dialogue to restore database files from a backup.
f)Create script: After clicking on that query tool open with create a database query
g)Disconnect database: Disconnect database from server
h)Generate ERD: The Entity-Relationship Diagram (ERD) tool is a database design tool that provides a graphical representation of database tables, columns, and inter-relationships.
i)Maintenance?: Click to open the Maintenance… dialog to VACUUM, ANALYZE, REINDEX, or CLUSTER.
j)Grant Wizard: To give permission or ownership to another user
k)Search Objects: Search database, schema, triggers and many other functions (Give minimum 3 characters in the search box)
l)PSQL tool
m)Query tool
n)Properties: Give information about the database
o)Additional parameters-
-Drop cascade?: Click to delete the currently selected object and all dependent objects from the server
— — — — — — — — — — — — — — — — — — — — — — —
3. Schema
a)Create
b)Refresh: Use after the change made in the schema
#Schema name(e.g. public):
a)Create
-Collocation?: It is a setting to determine how database engine should treat character data in the server.
-Domain?: This some sort of condition which can be create and then used during creating the table then the table is made according to condition in the domain.
-Foreign table?: change table to foreign table
-FTS configuration?: Like stemming operation
-FTS dictionary
-FTS parser
-FTS template
-Function?: Similar to functions in python. Function have a particular task. After calling it return the out which is in RETURN block.
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
-Procedure?: Used to insert values in the table. If we create a procedure then call that procedure name and insert values. There is no need to write a query of insert.
create procedure procedure_name(data_type1,data_type2)
language 'plpgsql'
as $$
begin
insert into table_name(column1,column2) values ($1,$2);
commit;
end;
$$;
call procedure_name(value1,value2
-Schema?: Create new schema
-Sequence?: Auto increment function always used with INSERT function
insert into table_name values (nextval('sequence_name'),'next_value';
-Table?: Create new table
-Trigger function
-Type
-View?: virtual table which does not store in the server. Only select query is used. Extract information from original/base table and make view means a virtual table. If we change the data in base table then there is change in view.
-Materialized view?: Stored query as well as result of that query in the server. Less time consuming. If we change the data in base table then there is no change in materialized view until we refreshed the view.
b)Refresh?: Use after the change made in the Schema
c)Backup: You can back up a single table, a schema, or a complete database. Select the name of the backup source in the pgAdmin tree control, right-click to open the context menu, and select Backup… to open the Backup dialog. The name of the object selected will appear in the dialog title bar.
d)Delete/drop: Delete or drop the Schema
e)Restore: Click to access the Restore dialog to restore Schema files from a backup.
f)Create script: After clicking on that query tool open with create Schema query
g)Grant Wizard: To give permission or ownership to another user
h)Search Objects: Search database, schema, triggers and many other functions (Give minimum 3 characters in the search box)
i)PSQL tool
j)Query tool
k)Properties: Give information about the Schema
l)Drop cascade: Click to delete the currently selected object and all dependent objects from the server
— — — — — — — — — — — — — — — — — — — — — — —
4. Table
a)Create
b)Refresh?: Use after the change made in the Table
c)Grant Wizard: To give permission or ownership to another user
d)Search Objects: Search database, schema, triggers and many other functions (Give minimum 3 characters in the search box)
e)PSQL tool
f)Query tool
#Table name(e.g. “bankdata”)
a)Create
-Table?: Create new table
-Column?: Add new column in selected table
-Index?: Create index column to the selected table
-Rule
-Trigger
-RLS policy
b)Refresh?: Use after the change made in the Table
c)Backup: You can back up a single table, a schema, or a complete database. Select the name of the backup source in the pgAdmin tree control, right click to open the context menu, and select Backup… to open the Backup dialog. The name of the object selected will appear in the dialog title bar.
d)Count rows: Number of rows present in the selected table
e)Delete/Drop: Delete or drop the table
f)Restore: Click to access the Restore dialog to restore database files from a backup.
g)Drop cascade: Click to delete the currently selected object and all dependent objects from the server
h)Import/Export data: Import the data/Export the data
i)Reset statistics
j)Maintenance: Click to open the Maintenance dialog to VACUUM, ANALYZE, REINDEX, or CLUSTER.
k)Script
-Create Script?: syntax of Create the table
-Delete Script?: syntax of Delete the table
-Insert Script?: syntax of Insert the table
-Select Script?: syntax of Select the table
-Update Script?: syntax of Updatet he table
l)Truncate
-Truncate?: Click to remove all rows from a table.
-Truncate cascade?: Click to remove all rows from a table and its child tables.
-Truncate restart
m)View/Edit data
-All rows?: To show all rows
-First 100 rows?: To show 1st 100 rows
-Last 100 rows?: To show last 100 rows
-Filtered rows?: To show rows according to condition
n)Search Objects?: Search database, schema, triggers and many other functions (Give minimum 3 characters in the search box)
o)PSQL tool
p)Query tool
— — — — — — — — — — — — — — — — — — — — — — —
2. Operations on PGadmin4 with?codes.
— — — — — — — — — — — — — — — — — — — — — — —
A)Database
6. Drop the database
B)Table
1.PostgreSQL Create Table using pgAdmin
pgAdmin>>>database>>>Schema>>>Tables>>>right-click>>>Create>>>Table>>>Give name of table>>>columns>>>add columns with their datatypes and constrains respectively>>>Save.
2.PostgreSQL Create Table using psql
psql>>>connect to the database>>>command to create a table in the database>>>Enter key.
3.PostgreSQL Drop/Delete Table by psql
The syntax for Dropping a table
DROP TABLE table_name;
First, we will confirm these tables with cammand (\d)>>>query for drop table>>>Enter key.
4.PostgreSQL Drop/Delete Table by pgAdmin
pgAdmin>>>database>>>Schema>>>Tables>>>select table>>>right-click>>>Drop/Delete>>>Yes.
5.PostgreSQL show tables using psql
\dt>>>Enter Key
6.PostgreSQL show tables using pgAdmin
query tool>>>syntax
syntax: select *from information_schema.tables where table_schema=’public’;
7. Return all column names of the table using pgAdmin
query tool>>>syntax
syntax:
SELECT COLUMN_NAME FROM information_schema.COLUMNS
WHERE TABLE_NAME = “Table_name”;
8.PostgreSQL describe table using psql
-Open psql tool>>>connect to database(\c database name)>>>Describe table command(\d)>>>enter key.
9.PostgreSQL ALTER TABLE command
General Syntax: ALTER TABLE table_name action;
— — — — — — — — — — — — — — — — — — — — — — —
Commands
1. Add columns
We will use the ALTER TABLE ADD COLUMN to add a new column to a table.
Syntax:ALTER TABLE table_name ADD COLUMN new_column_name TYPE;
a)Add multiple columns using alter table command
ALTER TABLE table_name
ADD COLUMN new_column_name_1 data_type constraint,
ADD COLUMN new_column_name_2 data_type constraint,
:::
:::
ADD COLUMN new_column_name_N data_type constraint;
b)Adding a column with the NOT NULL Constraint
ALTER TABLE table_name
ADD COLUMN new_column_name_1 data_type not null,
2. Drop a?column
We will use the ALTER TABLE DROP COLUMN command for deleting an existing column.
Syntax: ALTER TABLE table_name DROP COLUMN column_name;
a)drop column
ALTER TABLE table_name
DROP COLUMN column_name;
b)drop column which is connected with other objects
ALTER TABLE table_name
DROP COLUMN column_name CASCADE;
c)use IF EXIST function
ALTER TABLE table_name
DROP COLUMN IF EXISTS column_name;
3. Set a default value for the?column
For modifying the column’s default value, we can use the ALTER TABLE ALTER COLUMN SET DEFAULT or DROP DEFAULT command.
Syntax: ALTER TABLE table_name ALTER COLUMN column_name [SET DEFAULT value | DROP DEFAULT];
4. Add a constraint to a?column
We will use ALTER TABLE ADD CONSTRAINT command for adding a constraint.
Syntax: ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;
5. CHECK constraint to a?column
For adding the CHECK constraint, we will use the ALTER TABLE, ADD CHECK command.
Syntax: ALTER TABLE table_name ADD CHECK expression;
6. Rename a?table
For renaming a table, we will use the ALTER TABLE RENAME TO command.
Syntax: ALTER TABLE table_name RENAME TO new_table_name;
7. Rename a?column
We will use the alter table rename column to command for renaming a remaining column.
Syntax: ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;
a)Rename single column
领英推荐
ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name;
b)Renaming the multiple columns
ALTER TABLE table_name
RENAME COLUMN column_name1 TO new_column_name1;
ALTER TABLE table_name
RENAME COLUMN column_name2 TO new_column_name2;
8. Change the datatype of?a column
Syntax: ALTER TABLE table_name ALTER COLUMN column_name [SET DATA] TYPE new_data_type;
a)change the datatype of a single column
ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type;
b)change the datatype of multiple columns
ALTER TABLE table_name
ALTER COLUMN column_name1 TYPE new_data_type,
ALTER COLUMN column_name2 TYPE new_data_type,
.
.
.
ALTER COLUMN column_name_n TYPE new_data_type;
9. Change to not null constrain
To change the NOT NULL constraint, we will then use ALTER TABLE ALTER COLUMN command.
Syntax: ALTER TABLE table_name ALTER COLUMN column_name [SET NOT NULL| DROP NOT NULL];
10. TRUNCATE TABLE
To delete all data from a table, we use the DELETE command. When a table contains a lot of records and is also not useful, in that case, we will use the TRUNCATE TABLE command to remove all the records from a particular table.
TRUNCATE TABLE table_name;
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
CASCADE-This option automatically truncates all tables, which contains foreign-key references to other tables, or any tables added to the collection due to CASCADE.
RESTART IDENTITY-It repeatedly restarts orders owned by columns of the truncated tables.
CONTINUE IDENTITY-It does not change the values of orders, and it is the default option.
RESTRICT-It is also a default option, which can decline to truncate if other tables contain the foreign-key references of tables, which are not mentioned in the command.
a)PostgreSQL Truncate table using pgAdmin
b)PostgreSQL Truncate table using PSQL
truncate table Table_name;
TRUNCATE TABLE table_name1, table_name2 ...;
TRUNCATE TABLE table_name
CASCADE;
11. Temporary Table
A temporary table is a brief table; its name means; it presents at the time of a database session. The PostgreSQL automatically drops the temporary tables at the end of an operation or a session.
CREATE TEMPORARY TABLE temp_table_name(...);
Create Temp Table temp_table_Name(...);
DROP TABLE temp_table_name;
Note: If we have the same permanent table and temporary table name then we drop the table then 1st delete the temporary table.
12. Column Alias
What is PostgreSQL Column Alias?
In PostgreSQL, a Column alias is used to give a short name to the column in the select list of a SELECT command in a particular statement. The column aliasing works as a nickname for the column name, making the column name more readable and shorter.
It exists momentarily throughout the implementation of the command. The column aliasing is very useful when the column name is not user-friendly in real-time.
Syntax1
SELECT column_name AS alias_name
FROM table_name
Conditions...;
Syntax2
In the below syntax, we ignore the AS keyword because it is optional, and the column_name is given to an alias alias_name.
SELECT column_name alias_name
FROM table_name;
Syntax3
The below syntax is used to display how we can set an alias for expression within the SELECT condition.
SELECT expression AS alias_name
FROM table_name;
Note: We must enclose the alias_name in quotes (‘ ‘) if the alias_name contains spaces.
13. PostgreSQL Subquery
A subquery is a command used within another query. In contrast, the INNER SELECT or the INNER statement is called a SUBQUERY, and OUTER SELECT or the OUTER statement is called the MAIN command. The PostgreSQL subquery is enclosed in parentheses.
The PostgreSQL subquery can be used with different clauses such as SELECT, FROM, WHERE and HAVING clauses.
a)Subquery with SELECT clause
b)Subquery with FROM clause
c)Subquery with WHERE clause
d)Subquery With different Conditions
e)Subquery with IN Condition
f)Subquery With different statements
— — — — — — — — — — — — — — — — — — — — — — —
C)Conditions
Some of the most commonly used PostgreSQL conditions are as follows:
That it is used with WHERE clause to get involved in those rows where both conditions are true.
Example-
DELETE FROM customer
WHERE first_name = 'Jane'
AND last_name ='Miller';
UPDATE summer_fruits
SET summer_fruits_name = 'Strawberry'
WHERE fruit_id = 4
AND summer_fruits_name= 'Guava'
SELECT client.client_id, client.client_name,
client.client_qualification,client_details.mobile_number
FROM client, client_details
WHERE client.client_id = client_details.client_id
AND client.client_qualification= 'MBA';
SELECT client_name, client_profession, client_qualification, client_salary
FROM client
WHERE client_qualification = 'MBA'
AND client_salary <= 1000000;
INSERT INTO employee
(phone, address)
SELECT phone, address
FROM department
WHERE dept_id < 5
AND department_name = 'SALES';
2. OR Condition
The PostgreSQL OR condition is used with WHERE clause to include rows where either condition is true and select unique data either from various columns in a table.
Examples-
SELECT car_id, car_name, car_model, car_price, car_color, body_style
FROM car
WHERE body_style = 'coupe'
OR body_style ='wagon'
OR car_price = 399999;
INSERT INTO department
(phone, address)
SELECT phone, address
FROM employee
WHERE emp_id <6
OR employee_name = 'Emily Smith';
UPDATE customer
SET cust_name= 'Mike', cust_address='London', cust_age=26
WHERE cust_id = 103
OR cust_name = 'emily';
SELECT emp_id, emp_fname, emp_lname, location
FROM employee
WHERE location = 'New York'
OR emp_id > 8;
3. AND & OR Condition
provides the advantages of AND & OR Condition both in just a single command. The PostgreSQL allows us to test several conditions with the help AND & OR operators.
Example-
UPDATE department
SET dept_name= 'RESEARCH'
WHERE (emp_fname = 'Flora' OR dept_id = 4)
AND emp_id < 5;
DELETE FROM customer
WHERE (cust_address = 'Florida' AND cust_name = 'harvey')
OR cust_age >=26;
INSERT INTO department
(phone, address)
SELECT phone, address
FROM employee
WHERE (employee_name = 'Nia Davis' OR employee_name = 'Kat Taylor')
AND emp_id <5;
4. NOT Condition
The PostgreSQL NOT condition is used to get those rows where a condition is not true.
Examples-
SELECT customer_id, first_name, last_name
FROM Customer
WHERE last_name NOT IN ('Smith', 'Brown') ;
SELECT car_id, car_name, car_price, car_model
FROM car
WHERE car_price NOT BETWEEN 100000 AND 399999;
SELECT emp_id, emp_fname, emp_lname, location
FROM employee
WHERE NOT EXISTS (
SELECT *
FROM department
WHERE employee.emp_id= department.emp_id);
5. LIKE Condition
It is used to fetch data from a table where the defined condition satisfies the LIKE condition. And we can also say that the Like condition is used to perform pattern matching for identifying the exact outcome.
Examples-
SELECT emp_fname, emp_lname
FROM employee
WHERE emp_fname NOT LIKE 'Kat%';
/*So, we may identify the employee in the Employee table by seeing at the
emp_fname column to check if there is any value that starts with kat*/
SELECT emp_fname, emp_lname
FROM employee
WHERE emp_fname LIKE 'Kat%';
/*In the below example, the particular command will return those employees
whose emp_fname contains "in" string such as Katherine, Katrina, Karina,etc.*/
SELECT emp_fname, emp_lname
FROM employee
WHERE emp_fname LIKE '%in%'
ORDER BY emp_fname;
--show values which is end with "a"
SELECT emp_fname, emp_lname, location
FROM employee
WHERE emp_lname LIKE '_mith';
SELECT emp_fname, emp_lname
FROM employee
WHERE emp_fname LIKE '_at%'
ORDER BY emp_fname;
6. IN Condition
The PostgreSQL IN condition is used to describe the several values in a WHERE clause. And it is a shorthand for various OR conditions. The PostgreSQL IN condition can be used with the SELECT, INSERT, UPDATE, and DELETE commands.
Examples-
SELECT *
FROM employee
WHERE emp_fname IN ('John', 'Ava')
ORDER BY emp_id DESC;
SELECT emp_id, dept_id, emp_fname, dept_name
FROM department
WHERE emp_id IN (1, 2,3)
ORDER BY dept_name DESC;
SELECT emp_id, emp_fname, emp_lname
FROM employee
WHERE emp_id IN (
SELECT emp_id
FROM department
WHERE CAST (Joining_date AS DATE) = '2020-06-22'
)
ORDER BY emp_id;
7. NOT IN Condition
The PostgreSQL NOT IN condition is used with the WHERE clause to fetch data from a table where the defined condition contradicts the PostgreSQL IN condition.
Examples-
SELECT emp_id, dept_id, emp_fname, dept_name
FROM department
WHERE emp_id NOT IN (1, 2)
ORDER BY dept_name DESC;
SELECT emp_id, emp_fname, emp_lname
FROM employee
WHERE emp_fname NOT IN ('James', 'Mia')
ORDER BY emp_id;
8. BETWEEN Condition
The PostgreSQL Between condition is used to select values within a given range, and the values can be text, numbers, or dates. It can also be used with the WHERE clause to return data from a table between two defined conditions.
Example-
SELECT cust_id, cust_name, Cust_age
FROM customer
WHERE cust_age BETWEEN 23 AND 26 ;
SELECT emp_fname, dept_id, dept_name, joining_date
FROM department
WHERE joining_date BETWEEN '2017-10-12' AND '2020-06-22' ;
SELECT cust_id, cust_name, Cust_age
FROM customer
WHERE cust_age NOT BETWEEN 23 AND 26;
9. EXIST Condition
The PostgreSQL EXISTS condition is used to test for the existence of any record in a subquery and returns true if the subquery returns one or more records.
Examples-
SELECT client_name, client_profession,
client_qualification, Client_salary
FROM Client c
WHERE EXISTS
(SELECT 1
FROM Client_details cd
WHERE cd.client_id = c.client_id
AND client_salary > 30000 )
ORDER BY client_name, client_profession;
SELECT emp_fname, emp_lname
FROM employee
WHERE EXISTS (SELECT NULL)
ORDER BY emp_fname, emp_lname;
— — — — — — — — — — — — — — — — — — — — — — —
D)Datatypes
Overview
— — — — — — — — — — — — — — — — — — — — — — —
E)Clause
1. WHERE Clause
In the Select command, the WHERE condition comes after the FROM clause. And the condition is used to filter the rows returned from the SELECT command.
Syntax-
SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition]
ORDER BY sort_expression
It begins from the From clause → then it performs the Where condition → after that it will Select the particular columns → and then perform by the Order by clause.
The WHERE condition does not support the column aliases under the SELECT Command. The Where clause supports the DELETE and UPDATE command, which is used to define the removed and updated rows.
Examples-
SELECT first_name, last_name
FROM
employee
WHERE
first_name = 'Mia' AND last_name = 'Smith';
SELECT first_name,last_name
FROM employee
WHERE last_name LIKE 'smi%';
SELECT last_name, LENGTH(last_name) name_length
FROM employee
WHERE last_name LIKE '%ith' AND LENGTH(last_name) BETWEEN 2 AND 6
ORDER BY name_length;
2. Order by clause
In this section, we are going to learn the PostgreSQL ORDER BY condition, which is used for sorting data in ascending or descending order. And the records are fetched on the basis of one or more columns.
syntax-
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
It begins from the From clause → after that it will Select the particular columns → and then perform by the Order by clause.
Examples-
SELECT first_name, last_name
FROM employee
ORDER BY first_name ;
SELECT first_name, last_name
FROM employee
ORDER BY first_name desc ;
SELECT address, email
FROM employee
ORDER BY address ASC, email DESC;
SELECT num
FROM demo
ORDER BY num NULLS LAST;
--fetch null values at last
SELECT num
FROM demo
ORDER BY num NULLS FIRST;
----fetch null values at first
3. Group By
In this section, we are going to understand the working of GROUP BY clause in PostgreSQL. We also see examples of how GROUP BY clause working with SUM() function, COUNT(), JOIN clause, multiple columns, and the without an aggregate function.
Syntax-
SELECT column-list
FROM table_name
WHERE [conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
Examples-
SELECT emp_id, first_name, SUM(salary)
FROM employee
GROUP BY first_name, emp_id
ORDER BY emp_id;
SELECT first_name,
COUNT (emp_id)
FROM employee
GROUP BY first_name;
4. Having clause
-The having clause is used to specify a search condition for a group or an aggregate. And it is regularly used with the GROUP BY clause to filter groups or aggregates based on a detailed condition.
The HAVING clause allows us to filter groups of rows as per the defined condition.
The HAVING clause is useful to groups of rows.
Syntax-
SELECT column1, aggregate_function (column2)
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
Examples-
SELECT emp_id, first_name, SUM (salary)
FROM employee
GROUP BY first_name, emp_id
HAVING SUM (salary) > 25000
order by first_name DESC;
SELECT first_name, COUNT (emp_id)
FROM employee
GROUP BY first_name
HAVING COUNT (emp_id)< 2;
5. DISTINCT
In this section, we are going to understand the working of the PostgreSQL DISTINCT clause, which is used to delete the matching rows or data from a table and get only the unique records.
Select Distinct column1
FROM table_name;
Examples-
SELECT DISTINCT Summer_fruits
FROM demo_dist
ORDER BY Summer_fruits ;
SELECT DISTINCT Summer_fruits, Winter_fruits
FROM demo_dist
ORDER BY Summer_fruits, Winter_fruits;
6. LIMIT
LIMIT clause, which is used to get a subset of rows produced by a command.
syntax-
SELECT select_list
FROM table_name
ORDER BY sort_expression
LIMIT row_count
SELECT select_list
FROM table_name
LIMIT row_count OFFSET row_to_skip;
Examples-
SELECT Car_id, Car_name, Body_Style
FROM CAR
ORDER BY Car_id
LIMIT 6;
--Fetch top 6 rows
SELECT Car_id, Car_name, Body_Style
FROM CAR
ORDER BY Car_id
LIMIT 5 OFFSET 2;
7. Fetch Clause
FETCH clause, which is used to recover various rows returned by a command.
Syntax
OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY
Note:
Examples-
SELECT car_id, car_name, car_model
FROM Car
ORDER BY car_name
FETCH FIRST ROW ONLY;
SELECT car_id, car_name, car_model
FROM Car
ORDER BY car_name
FETCH first 1 ROW ONLY;
SELECT car_id, car_name, car_model
FROM Car
ORDER BY car_name
OFFSET 4 ROWS
FETCH first 4 ROW ONLY;
— — — — — — — — — — — — — — — — — — — — — — —
F)Query
1. Insert Query
INSERT command is used to insert new rows into a table. We can insert a single row or multiple row values at a time into the particular table.
If we want to insert character data, we must enclose it in single quotes (‘).
Syntax 1-
INSERT INTO TABLE_NAME
(column1,
column2,
column3, ……columnN)
VALUES (value1, value2, value3, ….. valueN);
Syntax 2.Inserting a single record using the default value keyword
INSERT INTO table
(column1, column2, ... )
DEFAULT VALUES;
Syntax 3.Inserting the multiple records using a sub-select
INSERT INTO table_name
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_table
[WHERE conditions];
Example:
INSERT INTO department (dept_name, location)
VALUES ('ACCOUNTING', 'Boston'),
('OPERATIONS','Florida'),
('SALES','Chicago');
INSERT INTO department (Dept_name, last_update)
VALUES('HR',DEFAULT);
INSERT INTO department_tmp
SELECT *
FROM
department
WHERE
last_update IS NOT NULL;
2. Select Query
SELECT command is the core command used to retrieve data from a database table, and the data is returned in the form of a result table, which is called result-sets.
The various clauses of the SELECT command are as follows:
Syntax
SELECT column1, column2,
……
columnN
FROM table_name;
SELECT * FROM table_name;
Examples-
Select
name
from employee;
Select
name
from "Company".employee;
--On the basis of schema
select
name,
age,
address
from "Company".employee;
SELECT
name AS full_name,
address
FROM
"Company".employe
--Using alice
3. Update Query
UPDATE command is used to change the present records in a table. To update the selected rows, we have to use the WHERE clause; otherwise, all rows would be updated.
Syntax
UPDATE table_name
SET column1 = value1,
column2 = value2....,
columnN = valueN
WHERE
condition;
Examples
UPDATE department
SET last_update = DEFAULT
WHERE
last_update IS NULL;
UPDATE department
SET location = 'U.S.A';
UPDATE department_tmp
SET location = department.location,
description = department.description,
last_update = department.last_update
FROM
department
WHERE
department_tmp.Dept_id = department.Dept_id;
UPDATE department
SET description = 'Names of departments',
location = 'NewYork'
WHERE
dept_id = 1
RETURNING dept_id,
description,
location;
4. Delete Query
The DELETE command is used to delete all existing records from a table. And the WHERE clause is used to remove the selected records or else, all the data would be eliminated.
Syntax
DELETE FROM table_name
WHERE [condition];
DELETE FROM table
WHERE table.id = (SELECT id FROM another_table);
Example
DELETE FROM department
WHERE dept_id = 6;
DELETE FROM department
USING department_tmp
WHERE
department.dept_id = department_tmp.dept_id;
--Using command
--we will use the DELETE command's returning condition for deleting all rows in the department_tmp table and returning the removed rows:
DELETE FROM department_tmp
RETURNING *;
— — — — — — — — — — — — — — — — — — — — — — —
G)Schema
For example
Suppose we have Company schema which contains the Employee table, and the public schema also has the Employee table. When we refer to the Employee table, it should be as follows:
public.comany
Or
Employee.company
SELECT current_schema();
A)Creating Schema in PostgreSQL
-select database>>>>schemas>>>create>>>schema>>>Give name>>>>save
CREATE SCHEMA schema_name;
Or
CREATE SCHEMA [IF NOT EXISTS] schema_name;
B)PostgreSQL Drop Schema
-select database>>>>schemas>>>delete/drop>>>yes
DROP SCHEMA schema_name;
or
DROP SCHEMA [IF EXISTS] schema_name [ CASCADE | RESTRICT ];
C)Alter Schema
ALTER SCHEMA schema_name
RENAME TO new_name;
ALTER SCHEMA schema_name
OWNER TO { new_owner | CURRENT_USER | SESSION_USER};
Thank You……………….