Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

PostgreSQL – Cheat Sheet

  • Difficulty Level : Medium
  • Last Updated : 30 Jun, 2021

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

NumericCharacterDate/TimeMonetaryBinary
BooleanGeometricJSONEnumeratedText-Search
UUIDNetwork Address TypesCompositeObject IdentifiersPseudo
BitStringXMLRangeArrayspg_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 OperatorsAND, 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

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!