Postgres Cheat Sheet

Create Database

create database sample_db;

Psequel command to show databases

\l

Psequel command to show all relation

\d

Quit from command line

\q

Create Sample Table

Example 1

CREATE TABLE people(
        id INTEGER,
        name VARCHAR(255)

)

Example 2

CREATE TABLE flights(
         id SERIAL PRIMARY KEY,
         origin VARCHAR NOT NULL,
         destination VARCHAR NOT NULL,
         duration INTEGER NOT NULL
);

Drop Table

DROP TABLE people;

Insert

Type 1

INSERT INTO people VALUES (1,'Corey')
INSERT INTO people VALUES (2,'Travis')

Type 2

INSERT INTO people (id,name) VALUES (3,'John')
INSERT INTO people (name,id) VALUES ('Harry',4)

Select Satement

All Select

SELECT * FROM people;

Specific Field

SELECT first_name,last_name FROM people;

Filter with "WHERE"(1)

SELECT * FROM people
WHERE last_name='joe';

Filter with "WHERE" and "OR"

SELECT * FROM people
WHERE last_name='joe'
OR last_name='Chanda';

Filter with "WHERE" and "AND"

SELECT * FROM people
WHERE last_name='joe'
AND age<33;

Conditonals

SELECT * FROM people
WHERE age>20;

Ordering(ascending)(Default)

SELECT * FROM people
WHERE age >20
ORDER BY age ASC; 

Ordering(descending)

SELECT * FROM people
ORDER BY age DESC; 

Ordering(Mutiple fields)

SELECT * FROM people
ORDER BY first_name,last_name ; 

Use AVG Function

SELECT AVG(duration) FROM flights;

Use SUM Function

SELECT SUM(duration) FROM flights;

Use COUNT Function Example 1

SELECT COUNT(*) FROM flights;

Example 2

SELECT COUNT(*) FROM flights WHERE origin='California';

Use MIN Function

SELECT MIN(duration) FROM flights

Use MAX Function

SELECT MAX(duration) FROM flights

String Matching

SELECT * FROM flights WHERE origin LIKE '%a%';

LIMIT keyword

SELECT * FROM flights LIMIT 2

LIMIT + ORDERING

SELECT *  FROM flights ORDER BY duration ASC LIMIT 3

Update Statement

Update all entry of specific field

UPDATE test_table 
SET location ='unknown';

Update Specific field with Condition and Filter

UPDATE people
SET occupation ='Garbage'
WHERE first_name='Dibaloke'
AND last_name='Chanda'

Delete Statement

Delete Everything from a table

DELETE FROM test_table

Delete Specific field by filtering

  DELETE FROM flights WHERE destination='London'

Complex Queries

GROUP BY

SELECT origin,COUNT(*) FROM flights GROUP BY origin