Section 1.1: Integer Types

Within PostgreSQL, there are three types of integers: smallint, int, and bigint.

• Smallint occupies 2 bytes with a range of -32,767 to 32,767.
• Int utilizes 4 bytes and ranges from -2,147,483,648 to 2,147,483,647.
• Bigint requires 8 bytes, accommodating values from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807.

Typically, we start with int unless there's a compelling reason to opt for smallint or bigint. Smallint is advantageous for limited ranges, like ages or HTTP status codes. Bigint should be used judiciously, as it may degrade database performance.

CREATE TABLE logs (

id SERIAL PRIMARY KEY,

status SMALLINT NOT NULL

);

PostgreSQL also includes smallserial, serial, and bigserial, corresponding to smallint, int, and bigint, respectively. Notably, serial automatically increments, eliminating the need for manual input.

CREATE TABLE todos (

id SERIAL PRIMARY KEY,

label TEXT NOT NULL

);

Section 1.2: Text Data Types

PostgreSQL supports three character types: TEXT, VARCHAR(n), and CHAR(n).

Interestingly, there are no performance differences among these types in PostgreSQL. TEXT can hold strings of any length. When using VARCHAR without specifying a length, it behaves like TEXT. However, when a length is defined, it limits the string to that maximum. CHAR is a fixed-length type, padding shorter strings with spaces as necessary.

CREATE TABLE todos (

id SERIAL PRIMARY KEY,

label TEXT NOT NULL

);

CREATE TABLE employees (

id SERIAL PRIMARY KEY,

first_name VARCHAR(100) NOT NULL

);

CREATE TABLE contacts (

id SERIAL PRIMARY KEY,

tin CHAR(9) NOT NULL

);

Section 1.3: Date and Time Types

The date type in PostgreSQL is 4 bytes and is specifically designed for date storage, ranging from 4713 BC to 5874897 AD. The recommended format is yyyy-mm-dd. While PostgreSQL may accept various formats, sticking to this standard can prevent unexpected outcomes.

CREATE TABLE employees (

id SERIAL PRIMARY KEY,

name TEXT NOT NULL,

hire_date DATE NOT NULL

);

INSERT INTO employees (name, hire_date) VALUES ('John', '2000-02-03');

Using an invalid format, like dd-mm-yyyy, might not trigger an error but can lead to confusion. PostgreSQL's flexibility can sometimes produce ambiguous results.

INSERT INTO employees (name, hire_date) VALUES ('Joe', '03-02-2001');

If there's a mistake, PostgreSQL will typically flag it.

INSERT INTO employees (name, hire_date) VALUES ('Joe', '20-03-2001');

Section 1.4: Timestamp and NULL Values

Timestamps are utilized when dates require time information. PostgreSQL can also handle time zones. Columns can be set to automatically record the date and time upon insertion.

CREATE TABLE todos (

id SERIAL PRIMARY KEY,

label TEXT NOT NULL,

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

);

INSERT INTO todos (label) VALUES ('Walk a dog');

NULL values indicate unknown data, not missing data. Therefore, comparisons should not use the equal sign (==); instead, one should check with IS NULL or IS NOT NULL.

CREATE TABLE orders (

id SERIAL PRIMARY KEY,

product_id INT NOT NULL,

sale_id INT

);

INSERT INTO orders (product_id, sale_id)

VALUES (1, NULL), (1, 2), (2, NULL), (3, 4);

Section 1.5: Numeric and Boolean Types

The NUMERIC type is ideal for storing precise values, especially in financial contexts. It can manage up to 131,072 digits before the decimal and 16,383 after.

CREATE TABLE crypto (

id SERIAL PRIMARY KEY,

name TEXT NOT NULL,

price NUMERIC(10,2)

);

INSERT INTO crypto (name, price) VALUES ('Bitcoin', 71029.90);

BOOLEAN values can be TRUE, FALSE, or NULL, with additional representations like 'true' or 'yes' allowed, though it’s best practice to stick with TRUE or FALSE.

CREATE TABLE articles (

id SERIAL PRIMARY KEY,

title TEXT NOT NULL,

is_public BOOLEAN NOT NULL

);

INSERT INTO articles (title, is_public)

VALUES ('Learning PostgreSQL', TRUE), ('Learning Python', FALSE);