PostgreSQL – Cheat Sheet

PostgreSQL is a powerful, open-source object-relational database system that aimed to help developers build applications, administrators to protect data integrity and build fault-tolerant environments. It supports advanced data types and performance optimization features, like Ms-SQL Server and Oracle.

Features of PostgreSQL:

• Open Source DBMS
• Supports ACID properties
• Diverse indexing techniques
• Log-based and trigger-based replication SSL
• Support for JSON
• Supports geographic objects
• Object-oriented and ANSI-SQL 2008 compatible

Data types in PostgreSQL:

The following table list all the data types available in PostgreSQL:

PostgreSQL Data Types
Numeric	Character	Date/Time	Monetary	Binary
Boolean	Geometric	JSON	Enumerated	Text-Search
UUID	Network Address Types	Composite	Object Identifiers	Pseudo
BitString	XML	Range	Arrays	pg_lsn

• Numeric Data types: smallint, integer, bigint, decimal, numeric, real, serial.
• Character Data types: varchar(n), text, char(n).
• Date/Time Data types: timestamp, date, time, interval.
• Monetary Data type: money.
• Binary Data type: bytea (supports hex and escape format).
• Boolean Data type: boolean.
• Geometric Data types: point, line, box, path, polygon, circle, lseg.
• JSON Data types: string, number, boolean, null.
• Enumerated Data types: enum.
• UUID Data type: uuid (stores Universally Unique Identifiers).
• Network Address types: cidr, inet, macaddr.
• Pseudo types: any, anyelement, anyarray, anyenum, anyrange, internal, record, trigger, event_trigger.
• BitString types: bit(n), bit varying(n).
• Range Data types: int4range, int8range, numrange, tsrange (timestamp range), daterange.
• pg_lsn type: pg_lsn (stores Log Sequence Number).

Operators in PostgreSQL:

An operator manipulates individual data items and returns a result. These are the reserved words used in WHERE clause to perform operations.

Operators
Arithmetic Operators	+, -, *, /, %, ^, !
Comparison Operators	=, !=, <>, >, <, >=, <=
Logical Operators	AND, NOT, OR
Bitwise Operators	&, |

Installation:

• To install PostgreSQL, run the following command:

sudo apt install postgresql

OR

sudo apt install postgresql postgresql-contrib

The postgresql-contrib will add some additional utilities and functionalities.

• After installation, switch over to the Postgres account:

sudo -i -u postgres

• Now, you can access the Postgres prompt using psql command.

Working With Databases:

The CREATE DATABASE command is used to create the database.

Syntax:

CREATE DATABASE database_name;

Example:

Create Database

Now, to store the data, we need the table. The CREATE TABLE statement is used to create the table.

Syntax:

CREATE TABLE table_name
(column_1 data_type,
column_2 data_type,
...
column_n data_type);

Example: 

Create table

The INSERT command is used to insert the new record (row) into the table.

Syntax: 

INSERT INTO table_name (column_1, column_2 ,...)
VALUES(value_1, value_2, ...);

Example:  

Insert into table

The SELECT statement is used to fetch the data from a database table, which returns the data in the form of a result table.

Syntax: 

SELECT  
column_1, column_2, .. column_n
FROM
table_name;

Example:

Select command

Note: We can fetch all the fields of the table using an asterisk(*) sign.

The WHERE clause is used to filter results returned by the SELECT statement.

Syntax:

SELECT column_name
FROM 
table_name 
WHERE condition;

Example:

Where clause

The LIMIT clause is used to get a subset of rows generated by a query. It is an optional clause of the SELECT statement. OFFSET n skips ‘n’ rows before beginning to return rows.

Syntax:

SELECT * 
FROM table_name 
LIMIT n;

Example:

Limit & Offset

The structure of an existing table can be modified using the ALTER TABLE statement. PostgreSQL supports the various actions to perform with ALTER TABLE as listed below:

• Add a column to an existing table

ALTER TABLE table_name ADD COLUMN new_column_name TYPE;

• Drop a column from an existing table

ALTER TABLE table_name DROP COLUMN column_name;

• Rename a column from an existing table

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

• Rename a column from an existing table

ALTER TABLE table_name ALTER COLUMN column_name [SET DEFAULT value | DROP DEFAULT];

• Change NOT NULL constraint

ALTER TABLE table_name ALTER COLUMN column_name [SET NOT NULL| DROP NOT NULL];

• Add CHECK constraints to a column

ALTER TABLE table_name ADD CHECK expression;

• Add a constraint

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;

• Rename an existing table

ALTER TABLE table_name RENAME TO new_table_name;

The UPDATE statement is used to update or modify existing data in the table.

Syntax:

UPDATE table_name
SET column_1 = value_1,
   column_2 = value_2, ...
WHERE
   condition_1 AND condition_2;

Example:

Update

The DELETE statement is used to delete the row(s) from the table.

Syntax:

DELETE FROM table_name
WHERE condition;

Example:

Delete