Basic PostgreSQL Commands
In this article, I have tried to cover all the basic Postgres SQL commands with examples. You can try all these commands on the SQL shell parallelly and check for the output.
Postgresql is almost similar to SQL. It would be better if you could finish off SQL and then start PostgreSQL.
— create a database Students using CREATE
CREATE DATABASE Students;
— Display all the databases and check if the database is created
\l
— drop a database to learn how drop works
DROP DATABASE [IF EXISTS] Students;
If exists is optional here. In case you do not use if exists and the database isn’t present it will display a warning but not an error.
— Destroying the database
dropdb -p 4565 -h host_name -i -e database_name;
— create the database and connect to the database to create the schema
CREATE DATABASE Students;
\c Students
— create a table in the students database.
Let us also add constraints
CREATE TABLE students_marks( student_id integer primary Key,student_name Varchar(20),student_score integer NOT NULL );
— check if the table is created in the database by checking all the relations in the database
\d
— drop a table
DROP TABLE students;
Note: You should be cautious while using the command DROP TABLE in PostgreSQL because when a table is deleted, then all the information contained in the table would also be lost permanently.
— Insert values into the table
INSERT INTO Students(student_id,student_name,student_marks) values (1,sridhar,35);
— Order BY
Sort data in a table based on a particular column
select * from table_name order by id
select * from table_name order by id DESC
— DISTINCT
used to remove duplicate rows from the result. It keeps one row for each group of duplicates
SELECT DISTINCT column_1 FROM table_name;
— LIMIT
Restrict the number of records returned by the SELECT query
SELECT * FROM students LIMIT 2;
— Array
define a column as an array of valid data types
we can create a column called contact which holds an array as shown below
CREATE TABLE Employees (id int PRIMARY KEY,name VARCHAR (100),contact TEXT []);
You can insert values into the array as shown below
INSERT INTO Employees VALUES(1,’Alice John’, ARRAY [ ‘(408)-743–9045’,’(408)-567–7834' ]);
— Between Operator
Select * from table_name where variable between value 1 to value 2
— Not Between Operator
Select * from table_name where variable not between value1 to value2
— LIKE Operator
Like and Not like helps us to match a variable using wildcards along with the select, update, insert, delete.
% wildcard matches one or more values
_ wildcard matches exactly one value
select * from table_name where variable LIKE (wild card)
select * from table_name where variable NOT LIKE (wildcard)
— Exists Operator
tests whether a row(s) exists in a subquery
We can use this command with select, insert, update, delete
Syntax: WHERE EXISTS (Subquery)
Let's check with an example
select * from book where EXISTS (select * author where book_id=price_id)
this will return all rows from the book database where book_id is equal to price_id
— Delete Command
Delete one or more records from a table with one or more conditions.
With one condition
DELETE FROM price where price_id=10
With two conditions
DELETE FROM price where price_id=10 AND price=40
Using exists
DELETE FROM table_name WHERE exists (subquery)
this will delete records from table that satisfy the written subquery
— Alter Command
There are many uses of Altercommand. It can be used to set constraints, change column names, etc.
Adding a new column
ALTER TABLE table_name ADD newcolumn_name column_definition;
Renaming a Column
ALTER TABLE table_name RENAME COLUMN old_name TO new_name
You can also set a default values for a column, rename tables using alter command.
— Joins
used for retrieving data from more than one table.
A JOIN condition is added to the statement, and all rows that meet the conditions are returned. The values from different tables are combined based on common columns
Inner Join
There are three types
Theta Join
allows one to join two tables based on the condition that is represented by theta. mostly referred to as Inner join
SELECT columns from table1 INNER JOIN table2 on table1.column=table2.column
Equi Join
provides us with a way of joining two tables based on primary key/foreign key relationship
SELECT * table1 JOIN table2 on tabke1.id=table2.id
Natural Join
Improving Equi join by using the Natural keyword.
Select * table1 NATURAL JOIN table 2
Outer Join
Here also there are three types
Left Outer Join
return all rows in the table on the left-hand side and only the rows in the right-hand side table where the join condition has been satisfied.
SELECT columns FROM table-1 LEFT OUTER JOIN table-2 ON table-1.column = table-2.column;
Right Outer Join
returns all rows in the table on the right-hand side and rows in the table on the left-hand side where the join condition has been satisfied.
SELECT columns FROM table-1 RIGHT OUTER JOIN table-2 ON table-1.column = table-2.column;
Full Outer Join
return all rows in the table on the left-hand side and all rows in the table on the right-hand side with nulls where the join condition is not satisfied.
SELECT columns FROM table-1 FULLOUTER JOIN table-2 ON table-1.column = table-2.column;
— Views
A view is a pseudo-table. But you can select it as an ordinary table.
When creating a view, you just need to create a query then give it a name, making it a useful tool for wrapping complex and commonly used queries.
Create View
CREATE [OR REPLACE] VIEW view-name AS SELECT column(s) FROM table(s)[WHERE condition(s)];
Delete View
DROP VIEW [IF-EXISTS] view-name;
— Triggers
a function that is triggered automatically when a database event occurs on a database object
A trigger can be marked with the FOR EACH ROW operator during its creation. Such a trigger will be called once for each row modified by the operation. A trigger can also be marked with the FOR EACH STATEMENT operator during its creation. This trigger will be executed only once for a specific operation.
Create Trigger
CREATE TRIGGER trigger-name [BEFORE|AFTER|INSTEAD OF] event-name ON table-name[ — Trigger logic];
Listing All triggers
All triggers created will be stored in pg_trigger table
SELECT tgname FROM pg_trigger;
Drop Trigger
DROP TRIGGER [IF EXISTS] trigger-name ON table-name [ CASCADE | RESTRICT ];
There are many other advanced PostgreSQL commands which I have not covered in this article. I have attached the reference links for further study.
Reference Links
