Skip to content

Instantly share code, notes, and snippets.

Created June 19, 2020 09:14
Show Gist options
  • Save arnabsen1729/2cd1cfe518de623c21ae6619b38267d6 to your computer and use it in GitHub Desktop.
Save arnabsen1729/2cd1cfe518de623c21ae6619b38267d6 to your computer and use it in GitHub Desktop.
PostgresSQL Cheatsheet



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


DROP DATABASE <db-name> ;

';' is needed

Creating Tables

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


test=# CREATE TABLE person (
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) );

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> (
   <colx> )
   VALUES (<value1>, <value2>, <value3>, ... <valuex>);



test=# INSERT INTO person (
VALUES ('Jake', 'Jones', 'MALE', DATE '1990-01-10', '');

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>

Copy link

Thank you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment