PostgresSQL Cheatsheet

psql_cheatsheet.md

PostgreSQL

Installation

Installation Process

NOTE: Make sure you add bin to the path for you to be able to run the command.

Starting a postgres db

$ sudo service postgress start

Logon to the psql command line tool

$ sudo -u postgres psql Now you will be in the psql mode # help -> will show you the help commands

Note: we will represent psql mode by the '#' before the commands and the terminal mode by '$'.

List the databases

# \l

Creating a new database

# CREATE DATABASE <db-name> ;

IMP: Make sure you put the ';' at the end, else the command won't be executed.

Connecting to a database

Method 1 Directly from terminal

$ psql -h localhost -p 5432 -U postgres <db-name>

-h : for the hostname -p : for port name (default: 5432) -U : username

Method 2 From the psql command line

# \c <db-name> this will connect you the the db

Deleting a database

PLEASE BE CAREFUL WHILE USING THIS COMMAND

DROP DATABASE <db-name> ;

';' is needed

Creating Tables

 # CREATE TABLE <table-name> ( 
 #  <col-name>  <data-type> <constraints>,
 #  .
 #  .
 #  .
 #  );

Example:

test=# CREATE TABLE person (
test(# id BIGSERIAL NOT NULL PRIMARY KEY,
test(# first_name VARCHAR(50) NOT NULL,
test(# last_name VARCHAR(50) NOT NULL,
test(# gender VARCHAR(7) NOT NULL,
test(# dob DATE NOT NULL,
test(# email VARCHAR(150) );
CREATE TABLE

This will create a table to view the tables run the \d command. It will list all the relations # \d

If you want to see only the tables run # \dt

To see more about the a table run # \d <table-name>

Various Data types in psql

Name	Aliases	Description
bigint	int8	signed eight-byte integer
bigserial	serial8	autoincrementing eight-byte integer
bit [ (n) ]		fixed-length bit string
bit varying [ (n) ]	varbit [ (n) ]	variable-length bit string
boolean	bool	logical Boolean (true/false)
box		rectangular box on a plane
bytea		binary data ("byte array")
character [ (n) ]	char [ (n) ]	fixed-length character string
character varying [ (n) ]	varchar [ (n) ]	variable-length character string
cidr		IPv4 or IPv6 network address
circle		circle on a plane
date		calendar date (year, month, day)
double precision	float8	double precision floating-point number (8 bytes)
inet		IPv4 or IPv6 host address
integer	int, int4	signed four-byte integer
interval [ fields ] [ (p) ]		time span
json		textual JSON data
jsonb		binary JSON data, decomposed
line		infinite line on a plane
lseg		line segment on a plane
macaddr		MAC (Media Access Control) address
money		currency amount
numeric [ (p, s) ]	decimal [ (p, s) ]	exact numeric of selectable precision
path		geometric path on a plane
pg_lsn		PostgreSQL Log Sequence Number
point		geometric point on a plane
polygon		closed geometric path on a plane
real	float4	single precision floating-point number (4 bytes)
smallint	int2	signed two-byte integer
smallserial	serial2	autoincrementing two-byte integer
serial	serial4	autoincrementing four-byte integer
text		variable-length character string
time [ (p) ] [ without time zone ]		time of day (no time zone)
time [ (p) ] with time zone	timetz	time of day, including time zone
timestamp [ (p) ] [ without time zone ]		date and time (no time zone)
timestamp [ (p) ] with time zone	timestamptz	date and time, including time zone
tsquery		text search query
tsvector		text search document
txid_snapshot		user-level transaction ID snapshot
uuid		universally unique identifier
xml		XML data

Taken from the docs

For more info look at this site

Commonly used constrainst in psql

The following are commonly used constraints available in PostgreSQL.

Keyword	Description
NOT NULL	Ensures that a column cannot have NULL value.
UNIQUE	Ensures that all values in a column are different.
PRIMARY KEY	Uniquely identifies each row/record in a database table.
FOREIGN KEY	Constrains data based on columns in other tables.
CHECK	Ensures that all values in a column satisfy certain conditions.
EXCLUSION	Ensures that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s), not all of these comparisons will return TRUE.

Deleting table

PLEASE BE SURE, because dropping a table will lead to permanent loss of all the data

# DROP TABE <table-name>;

Inserting records in table

We need to specify the table name first then specify the column names, and the specify the values in the same order you specified the column names

 INSERT INTO <table-name> (
   <col1>,
   <col2>,
   .
   .
   .
   <colx> )
   VALUES (<value1>, <value2>, <value3>, ... <valuex>);

  

Example:

test=# INSERT INTO person (
first_name,
last_name,
gender,
dob,
email)
VALUES ('Jake', 'Jones', 'MALE', DATE '1990-01-10', 'jaske@gmail.com');
INSERT 0 1

Executing commands from a file (.sql file)

# \i /absolute/path/to/the/sqlfile

Make sure the commands from the file are valid and ends with ';' semi-colon

Viewing the records of a table (Quering)

# SELECT * FROM <table-name>;

here * means you want to Select all the columns and hence every data will be displayed

# SELECT <col1>, <col2> from <table-name>;

Now only the data of col1 and col2 will be displayed

ODERING THE DATA using the ORDER BY clause # SELECT * FROM <table-name> ORDER BY <col1>;

By default they are in ascending order Also we can specify # SELECT * FROM <table-name> ORDER BY <col1>, <col2> ASC;

for descending order we use # SELECT * FROM <table-name> ORDER BY <col1>, <col2> DESC;

DISINCT DATA # SELECT DISTINCT <col1>, <col2> FROM <table-name> ORDER BY <col1>;

FILTERING DATA We can filter the data by adding a WHERE clause

# SELECT * FROM <col1> WHERE <condition>