PostgreSQL Commands

 

• Select Statement 

• select * from users;  - Selects all column

• select username from users;  - Selects only username column   

• select * from users limit 10;  - Selects only first 10 records

• Here column name is case insensitive 

• e.g username = USERNAME

• Columns name with space in between must be same as what is stored in database 

• e.g.  “User Name” is not equal to “user name”. You should enter “User Name” in query

• Where Filter 

• select * from users where country=’europe’;  - Selects all column from users table whose country column values is europe

• select * from users where country=’europe’ limit 10; - Selects first 10 records from users table whose country column values is europe

• select * from users where “Annual Salary” > 10000;

• Use logical operator on number columns to perform numeric operations

• 
  

select * from users where Extract(Year from to_date(birthdate, “MM/DD/YYYY”))=2015 limit 10;

• 
  

• Selects first 10 users whose birth year is 2015 

• Dynamic And/Or Filters

• Check max value of column dynamically

select * from users  where Extract(Year from to_date(birthdate, “MM/DD/YYYY”))= (select max(Extract(Year from to_date(birthdate, “MM/DD/YYYY”))) from users)  limit 10;

• Here is check maximum year of birthdate from users table 

• Then returns first 10 records whose birth year = calculated max year

• Check max value of column dynamically along with logical operator 

• 
  

select * from users  where Extract(Year from to_date(birthdate, “MM/DD/YYYY”))= (select max(Extract(Year from to_date(birthdate, “MM/DD/YYYY”))) from users) or (select max(Extract(Year from to_date(birthdate, “MM/DD/YYYY”)))-1 from users) limit 10;

• Here is check maximum year of birthdate from users table 

• Then returns first 10 records whose birth year = calculated max year

Logical and + or operator

• 
  

select * from users  where ( Extract(Year from to_date(birthdate, “MM/DD/YYYY”))= (select max(Extract(Year from to_date(birthdate, “MM/DD/YYYY”))) from users) or (select max(Extract(Year from to_date(birthdate, “MM/DD/YYYY”)))-1 from users) ) and Region = “Europe”;

• 
  

• Where - with %string%

select * from users where name like ‘%rut%’;

 To select records with country name having space in between  e.g Sri Lanka

select * from users where country like ‘% %’;

  To select records with country name having space at end  

e.g ‘India ‘(space at the end added)

select * from users where country like ‘% ’;

• Order by

• Order by on string column

select * from users order by country;

• Order by descending on number column

select * from users order by age desc;

• Sum

• 
  

select sum(“salary”) from users;

select sum(“salary”), country from users Where region=’Europe’ group by country Order by sum(“salary”) desc;

• Distinct

To get non repeating values of selected table , we use distinct. Below query gives u all column country from al records

select country from users;

To check non repeating/ unique values

select distinct country from users;