When to Use Integer vs. Alternatives in PostgreSQL

• Value Range
  The range of values an integer can store is from -2,147,483,648 to 2,147,483,647.
• Storage Size
  An integer value uses 4 bytes of storage space in your database.

When to use the integer data type:

• It's suitable for various scenarios like storing product quantities, user IDs, or any other data that falls within the integer range.
• This data type is a good default choice for storing whole numbers within the specified range.

Here are some additional points to consider:

• PostgreSQL offers additional integer types like smallserial and serial. These types are special because they automatically increment for each new row insertion, making them useful for creating primary keys or sequence numbers.
• PostgreSQL also provides other integer data types like smallint and bigint.
  • smallint has a smaller storage size (2 bytes) but a more limited range (-32,768 to 32,767).
  • bigint offers a wider range (-9223372036854775808 to 9223372036854775807) but uses more storage space (8 bytes).

---

Creating a Table with an Integer Column

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,  -- Auto-incrementing integer for ID
  name VARCHAR(255) NOT NULL,
  quantity INTEGER NOT NULL,  -- Stores quantity as whole numbers
  price NUMERIC(8,2)  -- Stores price with 2 decimal places
);

This code creates a table named products with three columns:

• price: This uses the NUMERIC data type with a precision of 8 and a scale of 2 to store prices with up to 2 decimal places.
• quantity: This column uses the INTEGER data type to store the product quantity as a whole number.
• name: This stores the product name as text with a maximum length of 255 characters.
• product_id: This is an auto-incrementing integer set as the primary key using SERIAL.

Inserting Data with Integer Values

INSERT INTO products (name, quantity)
VALUES ('T-Shirt', 100), ('Mug', 50);

This code inserts two rows into the products table. The quantity column uses integer values (100 and 50) which are compatible with the INTEGER data type.

Selecting Data with Integer Operations

SELECT product_id, name, quantity
FROM products
WHERE quantity > 75;

This code retrieves data from the products table. It selects rows where the quantity (stored as integers) is greater than 75.

---

Smallint

• Use Case
  This is suitable for storing small whole numbers that fall within this limited range. It's a good option if storage efficiency is a priority for small positive and negative values.
• Value Range
  -32,768 to 32,767
• Storage Size
  2 bytes (half of an integer)

Example
You might use smallint to store data like floor numbers in a building (unlikely to exceed 32,767 floors!).

Bigint

• Use Case
  This is ideal for storing very large whole numbers that wouldn't fit in the standard integer range. It's useful for things like large user IDs, website visitor counts, or timestamps far into the future.
• Value Range
  -9223372036854775808 to 9223372036854775807
• Storage Size
  8 bytes (double an integer)

Example
You might use bigint to store website visitor counts on a high-traffic site.

Numeric (or Decimal)

• Use Case
  This is a versatile option for storing exact numeric values, including whole numbers, decimals, and very large or small numbers. It offers greater flexibility than integer but uses more storage space.
• Value Range
  Theoretically unlimited with configurable precision (total digits) and scale (decimal places)
• Storage Size
  Varies depending on precision and scale

Example
You might use numeric to store precise financial data like account balances or scientific measurements.

Serial and Bigserial

• Use Case
  These are special integer types used to create auto-incrementing primary keys or sequence numbers. They simplify the creation of unique identifiers for your data.
• Value Range
  Starts from 1 and auto-increments for each new row insertion
• Storage Size
  Same as integer and bigint respectively

Example
You might use serial to create a unique ID for each product in your inventory.

Choosing the Right Alternative

The best alternative depends on your specific needs. Consider the range of values you need to store, the storage efficiency requirements, and the level of precision necessary.

• Use serial or bigserial for creating auto-incrementing primary keys.
• Use numeric for precise numeric values, including decimals, or if you need a wider range than integer with exact representation.
• Use bigint for extremely large whole numbers that wouldn't fit in an integer.
• Use smallint for very small positive and negative whole numbers if storage efficiency is crucial.
• Use integer for most general whole number storage within its range.