PostgreSQL

Create a new table

CREATE TABLE table_name (
    column_name1 data_type,
    column_name2 data_type,
    column_name3 data_type
);

Add data to a table

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

If you want to only fill in certain columns, you can specify which columns to insert data into:

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

Query data from a table

Select all

Select all data from a table:

SELECT * FROM table_name;

Select specific columns

Select specific columns from a table:

SELECT column1, column2 FROM table_name;

Count

Count the number of rows in a table:

SELECT COUNT(*) FROM table_name;

Filter

Filter data based on a condition:

SELECT * FROM table_name WHERE condition;

Order

The result of a query can be ordered by one or more columns, ascending (the default) or descending:

SELECT * FROM table_name ORDER BY column1, column2 DESC;

Limit

Limit the number of rows returned from a query:

SELECT * FROM table_name LIMIT 5;

Update data in a table

Update all

Update all data in a table:

UPDATE table_name SET column1 = value1, column2 = value2, ...;

Update specific rows

Update specific rows in a table:

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Delete data from a table

Delete all

Delete all data from a table:

DELETE FROM table_name;

Delete specific rows

Delete specific rows from a table:

DELETE FROM table_name WHERE condition;

Drop table

Delete a table:

DROP TABLE table_name;

Null values

A field with a NULL value is a field with no value.

It is very important to understand that a NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

To prevent NULL values in a column, use the NOT NULL constraint:

CREATE TABLE table_name (
    column_name data_type NOT NULL,
    column_name data_type NOT NULL,
    column_name data_type NOT NULL
);

To check if a value is NULL or not, use the IS NULL and IS NOT NULL operators:

SELECT column1, column2 FROM table_name WHERE column1 IS NULL;

Unique values and primary keys

The UNIQUE constraint ensures that all values in a column are different.

Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint.

CREATE TABLE table_name (
    column1 data_type UNIQUE,
    column2 data_type UNIQUE,
    column3 data_type UNIQUE
);

We use the SERIAL type for the primary key, which means it's a unique INTEGER value that gets automatically generated by the system.

CREATE TABLE table_name (
    id SERIAL PRIMARY KEY,
    column1 data_type,
    column2 data_type,
    column3 data_type
);

Primary keys helps us:

• Ensure data in a specific column is unique.
• Identify each row in a table.
• Quickly find a row in a table.

Foreign keys

A foreign key is a column or group of columns in a table that links to a column or group of columns in another table.

The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

CREATE TABLE table_name (
    id SERIAL PRIMARY KEY,
    column1 data_type,
    column2 data_type,
    column3 data_type,
    foreign_key_column INT REFERENCES other_table_name(id)
);

Update a table structure

Add a column

ALTER TABLE table_name ADD COLUMN column_name data_type;

Modify a column

ALTER TABLE table_name MODIFY COLUMN column_name data_type;

Delete a column

ALTER TABLE table_name DROP COLUMN column_name;

Joins

Inner join

The inner join keyword selects records that have matching values in both tables.

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

Left join

The left join keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

Right join

The right join keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Full join

The full join keyword returns all records when there is a match in either left (table1) or right (table2) table records.

SELECT column_name(s)
FROM table1
FULL JOIN table2 ON table1.column_name = table2.column_name
WHERE condition;

Type cheatsheet

Type	Description
CHAR	fixed-length character string.
VARCHAR	variable-length character string.
TEXT	variable-length character string.
DATE	date value.
TIME	time value.
DATETIME	date and time combination.
TIMESTAMP	date and time.
INT	integer number (4 bytes).
SMALLINT	integer number (2 bytes).
BIGINT	integer number (8 bytes).
DECIMAL	fixed-point number.
FLOAT	floating-point number.