PostgreSQL Cheat Sheet

Objects

Table	The table is used to store the data. A special feature of PostgreSQL table is inheritance.
Schema	A schema is a logical container of tables and other objects inside a database.
Tablespace	A tablespace is where PostgreSQL stores the data.
View	The view is a virtual table that is used to simplify complex queries and to apply security for a set of records.
Function	The function is a block reusable SQL code that returns a scalar value of a list of records. In PostgreSQL, functions can also return composite objects.
Cast	Casts enable you to convert one data type into another data type. Casts actually backed by functions to perform the conversion.
Sequence	Sequences are used to manage auto-increment columns that defined in a table as a serial column.

Other

CAST	convert explicit a value from one data type to another. CAST( expression AS datatype )
BETWEEN	value BETWEEN low AND high (include equal). Used with WHERE

Subquery

A subquery is a query nested inside another query such as SELECT, INSERT, DELETE and UPDATE.

GROUP and HAVING

The GROUP BY clause divides the rows returned from the SELECT statement into groups.

The HAVING clause sets the condition for group rows created by the GROUP BY clause after the GROUP BY clause applies while the WHERE clause sets the condition for individual rows before GROUP BY clause applies.

String operations

Concatenate

first_name || ' ' || last_name

SELECT

SELECT
column_name
FROM
table_name
ORDER BY
column_name ASC (DESC);

LIKE (case sensitive)

~~ is equivalent to LIKE

~~* is equivalent to ILIKE

!~~ is equivalent to NOT LIKE

!~~* is equivalent to NOT ILIKE

WHERE operators

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<> or !=	Not equal
AND	Logical operator AND
OR	Logical operator OR

LIMIT & OFFSET

SELECT column_a
FROM table_name
WHERE last_name = ''
ORDER BY first_name
LIMIT 5 OFFSET 3;

LIMIT is not a SQL-standard.

FETCH

OFFSET start { ROW | ROWS }

FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY

Order of clause

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

INSERT new record

INSERT INTO table_name (column_1, column_2)
VALUES
('val1', 'val2')
('val3, 'val4');

Alias

column_name AS alias_name

column_name alias_name

expression alias_name

INNER JOIN

SELECT column_a
FROM A_table
INNER JOIN B_table ON A_table.pka = B_table.fka;

JOINS general

Inner Join	selects rows from one table that have the corresponding rows in other tables.
Left Join	selects rows from one table that may or may not have the corresponding rows in other tables.
Self-join	joins a table to itself by comparing a table to itself.
Full Outer Join	uses the full join to find a row in a table that does not have a matching row in another table.
Cross Join	produces a Cartesian product of the rows in two or more tables.
Natural Join	joins two or more tables using implicit join condition based on the common column names in the joined tables.

PostgreSQL Cheat Sheet (DRAFT) by matiwan