Postgres Database Basic Commands and SQL Queries

In this post, We will discuss How to connect the Postgres database in both Windows and Ubuntu Operating Systems and will learn some basic Postgres commands. Also, we shall try a set of basic SQL queries for manipulating database Tables.

Connecting Postgres Database in Ubuntu

sudo -u postgres p postgres

Connecting Postgres Database in Windows

• Just open the psql window from the start menu

Display List of Databases in Postgres

#\l                                           

Connect to Database (Postgres)

 #\c postgres;

You are now connected to database "postgres" as user "user_3y3vmxg6f".

Create a Table (e.g. student)

create table student (rollno integer, name character(200), dob date);

CREATE TABLE

Adding Primary Key

alter table student add constraint student_pk primary key(rollno);

ALTER TABLE

Display Tables in DB

 #\dt; 

             List of relations
 Schema |  Name   | Type  |     Owner      
--------+---------+-------+----------------
 public | student | table | user_3y3vn3tuu
(1 row)

Describe Table

 #\d student;

                Table "public.student"
 Column |      Type      | Collation | Nullable | Default 
--------+----------------+-----------+----------+---------
 rollno | integer        |           | not null | 
 name   | character(200) |           |          | 
 dob    | date           |           |          | 
Indexes:
    "student_pk" PRIMARY KEY, btree (rollno)

Insert Values into Table (Student)

insert into student values(1000, 'Kumar', '1998-04-25');
insert into student values(1001, 'Thomas', '1998-05-23');
insert into student values(1003, 'Geetha', '1997-06-13');

INSERT 0 1
INSERT 0 1
INSERT 0 1

Insert Bulk Values

insert into student (rollno, name, dob) 
    values (1000, 'Kumar', '1998-04-25'),
           (1001, 'Thomas', '1998-05-23'),
           (1003, 'Geetha', '1997-06-13');

Select Rows from the Table (student)

select * from student;

rollno | name       |    dob     
--------+-----------+------------
   1000 | Kumar     | 1998-04-25
   1001 | Thomas    | 1998-05-23
   1003 | Geetha    | 1997-06-13
(3 rows)

Selecting Specific Columns with Where Clause

select rollno, name from student where rollno=1001;

rollno | name       |    dob     
--------+-----------+------------
   1001 | Thomas    | 1998-05-23
(1 row)

Update a Particular Cell in Table

update student set name='Anthoniraj' where rollno=1000;

UPDATE 1

Delete a Particular Row

delete from student where rollno=1003;

DELETE 1

Delete ALL ROWS from Table

delete from student;

DELETE 2

Closing Postgres Connection / Client Terminal

#\q